PostgreSQL 18: почему UNIQUE пропускает дубли с NULL

Импорт запустили повторно — и число операций выросло почти вдвое. На таблице при этом действительно был составной UNIQUE, а в журнале загрузчика не оказалось ни одной ошибки уникальности. Причина скрывалась в одном необязательном поле: PostgreSQL по умолчанию считает значения NULL различными при проверке уникального ограничения. Я разберу эту механику на воспроизводимом примере, покажу расследование на рабочей базе, очистку накопленных дублей и замену ограничения через конкурентно построенный индекс. Заодно объясню, когда NULLS NOT DISTINCT решает задачу, а когда лучше пересмотреть сам ключ импорта.

Ключ есть, но повторная строка всё равно проходит

Такой инцидент обычно описывают одинаково: «База должна была остановить дубль, но почему-то приняла его». Я начинаю не с кода загрузчика, а с определения ограничения и фактических данных. Наличие слова UNIQUE ещё не доказывает, что все ожидаемые повторы будут отклонены: важно, какие колонки входят в ключ, допускают ли они NULL и с каким правилом создан поддерживающий индекс.

В обычном SQL-сравнении выражение с NULL возвращает неизвестный результат, а не true или false. У уникальных ограничений PostgreSQL есть отдельное, явно документированное правило: по умолчанию два NULL считаются различными. Поэтому две строки могут совпадать по заполненным колонкам и обе содержать NULL в одном и том же месте составного ключа — обычный UNIQUE их пропустит. Если различаются заполненные значения, это не дубль и при NULLS NOT DISTINCT; отклоняется только повтор всей ключевой комбинации.

Ниже минимальный пример. Я сразу дал старому ограничению имя ops_natural_key_old: оно понадобится в миграции. Оба INSERT допустимы, потому что external_id в повторяющихся строках равен NULL, а ограничение использует поведение NULLS DISTINCT по умолчанию.

CREATE TABLE ops (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    op_date     date          NOT NULL,
    partner_id  bigint        NOT NULL,
    amount      numeric(18,2) NOT NULL,
    external_id text,
    CONSTRAINT ops_natural_key_old
        UNIQUE (op_date, partner_id, amount, external_id)
);

INSERT INTO ops (op_date, partner_id, amount, external_id)
VALUES ('2026-09-01', 42, 1200.00, NULL);

INSERT INTO ops (op_date, partner_id, amount, external_id)
VALUES ('2026-09-01', 42, 1200.00, NULL);

Путаницу усиливает GROUP BY: при группировке строки с одинаковыми заполненными значениями и NULL в одинаковых позициях попадают в одну группу. Поэтому диагностический запрос уверенно показывает повтор, хотя уникальное ограничение его разрешило. Это не противоречие и не повреждение индекса, а две разные операции с разными правилами обработки NULL. Практический вывод простой: если повторный импорт должен быть идемпотентным, одной проверки наличия UNIQUE недостаточно — нужно проверить и nullable-колонки, и свойство indnullsnotdistinct поддерживающего индекса.

• внешний идентификатор документа, который источник присваивает не всем операциям;
• номер ручной корректировки или внутреннего перемещения;
• необязательное подразделение, склад, договор либо статья затрат;
• номер партии, серия или номер декларации, применимый только к части номенклатуры;
• дата закрытия записи, которая остаётся пустой, пока запись действует.

Разбор: фитнес-сеть «Форма», 3 клуба, 48 РМ

Условный клиент из разбора — фитнес-сеть «Форма», 3 клуба, 48 РМ. Учётный контур работал на PostgreSQL 18 под Debian 12: 8 vCPU, 32 ГБ оперативной памяти, около 180 ГБ данных. Ночной загрузчик на Python забирал выписки и операции из внешнего портала и записывал их батчами по 5000 строк. Естественным ключом считалась комбинация даты, контрагента, суммы и внешнего номера документа. Разработчик ожидал, что повторный запуск будет безопасным именно благодаря составному UNIQUE.

В августе портал повторно отдал данные за сутки, а загрузчик после сетевого таймаута стартовал с начала дня. Через три недели бухгалтерия заметила расхождение оборотов по крупному контрагенту. К этому моменту в таблице было 2,4 млн строк, включая 96 тысяч лишних повторов. Все найденные повторы содержали external_id IS NULL: это были ручные корректировки и внутренние перемещения без номера портала. Записи с заполненным external_id не задвоились, потому что для них все четыре значения сравнивались обычным образом.

