Справочник по SQL

SELECT col1, col2 FROM table_name;

Выбрать колонки из таблицы.

SELECT * FROM t
WHERE col = 5 AND status = 'active';

Фильтр строк по условию: =, <>, <, >, AND, OR, IN, BETWEEN, LIKE.

SELECT * FROM t ORDER BY created_at DESC;

Сортировка результата. ASC по возрастанию (по умолчанию), DESC по убыванию.

SELECT * FROM t ORDER BY id LIMIT 10;

Ограничить количество строк в результате.

SELECT * FROM a JOIN b ON a.id = b.a_id;

Только те строки, для которых есть пара в обеих таблицах.

SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

Все строки из левой таблицы; справа — NULL, если пары нет.

SELECT u.name
FROM users u
JOIN orders o ON o.user_id = u.id;

Короткие имена для таблиц — обязательны при одинаковых колонках.

SELECT * FROM sizes CROSS JOIN colors;

Декартово произведение — каждая строка слева с каждой справа. Для генерации всех комбинаций.

SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;

Все строки обеих таблиц; NULL там, где пары нет. В MySQL нет FULL JOIN — эмулируют через LEFT ∪ RIGHT.

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Таблица соединяется сама с собой через алиасы — для иерархий «сотрудник → руководитель».

SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Строки слева, у которых СПРАВА нет пары — «пользователи без заказов». Альтернатива NOT EXISTS.

SELECT COUNT(*), COUNT(email) FROM users;

Считает строки в группе. COUNT(*) — все строки, COUNT(col) — только не-NULL, COUNT(DISTINCT col) — уникальные значения.

SELECT SUM(amount) FROM orders;

Сумма числовых значений в группе. NULL игнорируются. Для пустой группы возвращает NULL (не 0).

SELECT AVG(price) FROM products;

Среднее арифметическое. NULL не входят в делитель. Целочисленную колонку приводите к numeric, иначе дробная часть отбросится.

SELECT MIN(created_at) FROM orders;

Минимальное значение в группе. Работает с числами, датами и строками. NULL игнорируются.

SELECT MAX(created_at) FROM orders;

Максимальное значение в группе. Работает с числами, датами и строками. NULL игнорируются.

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

Сгруппировать строки. Все колонки SELECT, кроме агрегатов, должны быть в GROUP BY.

SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

WHERE для агрегатов — фильтр уже сгруппированных данных.

SELECT DISTINCT country FROM users;

Убрать дубликаты строк в результате.

COUNT(*) FILTER (WHERE type = 'view') AS views

Условный агрегат: считаем COUNT/SUM только по строкам, где WHERE-условие верно. Заменяет три отдельных запроса на один.

STRING_AGG(name, ', ' ORDER BY created_at)

Склеить значения в строку с разделителем. ORDER BY внутри гарантирует порядок.

ARRAY_AGG(amount ORDER BY created_at)

Собрать значения в массив. Удобно для аудита истории на одну строку отчёта.

GROUP BY GROUPING SETS ((kind), (user_id), ())

Несколько уровней группировки в одном запросе — построчно: по kind, по user_id и общий итог.

GROUP BY ROLLUP (DATE_TRUNC('month', ts))

Тот же группировочный уровень + строка с общим итогом (одна NULL-строка в конце).

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)

Медиана / квантили. Устойчивее к выбросам, чем AVG.

SELECT tag FROM articles, UNNEST(tags) tag

Развернуть массив в строки — одна строка на каждый элемент массива.

SELECT COUNT(DISTINCT user_id) FROM events;

Считает число уникальных значений. На больших таблицах дорог — для приблизительной оценки используйте APPROX_COUNT_DISTINCT / HLL.

SELECT BOOL_AND(active), BOOL_OR(is_admin) FROM users;

Агрегаты по булевым: BOOL_AND — true, если ВСЕ строки true; BOOL_OR — если хотя бы одна. NULL игнорируются.

SELECT dept, EVERY(salary > 0) FROM emp GROUP BY dept;

Стандартный SQL-синоним BOOL_AND — true, если условие выполнено во всех строках группы.

SELECT STDDEV_SAMP(amount), STDDEV_POP(amount) FROM orders;

