Привет, Хабр!

Постгрес – не просто реляционная БД, а настоящий кладезь фич, о которых начинающий разработчик может и не догадываться. Всё началось с того, что PostgreSQL изначально писался на С/C++ и всегда тянуло к расширению стандартного SQL набора возможностей. Так однажды разработчики решили добавить в него JSONB, логику на уровне запросов и многое другое – что в итоге сделало его не хуже NoSQL-систем. Но вернёмся к малоизвестным фичам. Ниже – пять приёмов и возможностей, которые неожиданно полезны в повседневной работе.

Как подсчитать, сколько строк вставилось, а сколько обновилось

Многие знают про INSERT ... ON CONFLICT ... DO UPDATE (апсерт). Но мало кто использует хитрость с RETURNING для отличия новых записей от обновлённых. Ключ — в системном столбце xmax. После выполнения апсерта можно вернуть результат так:

WITH new_data AS (
    SELECT * FROM (VALUES
        (1, 'Ivan', 1000),
        (2, 'Alisa',   800)
    ) AS t(id, name, salary)
)
INSERT INTO employees (id, name, salary)
SELECT id, name, salary FROM new_data
ON CONFLICT (id) DO UPDATE
    SET name = EXCLUDED.name,
        salary = EXCLUDED.salary
RETURNING id, name, salary, (xmax = 0) AS inserted;

Вставляем или обновляем сотрудников. Условие (xmax = 0) AS inserted показывает, какие строки реально вставлены. Столбец xmax указывает на транзакцию, удалившую строку, или равен 0, если строка была просто создана и не удалялась. Т.е в тех строках, где xmax=0, свежая вставка, а где xmax<>0 — обновление. Опишем это подробнее:

• xmax — особый системный столбец PostgreSQL. Если строка вставлена без предшествующего удаления, то xmax равен 0. При обновлении старый вариант строки удаляется (с новым xmin) и xmax старой версии получает непустое значение.

• В RETURNING мы возвращаем всё поле xmax = 0. Это булево значение, где TRUE означает вставку, FALSE — апдейт. В примере можно увидеть в результате, что для каждой записи метка inserted показывает, что с ней произошло (пример вывода см. ниже, строки с inserted = t вставлены, f — обновлены).

Так можно вести точный учёт того, сколько строк обновлено и сколько – добавлено, без дополнительных запросов.

Гранулярные права: GRANT SELECT только на нужные столбцы

Не все знают, что в PostgreSQL можно давать права не только на всю таблицу, но и на конкретные колонки. Допустим, есть таблица users, где хранятся и ИД пользователя, и чувствительная информация вроде паролей. Мы хотим разрешить аналитику чтение только некоторых полей. Схематично:

CREATE TABLE users (
    id            SERIAL PRIMARY KEY,
    username      TEXT,
    personal_id   TEXT,
    password_hash TEXT
);

-- Создаём пользователя-аналитика
CREATE USER analyst;

-- Сперва дали полные права, а потом отозвали
GRANT SELECT ON users TO analyst;
REVOKE SELECT ON users FROM analyst;

-- Даем только выборку из полей id и username
GRANT SELECT (id, username) ON users TO analyst;

После этого пользователь analyst может делать запросы только к указанным колонкам:

-- Так будет ошибка (пароля нет в правах):
-- SELECT id, username, password_hash FROM users;
-- А вот это сработает:
SELECT id, username FROM users;

В документации прямо указано, что SELECT может применяться ко всей таблице или только к перечисленным столбцам. Т.е Postgres хранит привилегии на уровне столбцов. Это удобно, чтобы закрыть поля типа password_hash или personal_id, пока открыты только id и username. Ценность фичи в том, что вы не вынуждены создавать отдельные представления: можно лишний раз открыть пользователю лишь часть таблицы.

DISTINCT ON: как выбирать первую или последнюю строку в группе

Когда нужно выбрать для каждой группы строк (скажем, пользователя или заказа) всего одну – например самую свежую запись – пригодится конструкция SELECT DISTINCT ON. В PostgreSQL она позволяет выбрать только первую строку по заданному ключу. Сначала перечисляем столбцы после DISTINCT ON, а потом используем ORDER BY, чтобы определить, какой из дубликатов считать первым. Пример: хранится лог действий пользователей, и мы хотим знать последнее действие каждого.

-- Таблица логов пользователя
CREATE TABLE user_logs (
    user_id INT,
    ts      TIMESTAMP,
    action  TEXT
);

-- Предположим, данные в user_logs уже есть...
-- Теперь выбираем последнее действие для каждого пользователя:
SELECT DISTINCT ON (user_id) user_id, ts, action
FROM user_logs
ORDER BY user_id, ts DESC;

