Ключевого слова UPSERT в PostgreSQL нет. Пишут INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. Если новая строка нарушила бы ограничение уникальности по этому столбцу, PostgreSQL вместо вставки обновляет существующую строку. DO NOTHING её пропускает.
A-100 уже был в таблице, поэтому его цена изменилась на 12.5. B-200 новый, и его вставили. Один оператор обрабатывает оба случая и остаётся корректным, когда другие сессии одновременно вставляют те же ключи.
Вставка или обновление одной строки
Оператор состоит из трёх частей:
INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
- Цель конфликта перечисляет столбцы уникального индекса или ограничения, например первичного ключа. Она должна точно совпадать с одним из них, иначе PostgreSQL выдаст «there is no unique or exclusion constraint matching the ON CONFLICT specification». Вместо столбцов можно указать имя ограничения:
ON CONFLICT ON CONSTRAINT products_pkey. EXCLUDEDэто строка, которую вы пытались вставить.EXCLUDED.priceэто новая цена, аproducts.priceуже сохранённая.DO UPDATE SETменяет только перечисленные столбцы. Остальные столбцы существующей строки сохраняют свои значения.
Уникальный индекс с WHERE (частичный индекс) тоже подходит как цель, если повторить его условие: ON CONFLICT (email) WHERE deleted_at IS NULL.
Пропуск дубликатов через DO NOTHING
DO NOTHING вставляет новые строки и без ошибки отбрасывает конфликтующие. Без цели конфликта он пропускает строку, которая нарушает любое ограничение уникальности.
RETURNING выводит только lin@example.com. Пропущенная строка не возвращается, поэтому DO NOTHING ... RETURNING показывает, какие строки были новыми.
Увеличение счётчика
Чтобы увеличить сохранённое значение, а не заменить его, прибавьте EXCLUDED к текущему. Текущее значение указывайте с именем таблицы. Внутри DO UPDATE просто views подходит и к таблице, и к EXCLUDED, и PostgreSQL останавливается с ошибкой «column reference "views" is ambiguous».
Первый оператор вставляет строку со значением 1, а два следующих прибавляют к нему, так что у /home в итоге 3.
Обновление, только если значение изменилось
WHERE после DO UPDATE SET решает для каждой строки, выполнять ли обновление. Если условие ложно, строка остаётся как есть и не считается ни вставленной, ни обновлённой. Так можно сохранять только самые свежие данные или не записывать новую версию строки, когда ничего не изменилось.
A-100 сохраняет 10, потому что входящая строка старше. B-200 получает 25. Чтобы пропускать обновления, которые записали бы те же значения, используйте WHERE prices.price IS DISTINCT FROM EXCLUDED.price; это условие правильно работает и с NULL.
Как отличить вставленные строки от обновлённых
RETURNING работает в upsert так же, как в любой вставке. Чтобы различить два случая, читайте системный столбец xmax: он равен 0 для строки, которую вставил этот оператор:
A-100 возвращается с 8 и inserted false, C-300 с 7 и true. xmax это внутренняя деталь, а не документированный API, так что относитесь к нему как к удобному приёму. Конструкция целиком описана на странице о RETURNING.
Ошибки, которые вы встретите
- «ON CONFLICT DO UPDATE command cannot affect row a second time»: один и тот же ключ встречается дважды в
VALUESилиSELECTодного оператора. Сначала уберите дубликаты, например черезSELECT DISTINCT ON (sku) .... - «column reference is ambiguous»: указывайте столбец с именем таблицы в выражениях
SETи вWHERE, как показано выше. - Пропуски в id identity или serial: вставка, которая превратилась в обновление, уже взяла значение из последовательности, поэтому номера id пропускаются. Это нормально.
MERGE (PostgreSQL 15 и новее) тоже умеет вставлять или обновлять, но не защищает от того, что две сессии одновременно вставят один и тот же ключ. Для upsert по ключу используйте ON CONFLICT. Все параметры перечислены в официальном справочнике по INSERT.
Часто задаваемые вопросы
Есть ли в PostgreSQL оператор UPSERT?
Ключевого слова UPSERT нет. В PostgreSQL 9.5 появился INSERT ... ON CONFLICT, который вставляет строку или, если она нарушила бы ограничение уникальности, обновляет существующую строку (DO UPDATE) или пропускает её (DO NOTHING).
Безопасен ли INSERT ... ON CONFLICT при одновременных вставках?
Да. Каждая предложенная строка заканчивается либо вставкой, либо обновлением, даже если другие сессии в тот же момент вставляют тот же ключ. SELECT, а за ним INSERT или UPDATE в коде приложения такой гарантии не дают.
Что выбрать для upsert: MERGE или ON CONFLICT?
Для вставки или обновления по ключу используйте ON CONFLICT. MERGE (PostgreSQL 15 и новее) покрывает больше сценариев, например удаление совпавших строк. При одновременной вставке одного ключа он может упасть с нарушением уникальности, а ON CONFLICT этого избегает.
Почему возникает ошибка «there is no unique or exclusion constraint matching the ON CONFLICT specification»?
Столбцы в ON CONFLICT (...) должны точно совпадать с уникальным индексом или ограничением. Добавьте ограничение UNIQUE или уникальный индекс на эти столбцы или укажите столбцы уже существующего.