Почему ON CONFLICT DO NOTHING может скрывать ошибки в данных

В PostgreSQL ON CONFLICT DO NOTHING позволяет пропускать вставку строк при нарушении уникальности. Однако без указания конкретной цели конфликта PostgreSQL учитывает все подходящие уникальные ограничения и индексы. Из-за этого операция может быть пропущена по причине, которую бизнес-логика приложения не предусматривала.

Допустим, есть таблица пользователей:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL UNIQUE
);

Добавим существующего пользователя:

INSERT INTO users (email, username)
VALUES ('alex@example.com', 'alex');

Теперь приложение пытается зарегистрировать другого пользователя:

INSERT INTO users (email, username)
VALUES ('maria@example.com', 'alex')
ON CONFLICT DO NOTHING;

Запрос завершится без ошибки, но новая запись не появится. Причина в том, что ON CONFLICT DO NOTHING без указания цели конфликта обрабатывает нарушения уникальности по всем подходящим уникальным ограничениям и индексам.

В данном случае email уникален, но username = 'alex' уже существует. Если приложение проверяет только успешность выполнения SQL-запроса, оно может ошибочно считать регистрацию завершённой.

Чтобы обрабатывать только ожидаемый конфликт, следует явно указать его цель:

INSERT INTO users (email, username)
VALUES ('maria@example.com', 'alex')
ON CONFLICT (email) DO NOTHING;

Теперь PostgreSQL пропустит вставку только при конфликте по email. Нарушение уникальности username приведёт к ошибке 23505 (unique_violation).

Дополнительно можно использовать RETURNING:

INSERT INTO users (email, username)
VALUES ('alex@example.com', 'alex2')
ON CONFLICT (email) DO NOTHING
RETURNING id;

Если запись вставлена, запрос вернёт её id. Если вставка пропущена из-за конфликта по email, результат будет пустым. Это позволяет приложению отличать успешную вставку от пропущенной операции без дополнительного SELECT. Важно учитывать, что RETURNING при DO NOTHING не возвращает существующую строку и не сообщает причину конфликта.

🔥 При использовании ON CONFLICT DO NOTHING важно явно указывать цель конфликта, если бизнес-логика допускает дубликаты только по определённому ключу. В сочетании с RETURNING это позволяет контролировать результат вставки, не скрывая нарушения уникальности по другим ключам.

➡️ SQL Ready | #практика

Обновляйте агрегаты одним запросом!

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

PostgreSQL позволяет объединить INSERT ... SELECT с ON CONFLICT:

INSERT INTO user_stats (user_id, total)
SELECT user_id, sum(amount)
FROM payments
GROUP BY user_id
ON CONFLICT (user_id)
DO UPDATE
SET total = EXCLUDED.total;

Здесь GROUP BY вычисляет суммы, а ON CONFLICT обновляет существующие записи или добавляет новые.

Для этого на user_stats(user_id) должно быть ограничение уникальности. Можно сразу пересчитывать несколько показателей:

INSERT INTO user_stats (user_id, total, orders_count)
SELECT user_id, sum(amount), count(*)
FROM payments
GROUP BY user_id
ON CONFLICT (user_id)
DO UPDATE SET
total = EXCLUDED.total,
orders_count = EXCLUDED.orders_count;

🔥 INSERT ... SELECT ... ON CONFLICT позволяет пересчитывать и сохранять агрегаты одним запросом, без циклов и промежуточной обработки в приложении.

➡️ SQL Ready | #совет

😍 Продвинутая оптимизация PostgreSQL — WAL, VACUUM и проектирование БД!

Статья о том, как повысить производительность PostgreSQL за счёт правильной настройки внутренних механизмов СУБД и проектирования структуры базы данных. Материал посвящён не только оптимизации SQL-запросов, но и причинам возникновения проблем с производительностью. Рассматриваются WAL (журнал предзаписи), VACUUM и AUTOVACUUM, очистка устаревших версий строк, управление транзакциями и проектирование таблиц.

📌 Оставляю ссылочку: dbserv.ru

➡️ SQL Ready | #сайт