Первый запрос я использовал для подсчёта повторов по предполагаемому естественному ключу. Второй связал pg_constraint с pg_index и показал не только наличие уникального ограничения, но и реальную трактовку NULL. Условия по conrelid и contype = 'u' отсекают ограничения других таблиц и типов.

SELECT op_date,
       partner_id,
       amount,
       external_id,
       count(*) AS row_count
FROM ops
GROUP BY op_date, partner_id, amount, external_id
HAVING count(*) > 1
ORDER BY row_count DESC
LIMIT 20;

SELECT c.conname,
       i.indexrelid::regclass AS index_name,
       i.indisvalid,
       i.indisunique,
       i.indnullsnotdistinct
FROM pg_constraint AS c
JOIN pg_index AS i
  ON i.indexrelid = c.conindid
WHERE c.conrelid = 'ops'::regclass
  AND c.contype = 'u';

Для ops_natural_key_old поле indnullsnotdistinct вернуло false. В PostgreSQL это означает, что уникальный индекс считает NULL различными; значение true означало бы семантику NULLS NOT DISTINCT. Заодно мы проверили indisvalid: невалидный индекс нельзя принимать за действующую защиту, даже если его имя осталось в каталоге после неудачного конкурентного построения.

После диагноза порядок работ стал понятен: прекратить появление новых повторов на время исправления, сохранить аудит-срез, составить соответствие удаляемых и сохраняемых идентификаторов, перенести внешние ссылки, удалить лишние строки, построить новый индекс и только затем перевести загрузчик на ON CONFLICT. Если начать с нового уникального индекса, он завершится ошибкой на уже существующих дублях. Если чистить таблицу при продолжающемся старом импорте, повторы могут вернуться между очисткой и построением индекса.

• сравнить колонки диагностического `GROUP BY` с колонками ограничения;
• проверить nullable-состояние каждой колонки ключа;
• прочитать `indnullsnotdistinct` и `indisvalid` из `pg_index`;
• выяснить, продолжает ли загрузчик писать строки во время ремонта;
• найти все внешние ключи и прикладные ссылки до удаления дублей.

Штатное решение: UNIQUE NULLS NOT DISTINCT

Поддержка NULLS NOT DISTINCT появилась в PostgreSQL 15 и присутствует в PostgreSQL 18. Это штатная возможность уникальных ограничений и уникальных B-tree-индексов, а не расширение и не особенность отдельной сборки. В определении табличного ограничения фраза ставится между UNIQUE и списком колонок.

Для новой таблицы нужная семантика задаётся сразу. На существующей чистой таблице ограничение можно добавить через ALTER TABLE, но эта форма сама строит индекс и обычно требует ACCESS EXCLUSIVE. Для крупной рабочей таблицы дальше я покажу двухэтапный вариант с CREATE UNIQUE INDEX CONCURRENTLY.

CREATE TABLE ops_new (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    op_date     date          NOT NULL,
    partner_id  bigint        NOT NULL,
    amount      numeric(18,2) NOT NULL,
    external_id text,
    CONSTRAINT ops_new_natural_key
        UNIQUE NULLS NOT DISTINCT
        (op_date, partner_id, amount, external_id)
);

ALTER TABLE ops
    ADD CONSTRAINT ops_natural_key
    UNIQUE NULLS NOT DISTINCT
    (op_date, partner_id, amount, external_id);

NULLS NOT DISTINCT не превращает external_id в обязательное поле. В таблице по-прежнему могут лежать строки с NULL, причём таких строк может быть много. Нельзя только дважды записать одну и ту же комбинацию. Например, операции (2026-09-01, 42, 1200.00, NULL) и (2026-09-01, 42, 1300.00, NULL) совместимы: суммы различаются. Повтор первой комбинации будет отклонён.

Обратную форму NULLS DISTINCT можно написать явно. Она совпадает с поведением PostgreSQL по умолчанию и полезна как документация принятого решения, особенно в миграциях между СУБД: стандарт SQL не требует от всех реализаций одинаковой трактовки NULL в уникальных ограничениях. При этом одного комментария в миграции недостаточно — фактическое состояние после выкладки всё равно лучше проверить через системный каталог.

