PostgreSQL шпаргалка

CTE — Common Table Expressions

CTE задаёт именованный подзапрос перед основным запросом. Делает код читаемым и позволяет ссылаться на один результат несколько раз.

WITH
  monthly_sales AS (
    SELECT
      DATE_TRUNC('month', order_date) AS month,
      user_id,
      SUM(amount) AS total
    FROM orders
    GROUP BY 1, 2
  ),
  user_avg AS (
    SELECT user_id, AVG(total) AS avg_monthly
    FROM monthly_sales
    GROUP BY user_id
  )
SELECT
  ms.month, ms.user_id, ms.total,
  ua.avg_monthly,
  ms.total - ua.avg_monthly AS diff_from_avg
FROM monthly_sales ms
JOIN user_avg ua USING(user_id)
ORDER BY ms.month, ms.user_id;

Рекурсивный CTE

Для обхода иерархий: дерево категорий, организационная структура, граф зависимостей.

WITH RECURSIVE org_tree AS (
  -- Базовый случай: корневые узлы
  SELECT
    id, name, manager_id,
    0          AS depth,
    ARRAY[id]  AS path,
    name::text AS full_path
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Рекурсивный шаг
  SELECT
    e.id, e.name, e.manager_id,
    ot.depth + 1,
    ot.path || e.id,
    ot.full_path || ' → ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE e.id != ALL(ot.path)  -- защита от циклов
)
SELECT REPEAT('  ', depth) || name AS tree, full_path
FROM org_tree ORDER BY path;

MATERIALIZED CTE

WITH expensive_calc AS MATERIALIZED (
  -- PostgreSQL выполнит ровно один раз и сохранит результат
  SELECT * FROM big_table WHERE complex_condition = TRUE
)
SELECT * FROM expensive_calc WHERE x = 1
UNION ALL
SELECT * FROM expensive_calc WHERE y = 2;

Типы индексов

Составной, частичный и функциональный индекс

-- Составной: порядок колонок критичен
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Частичный: индексируем только активные заказы
CREATE INDEX idx_orders_active ON orders (created_at)
  WHERE status = 'active';

-- Функциональный: для запросов с выражением
CREATE INDEX idx_email_lower ON users (LOWER(email));
-- WHERE LOWER(email) = '[email protected]' — пойдёт по индексу

-- Покрывающий: не надо ходить в heap за доп. колонками
CREATE INDEX idx_orders_covering ON orders (user_id)
  INCLUDE (status, amount);

Когда индекс не используется

-- Функция над колонкой без функционального индекса:
WHERE UPPER(name) = 'IVAN'               -- ✗ seq scan

-- Неявное приведение типов:
WHERE phone_number = 9161234567          -- ✗ если phone_number varchar

-- OR с неиндексированной колонкой:
WHERE user_id = 5 OR unindexed_col = 'x' -- ✗

-- Выборка > ~10% таблицы — seq scan может быть быстрее

Уровни изоляции

BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
  UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

BEGIN ISOLATION LEVEL REPEATABLE READ;
  ...
COMMIT;

Блокировки

-- Явная строчная блокировка:
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- Взять строку и не ждать, если она занята (очередь задач):
SELECT * FROM jobs WHERE status = 'pending'
LIMIT 1 FOR UPDATE SKIP LOCKED;

SAVEPOINT

BEGIN;
  INSERT INTO logs VALUES (1, 'step 1');
  SAVEPOINT sp1;
  INSERT INTO logs VALUES (2, 'step 2');
  ROLLBACK TO SAVEPOINT sp1;   -- отменяем только step 2
  INSERT INTO logs VALUES (3, 'step 3');
COMMIT;
-- В итоге: строки 1 и 3, строки 2 нет

JSONB запросы

CREATE INDEX idx_events_gin ON events USING GIN(data);

-- -> возвращает JSON, ->> возвращает text
SELECT data->>'user_id', data->'meta'->>'ip'
FROM events
WHERE data @> '{"type": "login"}'   -- содержит
  AND data ? 'session_id';          -- ключ существует

SELECT data->>'type' AS event_type, COUNT(*)
FROM events GROUP BY 1 ORDER BY 2 DESC;

GROUPING SETS, ROLLUP, CUBE

-- ROLLUP: итого по каждому уровню иерархии
SELECT year, quarter, month, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, quarter, month);
-- строки: (year,q,m), (year,q), (year), (итого)