Стандартное отклонение: _SAMP по выборке (делитель n-1), _POP по всей совокупности (делитель n). Голый STDDEV = STDDEV_SAMP.

SELECT VAR_SAMP(amount), VAR_POP(amount) FROM orders;

Дисперсия — квадрат стандартного отклонения. _SAMP по выборке, _POP по совокупности. Голый VARIANCE = VAR_SAMP.

SELECT MODE() WITHIN GROUP (ORDER BY status) FROM tickets;

Мода — самое частое значение в группе. При равной частоте берётся первое по ORDER BY.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) FROM orders;

Дискретный перцентиль — возвращает реально существующее значение из данных, в отличие от PERCENTILE_CONT, который интерполирует.

SELECT BIT_OR(flags), BIT_AND(flags) FROM permissions;

Побитовые агрегаты по integer-колонке: BIT_OR собирает все выставленные биты, BIT_AND — общие для всех строк. Для флаговых масок.

SELECT JSONB_AGG(t ORDER BY t.id) FROM tasks t;

Собрать строки в JSON-массив — удобно отдавать вложенные данные одним запросом. JSONB_AGG хранит как jsonb (быстрее, без дублей ключей).

SELECT JSONB_OBJECT_AGG(key, value) FROM settings;

Свернуть пары ключ-значение в один JSON-объект. Идеально для превращения таблицы настроек в карту.

SELECT CORR(price, sales) FROM products;

Коэффициент корреляции Пирсона между двумя колонками: от -1 до 1. Мера линейной связи.

SELECT REGR_SLOPE(y, x), REGR_INTERCEPT(y, x) FROM points;

Наклон и пересечение линии линейной регрессии y по x — тренд одним агрегатом, без внешней матстатистики.

SELECT REGR_R2(y, x) FROM points;

Коэффициент детерминации R² регрессии y по x: 0..1, насколько хорошо линия описывает данные.

SELECT user_id,
  MAX(amount) FILTER (WHERE kind = 'deposit')  AS deposit,
  MAX(amount) FILTER (WHERE kind = 'withdraw') AS withdraw
FROM tx GROUP BY user_id;

Разложить строки в колонки (pivot): MAX/SUM с FILTER по каждой категории. Заменяет ручной набор CASE-агрегатов.

SELECT region, product, SUM(amount)
FROM sales
GROUP BY CUBE (region, product);

Все возможные комбинации группировок сразу: по region, по product, по обоим и общий итог. Для кросс-табличных отчётов.

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

Проверить, что значение есть в списке, который сам собирается запросом.

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Вернуть строку, только если внутренний запрос нашёл хотя бы одну строку.

SELECT
  name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;

Подзапрос, возвращающий одно значение — можно поставить в SELECT.

SELECT id FROM a
UNION ALL
SELECT id FROM b;

Склеить результаты двух запросов (одинаковое число колонок и совместимые типы). UNION убирает дубликаты, UNION ALL оставляет (и быстрее).

SELECT user_id FROM purchases
INTERSECT
SELECT user_id FROM refunds;

Строки, которые есть в ОБОИХ запросах. В MySQL — начиная с 8.0.31.

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM banned;

Строки из первого запроса, которых НЕТ во втором. В Oracle это MINUS.

SELECT
  name,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
FROM players;

Уникальный номер для каждой строки внутри окна.

RANK() OVER (PARTITION BY dept ORDER BY salary DESC)

Ранг с пропусками (RANK) или без пропусков (DENSE_RANK) при равных значениях.

SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)

Разбить окно по группам — агрегат считается в пределах группы.

LAG(price, 1) OVER (ORDER BY date)

Значение из предыдущей (LAG) или следующей (LEAD) строки окна.

NTILE(4) OVER (ORDER BY score DESC)

Разбить строки на N равных групп по порядку. На неровных N группы получаются 3-3-2-2 (лишние идут в первые).