Уникальное ограничение автоматически создаёт поддерживающий уникальный B-tree-индекс. Дополнительный обычный индекс по тем же колонкам чаще всего лишь занимает место и увеличивает стоимость INSERT, UPDATE, очистки и резервного копирования. Перед удалением похожего индекса я всё же сравниваю порядок колонок, сортировку, операторные классы, INCLUDE, предикат и статистику использования: визуально похожий индекс может обслуживать другой запрос и не быть полным дублем.

• NULLS NOT DISTINCT доступно начиная с PostgreSQL 15;
• опция относится к UNIQUE-ограничениям и уникальным индексам;
• NULL остаётся допустимым значением, если отдельно не задано NOT NULL;
• в многоколоночном ключе отклоняется только совпадение всей комбинации;
• поддерживающий индекс для UNIQUE создаётся автоматически.

Сначала сохранить ссылки и вычистить дубли

Перед удалением я проверяю резервную копию и отдельно создаю аудит-таблицу с затронутыми строками. CREATE TABLE AS не заменяет полноценный бэкап и восстановление до точки во времени: это быстрый рабочий срез, по которому можно объяснить каждое удаление и при необходимости восстановить конкретную запись. Имя с датой удобно для расследования, но срок хранения такой таблицы должен определяться политикой хранения данных, а не примером из статьи.

Для отбора я группирую строки по тем же четырём полям, а затем соединяю найденные группы через строковое IS NOT DISTINCT FROM. Этот предикат возвращает true и для пары NULL, поэтому его семантика соответствует будущему ключу. В аудит попадут и сохраняемые строки, и предназначенные для удаления копии.

CREATE TABLE ops_dup_20260907 AS
WITH duplicated_keys AS (
    SELECT op_date, partner_id, amount, external_id
    FROM ops
    GROUP BY op_date, partner_id, amount, external_id
    HAVING count(*) > 1
)
SELECT o.*
FROM ops AS o
JOIN duplicated_keys AS d
  ON ROW(o.op_date, o.partner_id, o.amount, o.external_id)
     IS NOT DISTINCT FROM
     ROW(d.op_date, d.partner_id, d.amount, d.external_id);

Следующий шаг — зафиксировать правило выбора выжившей записи. В нашем случае сохранялся минимальный id; это допустимо только потому, что специалисты фитнес-сети «Форма», 3 клуба, 48 РМ подтвердили равнозначность повторов. Если строки отличаются статусом, временем обновления, автором или проведением в учётной системе, правило должно учитывать эти поля. Автоматическое «оставить самую старую» без согласования легко удалит актуальную операцию.

CREATE TABLE ops_dedup_map AS
SELECT id AS old_id, keep_id
FROM (
    SELECT id,
           min(id) OVER (
               PARTITION BY op_date, partner_id, amount, external_id
           ) AS keep_id
    FROM ops
) AS ranked
WHERE id <> keep_id;

CREATE UNIQUE INDEX ops_dedup_map_old_id_idx
    ON ops_dedup_map (old_id);

До DELETE я ищу все объявленные внешние ключи, ссылающиеся на ops. Этого запроса недостаточно для неформальных ссылок без ограничения, JSON-полей, журналов обмена и внешних систем, поэтому дополнительно проверяются код приложения и схема интеграций. Для каждого найденного дочернего объекта ссылки с old_id переводятся на keep_id; возможные конфликты уникальности в дочерней таблице разрешаются по её собственным правилам.

SELECT conrelid::regclass AS referencing_table,
       conname,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f'
  AND confrelid = 'ops'::regclass
ORDER BY conrelid::regclass::text, conname;

После переноса ссылок мы удаляли строки отдельными транзакциями максимум по 10 тысяч записей. Один запуск приведён ниже; его повторяют, пока ops_dedup_map не опустеет. Короткие транзакции уменьшают продолжительность удержания блокировок и объём единовременно накопленного WAL, но общий WAL от DELETE никуда не исчезает. Важно также не запускать несколько таких удалений без оценки нагрузки на реплики и диски.

BEGIN;