-- CUBE: все возможные комбинации (2^N)
GROUP BY CUBE(region, product, channel)

-- GROUPING SETS: явно нужные комбинации
GROUP BY GROUPING SETS(
  (region, product),
  (region),
  ()   -- гранд-итого
);

LATERAL JOIN

-- Топ-3 заказа для каждого пользователя:
SELECT u.id, u.name, o.order_id, o.amount
FROM users u
CROSS JOIN LATERAL (
  SELECT order_id, amount
  FROM orders
  WHERE user_id = u.id   -- ← ссылка на внешний u.id
  ORDER BY amount DESC
  LIMIT 3
) o;

UPSERT и MERGE

INSERT INTO user_stats (user_id, logins, last_seen)
VALUES (42, 1, NOW())
ON CONFLICT (user_id) DO UPDATE SET
  logins    = user_stats.logins + EXCLUDED.logins,
  last_seen = EXCLUDED.last_seen;

-- MERGE (PostgreSQL 15+)
MERGE INTO target t USING source s ON t.id = s.id
WHEN MATCHED AND s.deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.name;

Статистика и мониторинг

-- Обновить статистику планировщика:
ANALYZE orders;

-- Неиспользуемые индексы:
SELECT tablename, indexname, idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 100
ORDER BY pg_relation_size(indexrelid) DESC;

-- Топ медленных запросов:
SELECT ROUND(mean_exec_time::numeric, 2) AS avg_ms, calls, query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

Функция vs Процедура

Минимальная функция

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT a + b;
$$;

SELECT add_numbers(3, 5);  -- → 8

Минимальная процедура

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_id BIGINT, to_id BIGINT, amount NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
END;
$$;

CALL transfer_funds(1, 2, 500.00);

Волатильность

Структура блока

CREATE OR REPLACE FUNCTION process_order(order_id BIGINT)
RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE
    v_total    NUMERIC        := 0;
    v_rec      orders%ROWTYPE;      -- тип строки таблицы
    v_col_type orders.amount%TYPE;  -- тип как у колонки
    v_rec2     RECORD;              -- произвольная строка
BEGIN
    SELECT * INTO v_rec FROM orders WHERE id = order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % not found', order_id;
    END IF;

    RETURN 'Processed ' || order_id::text;
END;
$$;

Условия и циклы

-- IF / ELSIF / ELSE
IF v_total > 10000 THEN
    discount := 0.15;
ELSIF v_total > 5000 THEN
    discount := 0.10;
ELSE
    discount := 0;
END IF;

-- FOR по диапазону
FOR i IN 1..10 LOOP
    -- тело
END LOOP;

-- FOR по результату запроса
FOR rec IN SELECT id, name FROM users WHERE active = TRUE LOOP
    RAISE NOTICE 'User: % (%)', rec.name, rec.id;
END LOOP;

-- EXIT / CONTINUE
CONTINUE WHEN i % 2 = 0;  -- пропустить чётные
EXIT WHEN i > 50;          -- выйти если > 50

RAISE

RAISE NOTICE    'Debug: x = %', x;
RAISE WARNING   'Внимание: %', x;
RAISE EXCEPTION 'Ошибка: % не найден', id;

-- С деталями:
RAISE EXCEPTION 'Недостаточно средств'
    USING ERRCODE = 'P0001',
          DETAIL  = 'Баланс: ' || balance::text,
          HINT    = 'Пополните счёт';

RETURNS TABLE

CREATE OR REPLACE FUNCTION get_active_users(min_age INT DEFAULT 18)
RETURNS TABLE(id BIGINT, name TEXT, email TEXT, age INT)
LANGUAGE sql STABLE AS $$
    SELECT id, name, email,
           DATE_PART('year', AGE(birthdate))::int
    FROM users
    WHERE active = TRUE
      AND DATE_PART('year', AGE(birthdate)) >= min_age;
$$;

-- Вызов как обычная таблица:
SELECT name FROM get_active_users() WHERE email LIKE '%@gmail%';

SETOF и RETURN QUERY

CREATE OR REPLACE FUNCTION paginate_orders(
    p_user_id BIGINT, p_limit INT DEFAULT 20, p_offset INT DEFAULT 0
)
RETURNS SETOF orders
LANGUAGE plpgsql STABLE AS $$
BEGIN
    RETURN QUERY
        SELECT * FROM orders
        WHERE user_id = p_user_id
        ORDER BY created_at DESC
        LIMIT p_limit OFFSET p_offset;
