Команды psql
Метакоманды выполняются внутри psql (терминального клиента PostgreSQL), а не на сервере. Они начинаются с обратной косой черты и не требуют точки с запятой.
| Что делает | Команда |
|---|
| Список баз данных | \l |
| Подключиться к другой базе данных | \c shop |
| Список таблиц | \dt |
| Список таблиц во всех схемах | \dt *.* |
| Описание таблицы (столбцы, индексы, ключи) | \d users |
| Описание с хранением и комментариями | \d+ users |
| Список схем | \dn |
| Список представлений | \dv |
| Список индексов | \di |
| Список функций | \df |
| Список ролей (пользователей) | \du |
| Показать текущее подключение | \conninfo |
| Выполнить SQL из файла | \i setup.sql |
| Включить или выключить замер времени запросов | \timing |
| Расширенный вывод (по одному столбцу на строку) | \x |
| Изменить последний запрос в редакторе | \e |
| Справка по синтаксису SQL | \h CREATE TABLE |
| Список всех метакоманд | \? |
| Выйти из psql | \q |
Подключение из терминала
| Что делает | Команда |
|---|
| Подключиться к базе данных от имени пользователя | psql -U postgres -d shop |
| Подключиться к удалённому хосту и порту | psql -h db.example.com -p 5432 -U app -d shop |
| Подключиться по URL подключения | psql postgresql://app:secret@localhost:5432/shop |
| Выполнить один запрос и выйти | psql -d shop -c "SELECT count(*) FROM users;" |
| Выполнить SQL-файл | psql -d shop -f setup.sql |
| Создать базу данных из командной строки | createdb shop |
| Удалить базу данных из командной строки | dropdb shop |
Базы данных и схемы
| Что делает | SQL |
|---|
| Создать базу данных | CREATE DATABASE shop; |
| Удалить базу данных | DROP DATABASE shop; |
| Показать текущую базу данных | SELECT current_database(); |
| Создать схему | CREATE SCHEMA sales; |
| Показать порядок поиска схем | SHOW search_path; |
| Искать сначала в указанной схеме | SET search_path TO sales, public; |
| Показать версию сервера | SELECT version(); |
Таблицы и типы данных
| Что делает | SQL |
|---|
| Создать таблицу с identity-ключом | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| Добавить столбец | ALTER TABLE users ADD COLUMN phone text; |
| Переименовать столбец | ALTER TABLE users RENAME COLUMN country TO country_code; |
| Изменить тип столбца | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| Задать значение по умолчанию | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| Удалить столбец | ALTER TABLE users DROP COLUMN tags; |
| Очистить таблицу и сбросить identity | TRUNCATE orders, users RESTART IDENTITY; |
| Удалить таблицу, если она существует | DROP TABLE IF EXISTS products; |
| Точное десятичное число для денег | numeric(10,2) |
| Дата и время с часовым поясом | timestamptz |
| Случайный UUID | SELECT gen_random_uuid(); |
| Тип-перечисление (enum) | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
Ограничения и ключи
| Что делает | SQL |
|---|
| Добавить первичный ключ | ALTER TABLE logs ADD PRIMARY KEY (id); |
| Внешний ключ, удаляющий дочерние строки | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| Добавить ограничение CHECK | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| Уникальность, при которой NULL считаются равными (15+) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| Сделать столбец обязательным | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| Удалить ограничение | ALTER TABLE users DROP CONSTRAINT users_email_key; |
Запросы (возможности PostgreSQL)
Стандартные SELECT, WHERE и JOIN есть в шпаргалке по SQL; здесь собраны формы, характерные для PostgreSQL.
| Что делает | SQL |
|---|
| Сравнение без учёта регистра | SELECT * FROM users WHERE name ILIKE 'a%'; |
| Сравнение с регулярным выражением | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| Первая строка в каждой группе | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| LIMIT и OFFSET | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| Стандартное ограничение числа строк | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| Приведение типа через :: | SELECT '42'::integer + 1; |
| Значение вместо NULL | SELECT name, COALESCE(email, 'none') FROM users; |
| Сравнение, при котором NULL считаются равными | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| Сгенерировать последовательность чисел | SELECT * FROM generate_series(1, 5); |
| Собрать значения в строку | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| Условная агрегация | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
Вставка, обновление, удаление и upsert
| Что делает | SQL |
|---|
| Вставить строку и получить новый id | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| Вставить несколько строк | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| Upsert (вставить или обновить) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| Вставить, пропуская дубликаты | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| Обновить и увидеть изменённые строки | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| Обновить по данным другой таблицы | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| Удалить и увидеть удалённое | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| Удалить с использованием другой таблицы | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| Скопировать строки в новую таблицу | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| Что делает | SQL |
|---|
| Получить поле как JSON | SELECT profile -> 'plan' FROM users; |
| Получить поле как текст | SELECT profile ->> 'plan' FROM users; |
| Получить вложенное поле как текст | SELECT profile #>> '{address,city}' FROM users; |
| Строки, JSON которых содержит значение | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| Строки, в которых есть ключ | SELECT * FROM users WHERE profile ? 'city'; |
| Задать ключ | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| Объединить объекты | UPDATE users SET profile = profile || '{"beta": true}'; |
| Удалить ключ | UPDATE users SET profile = profile - 'city'; |
| Собрать JSON из столбцов | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| Строки в виде массива JSON | SELECT jsonb_agg(users) FROM users; |
| Индекс для поиска через @> и ? | CREATE INDEX users_profile_idx ON users USING gin (profile); |
Массивы
| Что делает | SQL |
|---|
| Литерал массива | SELECT ARRAY[1, 2, 3]; |
| Строки, где массив содержит значение | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| Строки, где массив содержит все указанные значения | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| Одна строка на каждый элемент | SELECT name, unnest(tags) FROM users; |
| Добавить элемент в конец | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| Длина массива | SELECT name, cardinality(tags) FROM users; |
Дата и время
| Что делает | SQL |
|---|
| Сейчас, с часовым поясом | SELECT now(); |
| Сегодня | SELECT current_date; |
| Прибавить интервал | SELECT current_date + INTERVAL '7 days'; |
| Строки за последние 30 дней | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| Усечь до месяца | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| Извлечь часть даты | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| Разница между датами (в днях) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| Возраст между датами | SELECT name, age(current_date, hired_on) FROM employees; |
| Форматировать дату как текст | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| Преобразовать текст в дату | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| Перевести в другой часовой пояс | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
Строковые функции
| Что делает | SQL |
|---|
| Конкатенация (NULL даёт NULL) | SELECT name || ' <' || email || '>' FROM users; |
| Конкатенация с пропуском NULL | SELECT concat_ws(' ', name, email) FROM users; |
| Верхний и нижний регистр | SELECT upper(name), lower(name) FROM users; |
| Длина | SELECT name, length(name) FROM users; |
| Часть строки | SELECT substring(name FROM 1 FOR 2) FROM users; |
| Замена | SELECT replace(email, '@example.com', '') FROM users; |
| Выделить часть по разделителю | SELECT split_part(email, '@', 2) FROM users; |
| Замена по регулярному выражению | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| Убрать пробелы | SELECT trim(' hi '); |
CTE и оконные функции
| Что делает | SQL |
|---|
| Дать подзапросу имя через WITH | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| Рекурсивный CTE (счёт до 5) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| Нумерация строк в каждой группе | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| Ранг с одинаковыми местами | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| Нарастающий итог | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| Значение из предыдущей строки | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| Соединение с собственным подзапросом для каждой строки | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
Индексы
| Что делает | SQL |
|---|
| Индекс B-tree (по умолчанию) | CREATE INDEX orders_user_idx ON orders (user_id); |
| Уникальный индекс | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| Составной индекс | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| Частичный индекс | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| Индекс по выражению | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| Покрывающий индекс (index-only scan) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| Построить, не блокируя запись | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| Удалить индекс | DROP INDEX IF EXISTS orders_user_idx; |
| Посмотреть план | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| Посмотреть план с реальным временем выполнения | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
Транзакции и блокировки
| Что делает | SQL |
|---|
| Начать, затем сохранить или отменить | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| Отменить часть транзакции | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| Более строгая изоляция | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| Заблокировать прочитанные строки | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| Очередь задач: пропускать заблокированные строки | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
Пользователи, роли и привилегии
| Что делает | SQL |
|---|
| Создать роль с правом входа | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| Доступ к таблице только на чтение | GRANT SELECT ON users TO app; |
| Доступ на чтение ко всем таблицам схемы | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| Отозвать привилегию | REVOKE SELECT ON users FROM app; |
| Сменить пароль | ALTER ROLE app PASSWORD 'new-secret'; |
| Показать текущего пользователя | SELECT current_user; |
Обслуживание и мониторинг
| Что делает | SQL |
|---|
| Выполняющиеся запросы | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| Отменить запрос | SELECT pg_cancel_backend(12345); |
| Завершить подключение | SELECT pg_terminate_backend(12345); |
| Размер таблицы вместе с индексами | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| Размер базы данных | SELECT pg_size_pretty(pg_database_size(current_database())); |
| Обновить статистику планировщика | ANALYZE orders; |
| Освободить место от мёртвых строк | VACUUM orders; |
| Перезаписать таблицу до минимального размера (блокирует её) | VACUUM FULL orders; |
| Показать параметр | SHOW work_mem; |
| Изменить параметр для текущего сеанса | SET work_mem = '64MB'; |
Резервное копирование и восстановление
Команды оболочки из клиентских утилит PostgreSQL.
| Что делает | Команда |
|---|
| Выгрузить базу данных в SQL-файл | pg_dump -U postgres shop > shop.sql |
| Выгрузить в формате custom | pg_dump -Fc -U postgres shop > shop.dump |
| Выгрузить одну таблицу | pg_dump -t orders shop > orders.sql |
| Восстановить SQL-дамп | psql -d shop -f shop.sql |
| Восстановить дамп в формате custom | pg_restore -d shop shop.dump |
| Выгрузить все базы данных и роли | pg_dumpall > cluster.sql |
| Экспортировать результат запроса в CSV (из psql) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| Импортировать CSV (из psql) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
Все команды psql и инструкции PostgreSQL, которые вам нужны, в таблицах для копирования: сначала подключение и команды \d, затем таблицы, upsert, JSONB, даты, оконные функции, индексы, роли, резервные копии и обслуживание.
Здесь приведён синтаксис PostgreSQL 17, и большая его часть работает и в более ранних версиях. SQL-инструкции проверены на PostgreSQL 17; чтобы попробовать любую из них, вставьте её в playground для PostgreSQL, в котором есть готовые примеры таблиц.