WITH batch AS (
    SELECT old_id
    FROM ops_dedup_map
    ORDER BY old_id
    LIMIT 10000
),
deleted AS (
    DELETE FROM ops AS o
    USING batch AS b
    WHERE o.id = b.old_id
    RETURNING o.id
)
DELETE FROM ops_dedup_map AS m
USING deleted AS d
WHERE m.old_id = d.id;

COMMIT;

В кейсе 96 тысяч лишних строк были обработаны такими порциями примерно за 40 минут, пока основное приложение продолжало работать. Это наблюдение конкретной системы с 8 vCPU, 32 ГБ памяти и базой около 180 ГБ, а не обещание производительности для любой установки. Основное время ушло на проверку и перенос прикладных ссылок, а не на сам DELETE.

Я не запускаю VACUUM после каждой порции автоматически. Обычный VACUUM нельзя выполнять внутри транзакционного блока, он создаёт дополнительную дисковую нагрузку, а частоту обслуживания нужно выбирать по числу мёртвых строк и настройкам autovacuum. После завершения очистки мы проверили результат, затем отдельно выполнили VACUUM (ANALYZE); VACUUM FULL здесь не нужен, поскольку он берёт ACCESS EXCLUSIVE и переписывает таблицу.

SELECT count(*) AS duplicate_groups
FROM (
    SELECT 1
    FROM ops
    GROUP BY op_date, partner_id, amount, external_id
    HAVING count(*) > 1
) AS duplicates;

VACUUM (ANALYZE) ops;

• проверить рабочий бэкап и создать аудит-срез до удаления;
• согласовать правило выбора выжившей строки с владельцем данных;
• построить таблицу соответствия `old_id` и `keep_id`;
• перенести объявленные и неформальные внешние ссылки;
• удалять короткими транзакциями и следить за WAL, репликами и autovacuum;
• повторить проверку дублей перед построением нового индекса.

Как заменить ключ на рабочей таблице

Прямой ALTER TABLE ... ADD CONSTRAINT ... UNIQUE обычно получает ACCESS EXCLUSIVE и строит индекс в рамках этой операции. На небольшой таблице это может быть приемлемо, но универсального безопасного размера нет: длительность зависит от объёма, дисков, кэша и текущей нагрузки. Для большой непартиционированной таблицы PostgreSQL документирует двухэтапную схему: конкурентно построить уникальный индекс, затем присоединить его как ограничение.

Между очисткой и завершением нового индекса старый ключ всё ещё пропускает повторы с NULL. Поэтому мы временно остановили только проблемный импорт, оставив остальное приложение доступным. Другой рабочий вариант — заранее перевести загрузчик в однопоточный режим с надёжной сериализацией. Простая проверка SELECT перед INSERT не закрывает окно гонки и не заменяет временный барьер.

Синтаксис CREATE INDEX отличается от синтаксиса ограничения: NULLS NOT DISTINCT ставится после списка ключевых колонок и необязательного INCLUDE, но перед WITH, TABLESPACE и WHERE. Внутри списка колонок существуют отдельные формы NULLS FIRST и NULLS LAST — они управляют сортировкой и не меняют уникальность.

CREATE UNIQUE INDEX CONCURRENTLY ops_natural_key_idx
    ON ops (op_date, partner_id, amount, external_id)
    NULLS NOT DISTINCT;

CREATE INDEX CONCURRENTLY нельзя выполнять внутри транзакционного блока. Команда делает два сканирования таблицы, ждёт завершения конфликтующих транзакций и обычно работает дольше обычного построения, зато не блокирует INSERT, UPDATE и DELETE на всё время. Перед запуском я проверяю свободное место и длинные транзакции, а во время работы смотрю прогресс в pg_stat_progress_create_index.

Если конкурентное построение завершается ошибкой, например из-за найденного повтора или взаимной блокировки, в каталоге может остаться невалидный индекс. Он занимает место и создаёт накладные расходы на изменения таблицы; невалидный уникальный индекс после определённых фаз неудачного построения может также продолжить проверять уникальность. Поэтому состояние нужно проверить явно, а затем либо удалить индекс и повторить построение, либо оценить документированный вариант REINDEX INDEX CONCURRENTLY.

SELECT indexrelid::regclass AS index_name,
       indisready,
       indisvalid,
       indisunique,
       indnullsnotdistinct
FROM pg_index
WHERE indrelid = 'ops'::regclass
ORDER BY indexrelid::regclass::text;

