Menu
CoddyTech

Шпаргалка по PostgreSQL

Последнее обновление

Команды 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;
Очистить таблицу и сбросить identityTRUNCATE orders, users RESTART IDENTITY;
Удалить таблицу, если она существуетDROP TABLE IF EXISTS products;
Точное десятичное число для денегnumeric(10,2)
Дата и время с часовым поясомtimestamptz
Случайный UUIDSELECT 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);
Добавить ограничение CHECKALTER 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 и OFFSETSELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
Стандартное ограничение числа строкSELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY;
Приведение типа через ::SELECT '42'::integer + 1;
Значение вместо NULLSELECT 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
Вставить строку и получить новый idINSERT 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
Получить поле как JSONSELECT 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;
Строки в виде массива JSONSELECT 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;
Конкатенация с пропуском NULLSELECT 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
Дать подзапросу имя через WITHWITH 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
Выгрузить в формате custompg_dump -Fc -U postgres shop > shop.dump
Выгрузить одну таблицуpg_dump -t orders shop > orders.sql
Восстановить SQL-дампpsql -d shop -f shop.sql
Восстановить дамп в формате custompg_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, в котором есть готовые примеры таблиц.

Часто задаваемые вопросы по шпаргалке PostgreSQL

Что такое команды psql?
psql является терминальным клиентом PostgreSQL. Помимо SQL, он понимает метакоманды, которые начинаются с обратной косой черты: \l выводит список баз данных, \c подключается к одной из них, \dt выводит список таблиц, \d users описывает таблицу, а \q завершает работу. Они выполняются в самом psql, поэтому не требуют точки с запятой и не работают в других клиентах.
Как вывести список всех таблиц в PostgreSQL?
В psql введите \dt (или \dt *.* для всех схем). На SQL, из любого клиента: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
Отличается ли SQL в PostgreSQL от MySQL?
Основа (SELECT, JOIN, GROUP BY) одинакова. PostgreSQL добавляет ILIKE, приведение типов через ::, RETURNING, DISTINCT ON, upsert через ON CONFLICT, массивы и JSONB, а там, где MySQL использует AUTO_INCREMENT, применяет identity-столбцы.
Чем JSON отличается от JSONB?
json хранит текст в том виде, в каком он записан; jsonb хранит разобранную двоичную форму, отбрасывает повторяющиеся ключи и пробелы, поддерживает индексы (GIN) и операторы вроде @> и ?. Используйте jsonb, если вам не нужен точный исходный текст.
Можно ли попрактиковаться с этими запросами онлайн?
Да. Playground для PostgreSQL выполняет любой запрос на настоящей базе данных PostgreSQL 17 прямо в браузере, с примерами таблиц, которые можно изменять. Для инструкций, которым нужны привилегии на сервере, например CREATE DATABASE или CREATE ROLE, нужен собственный PostgreSQL.
Эта шпаргалка по PostgreSQL бесплатна?

Да. Её можно бесплатно читать, копировать и распечатывать, регистрация не требуется.

Иллюстрация языков программирования Coddy

Изучайте PostgreSQL с Coddy

Начать