Это вернёт по одной строке на каждый user_id – ту, в которой ts максимален (из-за ORDER BY ts DESC). Главное помнить: ORDER BY должен начинаться с тех же столбцов, что указаны в DISTINCT ON, и далее по убыванию (или возрастанию) фактора времени или другого ранжирующего столбца. Если этого не сделать, база вернёт какую-то первую строку для группы, но результат может быть непредсказуемым.

Всё это часто незаслуженно забывают, а зря – это в целом проще оконных функций для этой задачи. Можно быстро получить, например, последний статус по каждой сущности без лишних подзапросов. Формально DISTINCT ON – расширение PostgreSQL над стандартом SQL, но оно полностью работает и очень полезно. Любопытно, что в стандарте SQL такого синтаксиса нет, а в ПГ он существует именно для первой строки группы.

Диапазонные типы и исключающие (EXCLUDE) ограничения

Ещё одна хитрость – специальные range-типы и ограничение EXCLUDE. В PostgreSQL есть встроенные типы диапазонов: int4range, tsrange (для timestamp), daterange и так далее. Их можно применять, когда нужно хранить, скажем, период брони или налоговый промежуток. А главное – рядом с ними появилась возможность задать исключающее ограничение, которое следит, чтобы такие интервалы не пересекались.

Типичный пример: таблица бронирований (reservation) со столбцом during типа tsrange. Мы хотим гарантировать, что временные периоды не накладываются друг на друга. Делаем так:

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- расширение для нужных операторов
CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING GIST (during WITH &&)
);

&& – оператор пересечение для диапазонов. Ограничение EXCLUDE не даст вставить две записи с перекрывающимися периодами. Например, если сначала сделать

INSERT INTO reservation VALUES ('[2023-01-01 10:00, 2023-01-01 12:00)');

то попытка вставить

INSERT INTO reservation VALUES ('[2023-01-01 11:30, 2023-01-01 13:00)');

упадёт с ошибкой о нарушении ограничения (как показано в примере из документации). Так Postgres непосредственно заботится о непересечении времени брони.

Никаких функций-приложений или дополнительной логики — все гарантируется на уровне СУБД. лавное помнить: для EXCLUDE нужно GiST-индекс (или btree_gist расширение, как выше) и соответствующие операторы (для диапазонов они есть по умолчанию).

DO-блоки: анонимный код на PL/pgSQL в запросе

И последнее, что полезно знать – в PostgreSQL существует команда DO, позволяющая выполнить кусок PL/pgSQL прямо из консоли (анонимный блок кода). Это похоже на создание функции на ходу, но без объявления. Например, хотите запустить скрипт по сбору статистики или выдать права всем таблицам – не нужно создавать функцию, можно написать:

DO $$
BEGIN
    -- Пример: выводим сообщение через RAISE NOTICE
    RAISE NOTICE 'Сейчас выполняю анонимный PL/pgSQL блок';
    -- Здесь могла бы быть любая логика: цикл, динамический EXECUTE и т.д.
END
$$;

Код просто выведет уведомление. Конечно, в других сценариях можно применять DO для более полезных дел – например, динамически изменять настройки или обрабатывать ошибки. Главное – запомнить: DO исполняет переданный код один раз. По сути, это быстрое средство запустить PL/pgSQL без создания permanent объекта.

Замечу, что язык по умолчанию – plpgsql, и он уже установлен в базе. Но если нужен другой, можно указать LANGUAGE; правда, тогда потребуется установить его через CREATE EXTENSION. Ещё важно: внутри DO нельзя выполнять транзакционные команды (COMMIT/ROLLBACK) если вы уже в транзакции. Но для большинства задач (генерация логов, сложные миграции и т.п.) он отлично подходит.

В заключение: это далеко не всё, что PostgreSQL умеет. Просто среди обычных вещей легко пропустить что-то полезное. А они делают жизнь гораздо удобнее и при этом надёжнее.

Когда система под нагрузкой начинает «задыхаться» или окружение не удаётся поднять с первого раза — это больно знакомо каждому, кто работает с PostgreSQL. Решения здесь не лежат на поверхности: нужен опыт и знание скрытых механизмов базы. Приходите на бесплатные уроки, которые помогут избежать типичных ловушек и научат управлять сложными сценариями:

• 3 сентября в 20:00 — PostgreSQL. Углубленный анализ производительности

• 17 сентября в 20:00 — PostgreSQL & Docker

Хотите понять, насколько вы разбираетесь в PostgreSQL? Пройдите вступительное тестирование — это поможет оценить уровень и понять, подойдёт ли вам Продвинутый курс по работе с базой данных PostgreSQL в различных средах.