🧐 Годная статья лежит на Хабре: «Как мы разрешили чтение с реплик PostgreSQL — и почему шесть лет говорили „нет“»!

В этой статье:
• Узнаете, почему чтение с реплик PostgreSQL может угрожать отказоустойчивости и приводить к проблемам с актуальностью данных;
• Разберёте, как организовать маршрутизацию запросов через Patroni, Consul и PgBouncer с учётом отставания репликации;
• Посмотрите, как перенос части запросов на реплики помог снизить пиковую нагрузку на primary и какие риски при этом остались.

🔊 Продолжай читать на Habr!

➡️ SQL Ready | #статья

Почему NOT EXISTS не всегда эквивалентен EXCEPT!

При сравнении двух наборов данных в sql часто используют NOT EXISTS или EXCEPT. Оба подхода позволяют найти строки, присутствующие в одном наборе и отсутствующие в другом. Однако их семантика различается при наличии дубликатов и NULL.

Рассмотрим задачу: найти товары поставщика, отсутствующие в основном каталоге. Создадим таблицы в PostgreSQL:

CREATE TABLE supplier_products (
    product_id INT
);

CREATE TABLE catalog_products (
    product_id INT
);

INSERT INTO supplier_products (product_id)
VALUES (10), (10), (20), (NULL);

INSERT INTO catalog_products (product_id)
VALUES (20), (NULL);

Один из вариантов решения — коррелированный NOT EXISTS:

SELECT s.product_id
FROM supplier_products s
WHERE NOT EXISTS (
    SELECT 1
    FROM catalog_products c
    WHERE c.product_id = s.product_id
);

Результат (порядок строк не гарантируется):

product_id
----------
10
10
NULL

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

Строка с NULL также попадает в результат: сравнение NULL = NULL не возвращает TRUE, поэтому соответствие не обнаруживается. Теперь выполним EXCEPT:

SELECT product_id
FROM supplier_products

EXCEPT

SELECT product_id
FROM catalog_products;

Результат:

product_id
----------
10

Здесь проявляются два свойства стандартного EXCEPT. Во-первых, EXCEPT без ALL устраняет дубликаты, оставляя только уникальные строки. Во-вторых, при операциях над множествами два значения NULL считаются неразличимыми. Поэтому NULL, присутствующий в обоих наборах, исключается из результата.

Это отличается от обычного сравнения через =. Если необходимо сохранить кратность строк, стандарт sql предусматривает EXCEPT ALL:

SELECT product_id
FROM supplier_products

EXCEPT ALL

SELECT product_id
FROM catalog_products;

Результат:

product_id
----------
10
10

EXCEPT ALL работает с мультимножествами. Количество экземпляров каждой строки определяется формулой:

max(количество_слева - количество_справа, 0)

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

Ещё одно различие возникает при сравнении составных ключей.
Предположим, сравниваются пары (product_id, warehouse_id), причём warehouse_id может содержать NULL. Создадим таблицы:

CREATE TABLE supplier_stock (
    product_id INT,
    warehouse_id INT
);

CREATE TABLE catalog_stock (
    product_id INT,
    warehouse_id INT
);

INSERT INTO supplier_stock VALUES
(10, NULL), (10, NULL), (20, 1);

INSERT INTO catalog_stock VALUES
(10, NULL);

Обычное равенство не позволит сопоставить строки (10, NULL):

SELECT s.product_id, s.warehouse_id
FROM supplier_stock s
WHERE NOT EXISTS (
    SELECT 1
    FROM catalog_stock c
    WHERE c.product_id = s.product_id
      AND c.warehouse_id = s.warehouse_id
);

Для NULL-безопасного сравнения стандарт SQL предусматривает IS NOT DISTINCT FROM.

Чтобы одновременно устранить дубликаты и воспроизвести семантику EXCEPT без ALL, добавим DISTINCT:

SELECT DISTINCT
    s.product_id,
    s.warehouse_id
FROM supplier_stock s
WHERE NOT EXISTS (
    SELECT 1
    FROM catalog_stock c
    WHERE c.product_id IS NOT DISTINCT FROM s.product_id
      AND c.warehouse_id IS NOT DISTINCT FROM s.warehouse_id
);

