Postgresql-аналоги myslq-евской «ON DUPLICATE KEY UPDATE»

В mysql есть очень удобная конструкция

Это очень удобно тогда, когда нужно вставить только те данные, которых в таблице нет (при этом в апдейте указывается уже существующее значение поля).
В postgresql такого, к сожалению, нет.
Но подобное поведение можно сэмулировать несколькими способами. Два самых интересных:

1. Создать правило

Этот способ удобен тем, что его нужно написать только один раз, и при любой вставке данных с уже существующим ключом выполнение работы продолжится, а самой вставки не произойдёт. Неудобен тем, что выполняется селект, да и вообще это по сути триггер, делающий лишнюю работу до вставки.
2. Обработать исключение

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

3. Вполне полноценная замена (начиная с 9.5 — https://www.postgresql.org/docs/9.5/sql-insert.html):

4 Responses so far.

  1. julia:
    Причем, если во втором способе вынести обработку исключения в php, то он станет еще и кроссплатформенным.

    Для частных случаев можно оптимизировать в некотором роде. Например, если UPDATE-ов предполагается гораздо больше чем INSERT-ов, то сначала пытаемся сделать UPDATE, проверяем кол-во обработанных записей, и если таких нет, делаем INSERT.

  2. Если вынести обработку в PHP, то соответственно работать будет медленней, ибо накладные расходы на сеть и следом более длинные транзакции и блокировки. Впрочем, вариант имеющий право на существование.

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

  3. alex:
    Самый большой минус — второго способа, то что при вставке в одном INSERT многих значений (для повышения производительности) — сгенерируется исключение, транзакция не завершится, ни одного значения вставлено не будет. например: INSERT INTO db(a, b) VALUES (1, 2), (3, 4), (3, 5); (при уникальном ключе поле a).
  4. Igor:
    Хотелось бы дополнить, сам наступил на эти грабли, мне требовалась генерация большого кол-во записей из разных таблиц в сводную, по описанным пунктам:
    1. Работает как надо, но для создания 3000000 записей из множества таблиц потребовалось 1 час, это я еще и вызывал функцию из самого postgres которая выполняет INSERT. НО все отработало корректно.
    2. Полностью согласен с комментарием «alex», часть записей при возникновении ошибки «уходит» в никуда, за то отработало за 6 минут но 5% записей не сгенерировалось.

LEAVE A COMMENT