LAST_VALUE(score) OVER (
  PARTITION BY team_id ORDER BY score DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Первое / последнее значение в окне. Для LAST_VALUE обязательно расширить фрейм через ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — иначе фрейм по умолчанию обрежет правый край.

PERCENT_RANK() OVER (ORDER BY score DESC, player_id)

Процентильный ранг 0..1. Tiebreaker во второй колонке делает результат детерминированным при равных score.

NTH_VALUE(amount, 2) OVER (
  PARTITION BY customer_id ORDER BY amount DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

N-ое значение из окна. Тоже требует расширенного фрейма.

AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Скользящие окна: «последние 7 дней включительно», «3-дневное среднее» и т.п.

WITH active AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active WHERE country = 'RU';

Именованный временный результат — удобно разбивать сложный запрос на шаги.

WITH a AS (...), b AS (...)
SELECT * FROM a JOIN b ON ...;

Несколько CTE через запятую, читаются сверху вниз.

WITH RECURSIVE chain AS (
  SELECT id, manager_id FROM emp WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id FROM emp e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;

Обход иерархии: стартовый запрос → UNION ALL → шаг рекурсии. Идеально для оргсхем, графов, цепочек.

FROM customers c
LEFT JOIN LATERAL (
  SELECT * FROM orders WHERE customer_id = c.id
  ORDER BY amount DESC LIMIT 2
) l ON true

Подзапрос видит колонки внешнего запроса. Удобно для «top-N на каждого X». LEFT JOIN LATERAL … ON true сохраняет внешние строки без заказов; форма через запятую их молча отбрасывает.

generate_series('2024-01-01'::date, '2024-01-15'::date, '1 day')

Сгенерировать календарь / шкалу. Включает обе границы. Базовый трюк для заполнения пропусков нулями.

INSERT INTO t (col1, col2) VALUES (1, 'a'), (2, 'b');

Добавить строки в таблицу.

UPDATE t SET col = 'x' WHERE id = 5;

Изменить существующие строки. WHERE обязателен — без него обновятся ВСЕ строки.

DELETE FROM t WHERE id = 5;

Удалить строки. WHERE обязателен.

INSERT INTO t (id) VALUES (1)
ON CONFLICT (id) DO NOTHING;

Идемпотентная вставка — повторный запуск молча пропускает уже существующие строки.

INSERT INTO t (id, n) VALUES (1, 1)
ON CONFLICT (id) DO UPDATE
  SET n = t.n + EXCLUDED.n;

UPSERT: вставить или обновить. EXCLUDED.col — то значение, которое мы пытались вставить.

MERGE INTO t USING src ON t.id = src.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Postgres 15+ — UPSERT-альтернатива с ветками MATCHED / NOT MATCHED и условиями в каждой.

INSERT INTO t (name) VALUES ('a')
RETURNING id, name;

Вернуть только что вставленные / обновлённые / удалённые строки сразу после операции — без второго round-trip.

DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.country = 'US';

JOIN-стилевой DELETE — фильтр по другой таблице без подзапроса.

UPDATE customers c SET total = s.total
FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) s
WHERE c.id = s.customer_id;

Массовый UPDATE с агрегатом из подзапроса.

WITH moved AS (
  DELETE FROM orders WHERE old RETURNING *
)
INSERT INTO archive SELECT * FROM moved;

Атомарная архивация: переносим строки одним statement-ом, без race-окна между DELETE и INSERT.

CREATE TABLE t (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Создать таблицу с типизированными колонками.

ALTER TABLE t ADD COLUMN created_at TIMESTAMP;

Изменить структуру существующей таблицы.

ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

Запрет на невалидные значения уровнем БД, а не кодом.

FOREIGN KEY (post_id) REFERENCES posts(id)
  ON DELETE CASCADE   -- or SET NULL / RESTRICT

Что делать с дочерней строкой, когда удаляют родителя: каскад, обнулить FK или запретить.

ALTER TABLE t
  ADD CONSTRAINT fk REFERENCES p(id) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT fk;

Добавить FK на большой проде без жёсткого lock-а: NOT VALID — моментально, VALIDATE — без блокировки писателей.

total NUMERIC GENERATED ALWAYS AS (price * (1 + tax)) STORED

Значение колонки вычисляется автоматически по формуле — формула живёт в одном месте.

CREATE UNIQUE INDEX u ON users (email)
WHERE deleted_at IS NULL;

Уникальность только по живым строкам — для soft-delete, чтобы повторно регистрироваться можно было после удаления.

CREATE TABLE logs (...) PARTITION BY RANGE (ts);
CREATE TABLE logs_2024 PARTITION OF logs
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Разрезать большую таблицу по диапазону. Старые партиции дропаются за миллисекунды.

CREATE TRIGGER touch BEFORE UPDATE ON notes
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

Авто-логика на уровне БД — например, обновлять updated_at без участия кода.

CREATE MATERIALIZED VIEW v AS SELECT ...;
REFRESH MATERIALIZED VIEW v;

Закэшированный результат тяжёлого запроса. Обновляется по расписанию через REFRESH.

LOWER(name), UPPER(code), LENGTH(text)

Нижний/верхний регистр, длина строки.

CONCAT(first_name, ' ', last_name)

Склеить строки. В PostgreSQL также работает оператор || (в MySQL по умолчанию || — это логическое ИЛИ, не конкатенация).

EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)

Достать часть даты — год, месяц, день.

DATE_TRUNC('month', created_at)

Округлить дату/время вниз до периода (день/неделя/месяц). Базовый инструмент для группировки по времени.

WHERE created_at >= NOW() - INTERVAL '7 days'

Текущий момент (NOW()) / сегодня (CURRENT_DATE) и арифметика с интервалами — «за последние 7 дней».

CAST(price AS INTEGER)   -- or price::int

Привести значение к другому типу. CAST(x AS type) — стандарт; x::type — короткая запись PostgreSQL.

TRIM(name), SUBSTRING(code FROM 1 FOR 3), REPLACE(phone, '-', '')

Обрезать пробелы, вырезать подстроку, заменить фрагмент. Базовая чистка строк.

SPLIT_PART(email, '@', 2)   -- domain from an e-mail

Разбить строку по разделителю и взять N-ю часть. В MySQL — SUBSTRING_INDEX.

WHERE name ILIKE '%ivan%'

Регистронезависимый LIKE (PostgreSQL). В MySQL обычный LIKE и так нечувствителен к регистру при стандартной коллации.

LEFT(code, 3), RIGHT(phone, 4)

Взять N символов с начала (LEFT) или с конца (RIGHT) строки.

POSITION('@' IN email), STRPOS(email, '@')

Позиция первого вхождения подстроки (с 1), 0 если не найдено. STRPOS — короткая форма PostgreSQL.

LPAD(id::text, 6, '0'), RPAD(name, 20, ' ')

Дополнить строку до нужной длины слева (LPAD) или справа (RPAD). Классика — нули перед номером.

INITCAP('john DOE')   -- John Doe

Первая буква каждого слова в верхний регистр, остальные в нижний. В MySQL отсутствует.

REPEAT('ab', 3)   -- ababab

Повторить строку N раз. Удобно для заполнителей и простых текстовых диаграмм.

REVERSE(name)

Перевернуть строку посимвольно. Иногда используют для индексации по суффиксу.

char_length(name), char_length('açai')   -- 4

Длина строки в СИМВОЛАХ (не байтах) — важно для UTF-8. Синоним character_length.

REGEXP_REPLACE(phone, '[^0-9]', '', 'g')

Замена по регулярному выражению. Флаг 'g' — заменить все вхождения, иначе только первое.

SELECT (REGEXP_MATCHES(url, '/(\d+)'))[1] AS id;

Вернуть захваченные группы регулярки как массив. С флагом 'g' даёт по строке на каждое совпадение.

REGEXP_SPLIT_TO_ARRAY('a, b,c', '\s*,\s*')

Разбить строку по регулярному разделителю в массив. Есть и …TO_TABLE — сразу в строки.

TRANSLATE(code, 'abc', 'xyz')   -- a->x, b->y, c->z

Посимвольная замена по двум наборам. Лишние символы из первого набора удаляются. Не путать с REPLACE.

BTRIM(code, '0'), LTRIM(s), RTRIM(s, '/')

Срезать заданные символы с обоих концов (BTRIM), слева (LTRIM) или справа (RTRIM); по умолчанию — пробелы.

FORMAT('Hi %s, id=%L', name, id)

Шаблонная сборка строки: %s — значение, %I — идентификатор, %L — безопасный литерал. Незаменимо в динамическом SQL.

WHERE STARTS_WITH(path, '/api/')

Начинается ли строка с префикса — читабельнее, чем LIKE 'x%'. Есть с PostgreSQL 11.

ascii('A')   -- 65
chr(65)      -- A

Код первого символа (ascii) и символ по коду (chr). В MySQL обратная функция называется CHAR.

to_hex(255)   -- 'ff'

Перевести целое число в шестнадцатеричную строку. Удобно для цветов, масок битов, отладки.

ROUND(3.14159)        -- 3
ROUND(2.5)            -- banker? no: 3

Округлить до ближайшего целого. Половинки округляются от нуля (2.5 → 3).

ROUND(3.14159, 2)     -- 3.14
ROUND(12345.6, -2)    -- 12300

Округлить до n знаков после запятой; отрицательный n округляет влево от точки. Работает только на numeric, не на float.

CEIL(4.1)   -- 5
CEIL(-4.1)  -- -4

Округление вверх до ближайшего целого. CEILING — синоним.

FLOOR(4.9)   -- 4
FLOOR(-4.1)  -- -5

Округление вниз до ближайшего целого. Для отрицательных уходит дальше от нуля.

TRUNC(3.99)     -- 3
TRUNC(3.456, 2) -- 3.45

Отбросить дробную часть (к нулю), без округления. В MySQL это TRUNCATE(x, n).

ABS(-7)   -- 7

Модуль числа — абсолютное значение без знака.

MOD(10, 3)   -- 1

Остаток от деления. Полезен для «каждой N-й строки» и чётности; знак результата идёт за делимым.

POWER(2, 10)   -- 1024

Возведение в степень. POW — синоним.

SQRT(144)   -- 12

Квадратный корень. От отрицательного аргумента — ошибка.

EXP(1)    -- 2.7182818...
LN(2.718) -- ~1

Экспонента e^x и натуральный логарифм (по основанию e). LN(0) и LN(отриц.) — ошибка.

LOG(100)     -- 2  (base 10)
LOG(2, 8)    -- 3  (base 2)

В PostgreSQL LOG(x) — по основанию 10, LOG(b, x) — по произвольному основанию. Внимание: в MySQL LOG(x) — натуральный логарифм.

SIGN(-42)  -- -1
SIGN(0)    -- 0

Знак числа: -1, 0 или 1. Удобно для ветвления по направлению изменения.

GREATEST(a, b, c), LEAST(a, b, c)

Максимум / минимум среди аргументов в одной строке (не агрегат). NULL-аргументы игнорируются.

SELECT * FROM t ORDER BY RANDOM() LIMIT 5;

Случайное число в [0,1). В MySQL — RAND(). ORDER BY RANDOM() даёт случайную выборку, но дорог на больших таблицах.

DIV(7, 2)   -- 3
7 / 2       -- 3 when both are int

Целочисленное деление с отбрасыванием остатка. В PostgreSQL / уже делит нацело, если оба операнда целые; в MySQL для этого есть оператор DIV.

WIDTH_BUCKET(score, 0, 100, 10)  -- bucket 1..10

Разложить значение по гистограммным корзинам равной ширины между двумя границами. Для распределений и группировки по диапазонам.

AGE(end_ts, start_ts)   -- or AGE(birthday) vs now
AGE('2024-03-01', '2024-01-15')

Разница между двумя датами как интервал (годы/месяцы/дни), а не в секундах. С одним аргументом считает от текущей даты — удобно для возраста.

DATE_PART('hour', created_at), DATE_PART('dow', created_at)

Функциональная форма EXTRACT — достаёт часть даты/времени числом. Поле задаётся строкой, поэтому удобно подставлять переменной.

EXTRACT(EPOCH FROM (ended_at - started_at)) AS seconds

Перевести интервал или timestamp в секунды (Unix-время). Базовый способ померить длительность в секундах — затем дели на 60/3600.

TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI'), TO_CHAR(amount, 'FM999G999D00')

Отформатировать дату/число в строку по шаблону (YYYY, MM, DD, HH24...). Шаблоны в PostgreSQL отличаются от MySQL DATE_FORMAT.

TO_DATE('2024-03-15', 'YYYY-MM-DD')

Разобрать строку в дату по явному шаблону. Надёжнее, чем приведение ::date, когда формат нестандартный.

TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
TO_TIMESTAMP(1710512400)   -- from Unix epoch

Разобрать строку в timestamp по шаблону, либо построить timestamptz из Unix-секунд (числовой аргумент).

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP;

Время начала транзакции: CURRENT_TIMESTAMP с часовым поясом (timestamptz), LOCALTIMESTAMP — без. Не меняется в течение одной транзакции.

SELECT CURRENT_DATE, CURRENT_TIME;

Только сегодняшняя дата (CURRENT_DATE) или только время (CURRENT_TIME) — без скобок, это не функции, а специальные значения SQL.

SELECT order_date + 7, due_date - 1, end_dt - start_dt AS days;

К date можно прибавлять/вычитать целые дни (date + 7). Разность двух date даёт число дней (integer), двух timestamp — интервал.

make_date(2024, 3, 15), make_time(14, 30, 0)

Собрать дату или время из отдельных чисел год/месяц/день, час/минута/секунда — без возни с форматными строками.

make_timestamp(2024, 3, 15, 14, 30, 0)
make_interval(days => 10, hours => 2)

Собрать timestamp или интервал из числовых компонентов. make_interval принимает именованные аргументы (days =>, hours =>).

ts_utc AT TIME ZONE 'Europe/Moscow'
local_ts AT TIME ZONE 'UTC'

Перевести момент в другой часовой пояс. На timestamptz даёт локальное время в зоне; на timestamp без зоны — наоборот, трактует его как время в зоне.

JUSTIFY_HOURS(INTERVAL '36 hours')   -- 1 day 12:00:00

Нормализовать интервал: перевести лишние часы в дни, дни в месяцы. Делает «50 hours» читаемым как «2 days 02:00:00».

DATE_BIN('15 minutes', ts, TIMESTAMP '2024-01-01')

Округлить timestamp вниз к началу бакета произвольной ширины (например, 15 минут) от опорной точки. Гибче DATE_TRUNC. Postgres 14+.

(start_a, end_a) OVERLAPS (start_b, end_b)

Проверить, пересекаются ли два временных интервала. Удобно для поиска конфликтов бронирований/смен.

now()::timestamptz, '2024-03-15 10:00'::timestamp

timestamptz хранит момент в UTC и учитывает пояс при выводе; timestamp — «наивное» время без зоны. Для событий почти всегда нужен timestamptz.

CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 70 THEN 'B'
  ELSE 'C'
END

Условная логика прямо в SELECT — аналог if/else.

COALESCE(nickname, full_name, 'Anonymous')

Вернуть первое не-NULL значение из списка.

NULLIF(divisor, 0)

Превратить значение в NULL, если оно равно второму аргументу. Помогает избежать деления на ноль.

-- = NULL is never true — use IS NULL
WHERE deleted_at IS NULL
-- NULL-safe equality:
WHERE a IS DISTINCT FROM b

Сравнение с NULL через = всегда даёт «неизвестно» (не TRUE/FALSE). Для проверки — IS NULL; для NULL-безопасного равенства — IS DISTINCT FROM.

payload->>'target'

Достать значение из JSONB как text — для подстановки в строки и сравнений.

payload @> '{"plan":"pro"}'

Содержит ли JSONB указанный фрагмент. Использует GIN-индекс — быстро на больших таблицах.

CREATE INDEX idx ON events USING GIN (payload jsonb_path_ops)

Оптимальный индекс под @>-запросы по JSONB. Компактнее, чем дефолтный jsonb_ops.

data->'user'->>'name'   -- -> keeps json, ->> as text

-> достаёт поле/элемент как jsonb (для дальнейшей навигации), ->> — как text. Ключ строкой, индекс массива числом.

data #>> '{address,city}'   -- text at a nested path

Достать значение по вложенному пути массивом ключей: #> как jsonb, #>> как text. Короче цепочки ->.

jsonb_build_object('id', id, 'name', name)

Собрать JSON-объект из чередующихся пар ключ, значение. Типы значений сохраняются (числа остаются числами).

jsonb_build_array(id, name, created_at)

Собрать JSON-массив из переданных аргументов любых типов.

jsonb_agg(item ORDER BY created_at)

Агрегат: собрать строки группы в JSON-массив. ORDER BY внутри фиксирует порядок элементов.

SELECT e FROM t, jsonb_array_elements(t.tags) AS e

Развернуть JSON-массив в строки — одна строка на элемент. Вариант _text возвращает text вместо jsonb.

jsonb_array_length(data->'items')

Длина JSON-массива. Ошибка, если значение не массив — оберни проверкой jsonb_typeof.

jsonb_set(data, '{address,city}', '"Lima"')

Вернуть копию JSON с заменённым значением по пути. create_missing=true (по умолчанию) добавляет ключ, если его нет.

SELECT key, value FROM jsonb_each(data)

Развернуть JSON-объект в строки (key, value) — по паре на ключ. Вариант _text даёт value как text.

SELECT jsonb_object_keys(data)

Вернуть имена ключей JSON-объекта верхнего уровня, по строке на ключ.

data ? 'email'        -- has key?
data ?| array['a','b'] -- any of these keys?

Проверка наличия ключей: ? один ключ, ?| хотя бы один из, ?& все. Работают по верхнему уровню и опираются на GIN-индекс.

data || '{"verified":true}'

Слить два JSONB: правые ключи перезаписывают левые (мелкое слияние, без рекурсии). Удобно для частичного апдейта.

data - 'temp'              -- drop a key
data #- '{address,zip}'    -- drop at a path

Удалить ключ/элемент: - по ключу или индексу верхнего уровня, #- по вложенному пути. Возвращает новый JSONB.

to_jsonb(row_var)   -- whole row as a json object

Превратить любое значение/строку/массив SQL в jsonb. Целую строку таблицы превращает в JSON-объект колонка → значение.

jsonb_typeof(data->'price')   -- 'number','string',...

Тип JSON-значения строкой: object, array, string, number, boolean, null. Полезно перед jsonb_array_length и т.п.

jsonb_pretty(data)

Отформатировать JSONB с отступами для читаемого вывода/отладки.

CREATE INDEX i ON orders (id) WHERE status = 'pending';

Индекс только по «горячему» подмножеству строк — компактнее и быстрее на скан.

CREATE INDEX i ON orders (customer_id, created_at DESC);

Покрывает фильтр + сортировку одним проходом. Порядок колонок важен.

-- bad : WHERE EXTRACT(YEAR FROM ts) = 2024
-- good: WHERE ts >= '2024-01-01' AND ts < '2025-01-01'

Не оборачивай колонку в функцию — иначе индекс не подключится. Перепиши на диапазон.

-- NOT IN collapses to 0 rows on any NULL
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

NOT IN тихо ломается на любом NULL в подзапросе. NOT EXISTS — NULL-safe.

CREATE INDEX CONCURRENTLY i ON events (user_id, kind);

Построить индекс на горячей таблице без жёсткого lock-а. Внутри транзакции запрещён.

EXPLAIN SELECT * FROM orders WHERE user_id = 5;

Показать план запроса без выполнения: какие сканы (Seq Scan / Index Scan), порядок соединений, оценка строк.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 5;

Выполнить запрос и показать РЕАЛЬНОЕ время и число строк против оценки. Большой разрыв оценка↔факт — признак устаревшей статистики или отсутствующего индекса.

BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

Залочить строки до конца транзакции. Стандарт для переводов между счетами.

UPDATE accounts SET balance = balance - 200
WHERE id = 1 AND balance >= 200;

Атомарная проверка-и-обновление в одной команде. Если строки не обновились — на бэке возвращаем «недостаточно средств».

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED LIMIT 1;

Очередь воркеров: каждый забирает свою джобу, не толкаясь на залоченных другими.

UPDATE counters SET n = n + 1 WHERE id = 1;

Один UPDATE безопасно инкрементит счётчик при параллельном доступе. SELECT-then-UPDATE теряет инкременты.

GRANT SELECT, INSERT ON orders TO analyst;

Выдать права на объект роли/пользователю. Перечисляем нужные действия (SELECT, INSERT, UPDATE, DELETE).

REVOKE INSERT ON orders FROM analyst;

Отозвать ранее выданные права.

CREATE ROLE analyst LOGIN PASSWORD 'secret';

Создать роль (пользователя/группу). Права выдаются роли, а пользователи в неё включаются.

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

Типовой паттерн «только чтение»: доступ к схеме + SELECT на все таблицы + правило для будущих таблиц через ALTER DEFAULT PRIVILEGES.