DROP INDEX CONCURRENTLY IF EXISTS ops_natural_key_idx;

Команду DROP INDEX CONCURRENTLY из примера выполняют только после подтверждённой неудачи, вне транзакционного блока и до присоединения индекса к ограничению. Она не подходит для индекса, уже принадлежащего UNIQUE или PRIMARY KEY. Если новый индекс валиден, удалять его, конечно, не нужно.

Финальная замена — короткая операция с метаданными. В одной транзакции я ограничиваю ожидание блокировки тремя секундами, удаляю прежнее ограничение и присоединяю новый индекс. PostgreSQL переименует индекс в имя ограничения и передаст ограничению владение индексом. Если lock_timeout сработает, транзакцию нужно откатить и повторить целиком в более спокойный момент.

BEGIN;
SET LOCAL lock_timeout = '3s';

ALTER TABLE ops
    DROP CONSTRAINT ops_natural_key_old,
    ADD CONSTRAINT ops_natural_key
        UNIQUE USING INDEX ops_natural_key_idx;

COMMIT;

ALTER TABLE на финальном шаге всё равно запрашивает ACCESS EXCLUSIVE, но не сканирует заново всю таблицу: для UNIQUE на готовом подходящем индексе документация называет остальные случаи быстрыми. Это не означает, что блокировка гарантированно будет получена мгновенно. Короткая команда может долго стоять за активной транзакцией, поэтому lock_timeout и наблюдение за блокировками обязательны.

Описанная схема не переносится один в один на секционированную таблицу. PostgreSQL 18 не поддерживает CREATE INDEX CONCURRENTLY непосредственно на секционированной родительской таблице и не поддерживает форму ADD ... USING INDEX для неё. Индексы можно строить конкурентно на отдельных секциях и затем присоединять к родительскому индексу, но уникальный ключ родителя должен включать все колонки ключа секционирования. Такой объект требует отдельного плана миграции.

• остановить источник новых дублей или надёжно сериализовать импорт;
• построить `CREATE UNIQUE INDEX CONCURRENTLY` вне транзакции;
• проверить `indisready`, `indisvalid` и `indnullsnotdistinct`;
• короткой транзакцией заменить старое ограничение через `USING INDEX`;
• после миграции повторно прогнать диагностический запрос и импорт;
• для секционированной таблицы подготовить отдельную процедуру.

Обходные решения для PostgreSQL 14 и старше

В PostgreSQL 14 и более ранних версиях NULLS NOT DISTINCT нет. Один из распространённых обходов — уникальный индекс по выражению с COALESCE. Он действительно закрывает повтор с NULL, но требует суррогатного значения, которое гарантированно не встретится в реальных данных. Пустая строка для этого плохо подходит, если источник вправе прислать настоящий пустой идентификатор.

В примере я использую строку с явным префиксом и дополнительно запрещаю её как реальное значение. Это делает выбранное соглашение проверяемым, но всё равно связывает модель с искусственным маркером. Ограничение CHECK также допускает NULL, потому что результат проверки для NULL не равен false; именно это здесь и требуется.

ALTER TABLE ops
    ADD CONSTRAINT ops_external_id_reserved_check
    CHECK (external_id <> '@@NULL@@');

CREATE UNIQUE INDEX ops_natural_key_expr_idx
    ON ops (
        op_date,
        partner_id,
        amount,
        COALESCE(external_id, '@@NULL@@')
    );

INSERT INTO ops (op_date, partner_id, amount, external_id)
VALUES ('2026-09-01', 42, 1200.00, NULL)
ON CONFLICT (
    op_date,
    partner_id,
    amount,
    COALESCE(external_id, '@@NULL@@')
)
DO NOTHING;

При выводе арбитражного индекса для ON CONFLICT PostgreSQL сопоставляет указанные колонки и выражения с уникальными индексами таблицы. Поэтому для индекса по выражению COALESCE должно присутствовать и в conflict_target; простой список четырёх колонок такой индекс не выведет. Выражение не обязано быть текстуально побайтно идентичным, если PostgreSQL распознаёт его как то же выражение, но полагаться на случайные переписывания и неявные приведения типов я не советую.