Результат:

product_id | warehouse_id
-----------+-------------
20         | 1

Для этих столбцов запрос эквивалентен EXCEPT без ALL. Все примеры рассчитаны на PostgreSQL. Поддержка отдельных конструкций зависит от СУБД и версии. По производительности универсального победителя нет: результат зависит от данных, индексов и плана выполнения.

🔥 NOT EXISTS проверяет отсутствие соответствия, EXCEPT вычисляет разность множеств без дубликатов, а EXCEPT ALL учитывает кратность строк. При выборе конструкции важно учитывать NULL и повторяющиеся значения.

➡️ SQL Ready | #практика

🐱 PostgreSQL Cookbook — практический справочник по работе с PostgreSQL на русском языке!

Материалы представлены в формате практических примеров: конкретная задача, способ её решения и пример SQL-кода. Такой формат позволяет не только изучать возможности PostgreSQL, но и использовать справочник непосредственно во время разработки. Особенно полезен, когда нужно разобраться с определённой конструкцией SQL, подобрать подходящий запрос или найти решение типовой задачи.

📌 Оставляю ссылочку: dsbasko.github.io

➡️ SQL Ready | #сайт

Почему оконная функция LAST_VALUE часто возвращает не последнюю строку!

Оконные функции позволяют выполнять вычисления по набору связанных строк без сворачивания результата через GROUP BY. Однако для некоторых функций результат зависит не только от PARTITION BY и ORDER BY, но и от границ оконного фрейма.

Особенно часто это проявляется при использовании LAST_VALUE. Рассмотрим задачу: для каждой операции необходимо вывести её сумму и сумму последней операции соответствующего счёта.

Предположим, данные представлены таблицей:

transactions(
    id,
    account_id,
    amount,
    created_at
)

Попробуем решить задачу через LAST_VALUE (PostgreSQL):

SELECT
    id,
    account_id,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY account_id
        ORDER BY created_at
    ) AS last_amount
FROM transactions;

На первый взгляд LAST_VALUE должна возвращать сумму последней операции в пределах account_id. Но результат может совпадать с amount текущей строки.

Причина заключается в оконном фрейме. При наличии ORDER BY стандартный фрейм в PostgreSQL определяется как:

RANGE BETWEEN UNBOUNDED PRECEDING
          AND CURRENT ROW

При этом CURRENT ROW в режиме RANGE включает группу строк с одинаковыми значениями сортировки — peer group. Рассмотрим операции одного счёта:

id  account_id  amount  created_at
--  ----------  ------  ----------
1   42          100     2026-01-10
2   42          200     2026-01-11
3   42          300     2026-01-12

Для запроса с LAST_VALUE результат будет:

id  amount  last_amount
--  ------  -----------
1   100     100
2   200     200
3   300     300

Фрейм заканчивается на текущей строке или её peer group, поэтому последующие операции с большими значениями сортировки не входят в расчёт.

Чтобы получить действительно последнюю операцию раздела, необходимо явно расширить фрейм:

SELECT
    id,
    account_id,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY account_id
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS last_amount
FROM transactions;

Теперь фрейм охватывает все строки счёта, а LAST_VALUE возвращает сумму последней операции. Для приведённых данных last_amount будет равен 300 во всех строках.

Дополнительная сортировка по id обеспечивает однозначный порядок при совпадении created_at, если id уникален. Но есть ещё один нюанс — различие между ROWS и RANGE. Рассмотрим накопительную сумму:

SELECT
    id,
    account_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY account_id
        ORDER BY created_at
    ) AS running_total
FROM transactions;

Предположим, две операции одного счёта имеют одинаковое время:

id  amount  created_at
--  ------  ----------
1   100     10:00:00
2   200     10:00:00
3   300     11:00:00

При стандартном RANGE обе операции с временем 10:00:00 принадлежат одной peer group. Результат накопительной суммы:

id  amount  running_total
--  ------  -------------
1   100     300
2   200     300
3   300     600