END;
$$;

OUT-параметры — несколько значений

CREATE OR REPLACE FUNCTION get_order_stats(
    user_id          BIGINT,
    OUT total_orders  INT,
    OUT total_amount  NUMERIC,
    OUT last_order_at TIMESTAMPTZ
)
LANGUAGE sql STABLE AS $$
    SELECT COUNT(*)::int, SUM(amount), MAX(created_at)
    FROM orders WHERE orders.user_id = get_order_stats.user_id;
$$;

SELECT * FROM get_order_stats(42);
-- total_orders | total_amount | last_order_at

Структура триггера

-- Шаг 1: триггерная функция
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    -- NEW — новая строка (INSERT/UPDATE)
    -- OLD — старая строка (UPDATE/DELETE)
    NEW.updated_at := NOW();
    RETURN NEW;  -- обязательно для BEFORE триггера
END;
$$;

-- Шаг 2: вешаем на таблицу
CREATE TRIGGER trg_set_updated_at
    BEFORE UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION set_updated_at();

BEFORE vs AFTER vs INSTEAD OF

Аудит-триггер

CREATE OR REPLACE FUNCTION audit_trigger_fn()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit_log(table_name, operation, old_data, new_data)
    VALUES (
        TG_TABLE_NAME,   -- имя таблицы
        TG_OP,           -- 'INSERT', 'UPDATE', 'DELETE'
        CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END
    );
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_fn();

WHEN-условие

-- Реагировать только при изменении status:
CREATE TRIGGER trg_status_change
    AFTER UPDATE ON orders
    FOR EACH ROW
    WHEN (OLD.status IS DISTINCT FROM NEW.status)
    EXECUTE FUNCTION on_status_changed();

EXCEPTION блок

BEGIN
    RETURN a / b;

EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Деление на ноль';
        RETURN NULL;

    WHEN unique_violation THEN
        RAISE EXCEPTION 'Запись уже существует';

    WHEN OTHERS THEN
        RAISE WARNING 'Ошибка: % %', SQLSTATE, SQLERRM;
        RETURN NULL;
END;

Коды ошибок PostgreSQL

GET STACKED DIAGNOSTICS

WHEN OTHERS THEN
    DECLARE
        v_state   TEXT;
        v_msg     TEXT;
        v_detail  TEXT;
        v_context TEXT;
    BEGIN
        GET STACKED DIAGNOSTICS
            v_state   = RETURNED_SQLSTATE,
            v_msg     = MESSAGE_TEXT,
            v_detail  = PG_EXCEPTION_DETAIL,
            v_context = PG_EXCEPTION_CONTEXT;

        INSERT INTO error_log(state, msg, detail, context)
        VALUES (v_state, v_msg, v_detail, v_context);

        RAISE;  -- перебросить ошибку дальше
    END;

Динамический SQL

-- format() с %I (идентификатор) и %L (литерал)
EXECUTE format(
    'SELECT COUNT(*) FROM %I.%I WHERE status = %L',
    schema_name, table_name, 'active'
) INTO result;

SECURITY DEFINER

-- Выполняется с правами владельца функции, а не вызывающего.
CREATE OR REPLACE FUNCTION get_salary(emp_id INT)
RETURNS NUMERIC LANGUAGE sql
SECURITY DEFINER
SET search_path = public   -- обязательно фиксировать!
AS $$
    SELECT salary FROM salaries WHERE id = emp_id;
$$;

GRANT EXECUTE ON FUNCTION get_salary(INT) TO readonly_role;
REVOKE ALL ON TABLE salaries FROM readonly_role;

pg_notify — асинхронные уведомления

CREATE OR REPLACE FUNCTION notify_order_created()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    PERFORM pg_notify(
        'order_events',
        json_build_object('id', NEW.id, 'status', NEW.status)::text
    );
    RETURN NEW;
END;
$$;

-- Python-клиент:
-- conn.set_isolation_level(0)
-- cur.execute("LISTEN order_events")
-- select.select([conn], [], [])
-- conn.poll()
-- for notify in conn.notifies: print(notify.payload)

Типы JOIN — визуально

Все JOIN работают по принципу: берём строки из левой таблицы, ищем совпадение в правой по условию ON, и в зависимости от типа JOIN решаем, что делать если совпадения нет.