Второй вариант для одного nullable-поля — два частичных уникальных индекса. Первый покрывает строки с заполненным external_id, второй — строки с NULL и потому индексирует только три оставшиеся колонки. При нескольких независимо nullable-полях число комбинаций предикатов может вырасти до двух в степени количества таких полей. Это верхняя граница для полного покрытия всех сочетаний, а не обязательное число индексов для любой бизнес-модели.

CREATE UNIQUE INDEX ops_natural_key_present_idx
    ON ops (op_date, partner_id, amount, external_id)
    WHERE external_id IS NOT NULL;

CREATE UNIQUE INDEX ops_natural_key_missing_idx
    ON ops (op_date, partner_id, amount)
    WHERE external_id IS NULL;

INSERT INTO ops (op_date, partner_id, amount, external_id)
VALUES ('2026-09-01', 42, 1200.00, NULL)
ON CONFLICT (op_date, partner_id, amount)
WHERE external_id IS NULL
DO NOTHING;

Частичные индексы иногда точнее отражают бизнес-правило. Например, заполненный внешний номер может быть уникален глобально, а операция без номера — только в пределах даты, партнёра и суммы. Но тогда это уже не технический обход, а два явно разных правила идентичности. Их стоит так и назвать в схеме и покрыть отдельными тестами ON CONFLICT.

Худший вариант — триггер или код приложения, который сначала выполняет SELECT, а затем решает, делать ли INSERT. При стандартной конкуренции две сессии могут одновременно не увидеть строку и обе выполнить вставку. Уровень SERIALIZABLE способен завершить одну из транзакций ошибкой сериализации, но приложение обязано корректно повторять такие транзакции. Advisory-блокировка тоже требует единого, безошибочного способа вычислять ключ блокировки. Оба решения сложнее уникального индекса.

После обновления до PostgreSQL 15 или новее я предпочитаю заменить обходы штатным NULLS NOT DISTINCT. Перед удалением старых индексов нужно убедиться, что новый ключ полностью воспроизводит требуемую семантику, а все запросы и формы ON CONFLICT уже переведены. Одновременное наличие нескольких похожих уникальных индексов может увеличить стоимость записи и сделать выбор арбитров менее очевидным.

• индекс с `COALESCE` требует защищённого от коллизии маркера;
• арбитражное выражение нужно указать в `ON CONFLICT`;
• два частичных индекса удобны при одном nullable-поле;
• при нескольких nullable-полях схема частичных индексов быстро усложняется;
• проверка перед вставкой без уникального индекса остаётся гонкой.

Когда лучше изменить сам ключ импорта

Nullable-поле в уникальном ключе не является автоматической ошибкой модели. Иногда смысл совершенно точный: внешний номер может отсутствовать, но две операции с одинаковыми остальными реквизитами и двумя отсутствующими номерами всё равно считаются одной операцией. Именно для такого правила и существует NULLS NOT DISTINCT. Проблема начинается, когда команда не может однозначно объяснить, что означает NULL и почему остальные поля вместе идентифицируют запись.

Я задаю владельцу данных три вопроса. Одинаковы ли две операции без внешнего номера, если совпали дата, партнёр и сумма? Может ли одна операция законно повторяться с теми же реквизитами? Что произойдёт, если номер сначала отсутствовал, а потом появился? Ответы определяют, нужен ли DO NOTHING, обновление существующей строки, отдельная таблица сопоставления или устойчивый идентификатор источника.

Лучший ключ импорта — стабильный идентификатор, который выдаёт источник и не переиспользует. Если такого идентификатора нет, загрузчик может формировать каноническую строку из типизированных полей по зафиксированным правилам. Я предпочитаю хранить полную каноническую строку, а не только MD5: любой хэш имеет вероятность коллизии, а полное значение удобнее расследовать. Важно определить часовой пояс, формат даты, регистр, пробелы, округление суммы и различие между NULL и пустой строкой.

Ниже SQL-вариант для случая, когда бизнес подтвердил, что идентичность операции точно совпадает с текущими четырьмя типизированными полями. jsonb_build_array сохраняет границы элементов и отдельное значение JSON null, поэтому разделители внутри external_id не создают неоднозначность. Пакетная команда обрабатывает до 10 тысяч строк; после каждого запуска транзакцию следует завершить, а загрузчик к этому моменту уже должен писать import_key для новых данных по тому же контракту.

ALTER TABLE ops ADD COLUMN import_key text;