Это корректное поведение: фрейм первой строки включает все операции с тем же значением created_at.

Если необходимо накапливать сумму строго по одной операции, следует использовать ROWS и уникальный порядок сортировки:

SELECT
    id,
    account_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY account_id
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total
FROM transactions;

Теперь результат будет:

id  amount  running_total
--  ------  -------------
1   100     100
2   200     300
3   300     600

Различие принципиальное: ROWS задаёт границы относительно позиций строк, а RANGE — относительно значений сортировки. При этом без уникального ORDER BY последовательность строк с одинаковыми ключами остаётся неопределённой.

Также LAST_VALUE возвращает значение последней строки фрейма, а не максимальное значение столбца. Поэтому замена на MAX(amount) может изменить смысл расчёта. Поддержка оконных фреймов различается между СУБД, а порядок вывода строк без внешнего ORDER BY не гарантируется.

🔥 ORDER BY внутри OVER определяет порядок вычисления, но не обязательно включает весь раздел во фрейм. Для LAST_VALUE и накопительных агрегатов важно явно задавать границы фрейма и детерминированный порядок сортировки.

➡️ SQL Ready | #практика

Объединяйте соседние интервалы автоматически!

В PostgreSQL есть малоизвестный агрегат range_agg(), который объединяет пересекающиеся и соприкасающиеся диапазоны. Не нужны оконные функции, рекурсивные запросы и ручной поиск границ.

Допустим, в таблице bookings хранятся периоды занятости сотрудников.

SELECT employee_id, 
range_agg(period) AS busy_periods 
FROM bookings 
GROUP BY employee_id;

Результат — multirange: набор непересекающихся интервалов. Пересекающиеся и соседние периоды PostgreSQL объединит автоматически, сохранив промежутки между ними.

Можно сразу найти свободное время внутри рабочего дня:

SELECT employee_id, 
datemultirange( 
daterange('2026-11-01', '2026-11-08') 
) - range_agg(period) AS free_periods 
FROM bookings 
GROUP BY employee_id;

Здесь вычитание возвращает все свободные промежутки, даже если их несколько. Предполагается, что period имеет тип daterange, а результат ограничивается указанной неделей.

🔥 range_agg() вместе с multirange позволяет объединять занятые интервалы и находить свободные периоды без сложных оконных запросов.

➡️ SQL Ready | #совет

📂 Шпаргалка по структуре каталогов Linux!

Например, /etc содержит системные конфигурации, /var используется для хранения логов и изменяемых данных, а /proc предоставляет информацию о процессах и состоянии ядра.

На картинке — основные каталоги с кратким описанием их назначения и роли в файловой системе.

Сохрани, чтобы не потерять!

➡️ SQL Ready | #ресурс

Используйте FETCH FIRST ... WITH TIES!

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

Например, запрос вернёт ровно три самых дорогих товара:

SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Но если у третьего и четвёртого товара одинаковая цена, четвёртый не попадёт в результат.

В PostgreSQL 13+ есть WITH TIES:

SELECT id, name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS WITH TIES;

Теперь запрос вернёт три строки и все дополнительные строки с таким же значением сортировки, как у третьей. Не нужны RANK(), CTE и дополнительная фильтрация.

Важно: WITH TIES сравнивает весь набор выражений из ORDER BY. Если добавить туда уникальный id, строки с одинаковой ценой уже не будут считаться равными.

🔥 FETCH FIRST ... WITH TIES позволяет получать первые N строк вместе со всеми совпадениями на границе выборки без оконных функций.

➡️ SQL Ready | #совет

🤔 PostgreSQL — разбор методов оптимизации!

Статья о том, как анализировать производительность PostgreSQL, находить причины медленных запросов и устранять узкие места. Материал помогает разобраться, какие факторы влияют на скорость обработки данных и как подходить к оптимизации системно. Основное внимание уделяется индексам, планам выполнения SQL-запросов, инструменту EXPLAIN ANALYZE и настройке PostgreSQL.

📌 Оставляю ссылочку: senzpo.ru

➡️ SQL Ready | #сайт