INNER JOIN

Возвращает только строки, где условие ON выполнено в обеих таблицах. Самый распространённый тип.

SELECT
    o.id        AS order_id,
    u.name      AS user_name,
    o.amount
FROM orders o
INNER JOIN users u ON u.id = o.user_id;
-- Заказы без пользователя и пользователи без заказов — не попадут

-- Краткая запись (JOIN без слова INNER — то же самое):
FROM orders o JOIN users u ON u.id = o.user_id

LEFT JOIN

Все строки из левой таблицы. Если в правой нет пары — поля правой будут NULL. Удобен для поиска "сирот".

SELECT
    u.id,
    u.name,
    o.id   AS order_id,
    o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- Пользователи без заказов будут, но order_id и amount = NULL

-- Найти пользователей, у которых НЕТ ни одного заказа:
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

FULL JOIN

Объединяет результаты LEFT и RIGHT JOIN. Строки без пары с обеих сторон попадают в результат с NULL.

SELECT
    u.name      AS user_name,
    o.id        AS order_id
FROM users u
FULL JOIN orders o ON o.user_id = u.id;
-- Пользователи без заказов: order_id = NULL
-- Заказы без пользователя: user_name = NULL

-- Найти строки без пары с ОБЕИХ сторон:
WHERE u.id IS NULL OR o.id IS NULL

CROSS JOIN

Каждая строка левой таблицы соединяется с каждой строкой правой. Результат: N × M строк. Применяется для генерации комбинаций.

-- Все возможные комбинации размеров и цветов:
SELECT s.name AS size, c.name AS color
FROM sizes s
CROSS JOIN colors c;

-- Генерация сетки дат × категорий для отчёта:
SELECT d.day, cat.name
FROM generate_series(
    '2024-01-01'::date,
    '2024-01-31'::date,
    '1 day'
) d(day)
CROSS JOIN categories cat;

SELF JOIN

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

-- Каждый сотрудник + имя его менеджера:
SELECT
    e.name      AS employee,
    m.name      AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- LEFT JOIN чтобы CEO (без менеджера) тоже попал в результат

-- Найти пары сотрудников из одного отдела:
SELECT a.name, b.name, a.department
FROM employees a
JOIN employees b
  ON  a.department = b.department
  AND a.id < b.id;  -- чтобы не дублировать пары (A,B) и (B,A)

JOIN по нескольким условиям и USING

-- Несколько условий в ON:
FROM orders o
JOIN promotions p
  ON  p.user_id = o.user_id
  AND p.product_id = o.product_id
  AND p.valid_until >= o.created_at

-- USING — сокращение когда колонки называются одинаково:
FROM orders o
JOIN users u USING(user_id)
-- эквивалентно: ON u.user_id = o.user_id
-- колонка user_id в результате будет только одна (без дублирования)

JOIN с подзапросом и CTE

-- JOIN с подзапросом — фильтруем до соединения (эффективнее):
FROM orders o
JOIN (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 10000
) big_spenders ON big_spenders.user_id = o.user_id

-- То же через CTE — читаемее:
WITH big_spenders AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
    HAVING SUM(amount) > 10000
)
SELECT u.name, bs.total
FROM users u
JOIN big_spenders bs ON bs.user_id = u.id;

Частые ошибки с JOIN

-- ✗ Умножение строк при JOIN на таблицу с дублями:
FROM orders o
JOIN order_tags t ON t.order_id = o.id
-- если у заказа 3 тега — заказ появится 3 раза!
-- Решение: агрегировать теги до JOIN или использовать LATERAL

-- ✓ Агрегируем сначала:
FROM orders o
JOIN (
    SELECT order_id, array_agg(tag) AS tags
    FROM order_tags GROUP BY order_id
) t ON t.order_id = o.id

-- ✗ NULL в условии JOIN — строка не совпадёт никогда:
ON a.manager_id = b.id
-- если manager_id = NULL — строка не попадёт в INNER JOIN
-- используйте LEFT JOIN или IS NOT DISTINCT FROM

-- ✓ Сравнение с учётом NULL:
ON a.manager_id IS NOT DISTINCT FROM b.id

Производительность JOIN

-- Индекс на колонке JOIN — самое важное для производительности:
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Проверить какую стратегию выбрал планировщик:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders o JOIN users u ON u.id = o.user_id;