WITH batch AS (
    SELECT id
    FROM ops
    WHERE import_key IS NULL
    ORDER BY id
    LIMIT 10000
    FOR UPDATE SKIP LOCKED
)
UPDATE ops AS o
SET import_key = jsonb_build_array(
        o.op_date,
        o.partner_id,
        o.amount,
        o.external_id
    )::text
FROM batch AS b
WHERE o.id = b.id;

Когда пустых значений не осталось, PostgreSQL 18 позволяет добавить именованное NOT NULL как табличное ограничение с атрибутом NOT VALID, а затем проверить старые строки отдельной командой. Точный порядок слов — ADD CONSTRAINT, имя, NOT NULL, имя колонки, NOT VALID. Новые и изменяемые строки после установки уже проверяются, а VALIDATE CONSTRAINT сканирует существующие данные под более мягкой блокировкой SHARE UPDATE EXCLUSIVE.

ALTER TABLE ops
    ADD CONSTRAINT ops_import_key_nn
    NOT NULL import_key
    NOT VALID;

ALTER TABLE ops
    VALIDATE CONSTRAINT ops_import_key_nn;

CREATE UNIQUE INDEX CONCURRENTLY ops_import_key_idx
    ON ops (import_key);

ALTER TABLE ops
    ADD CONSTRAINT ops_import_key
    UNIQUE USING INDEX ops_import_key_idx;

В PostgreSQL 18 NOT NULL хранится в pg_constraint, имеет тип contype = 'n', может получать имя и поддерживает отдельную валидацию. Это реальная новинка версии 18. Она не делает первоначальный ADD CONSTRAINT полностью неблокирующим: команда всё равно должна кратко получить требуемую блокировку, но отсутствие немедленного полного сканирования заметно сокращает критический этап.

Идемпотентная вставка после перехода на отдельный ключ выглядит просто. Здесь указаны реальные значения примера, а import_key соответствует канонической JSON-строке из четырёх полей. При DO UPDATE нельзя бездумно менять колонку, участвующую в идентичности: если сумма входит в ключ, изменение суммы фактически означает другую операцию. Поэтому пример обновляет только время получения и внешний номер, а бизнес-правило для каждой изменяемой колонки нужно утвердить отдельно.

INSERT INTO ops (
    op_date,
    partner_id,
    amount,
    external_id,
    import_key,
    received_at
)
VALUES (
    '2026-09-01',
    42,
    1200.00,
    NULL,
    '["2026-09-01", 42, 1200.00, null]',
    CURRENT_TIMESTAMP
)
ON CONFLICT (import_key) DO UPDATE
SET external_id = COALESCE(EXCLUDED.external_id, ops.external_id),
    received_at = EXCLUDED.received_at;

Соседние возможности PostgreSQL 18 не следует смешивать с идемпотентностью импорта. Встроенная uuidv7() создаёт UUID версии 7 с временной упорядоченностью и может улучшить локальность вставок по сравнению со случайными UUID версии 4, но новый UUID при каждом запуске не распознаёт повтор входной операции. Это хороший суррогатный первичный ключ, а не замена устойчивому ключу источника.

Темпоральные ограничения PostgreSQL 18 решают другой класс задач. UNIQUE и PRIMARY KEY с WITHOUT OVERLAPS требуют, чтобы последняя колонка была диапазоном или мультидиапазоном; для внешнего ключа применяется PERIOD. Обычную nullable-дату date_to нельзя просто дописать после WITHOUT OVERLAPS. Сначала модель периода нужно представить диапазоном, определить границы и запретить пустые диапазоны. Если NULL использовался как условное «действует бессрочно», темпоральная модель заслуживает отдельного проектирования, но не механической замены одной строки DDL.

• зафиксировать, что именно означает NULL в каждом поле естественного ключа;
• проверить, может ли одна законная операция повторить дату, партнёра и сумму;
• получать стабильный идентификатор источника, если источник способен его предоставить;
• канонизировать даты, суммы, регистр, пробелы, NULL и пустые строки по единому контракту;
• не использовать случайный UUID как средство обнаружения повторного импорта;
• прогнать один и тот же входной набор дважды и сравнить состояние базы;
• проверить параллельный запуск хотя бы двух воркеров, а не только последовательный тест.

Частые вопросы