Что происходит с дубликатами при вставке нескольких строк?

Я использую скрипт Python, который вставляет большой объем данных в базу данных Postgres, я использую один запрос для выполнения нескольких вставок строк:

INSERT INTO table (col1,col2) VALUES ('v1','v2'),('v3','v4') ... etc

Мне было интересно, что произойдет, если он ударит дубликат ключа для вставки. Это остановит весь запрос и выдаст исключение? Или он просто проигнорирует вставку этой конкретной строки и пойдет дальше?

Источник

2 ответа

Решение

INSERT просто вставит все строки, и ничего особенного не произойдет, если только у вас нет какого-либо ограничения, запрещающего дублирование / наложение значений (PRIMARY KEY, UNIQUE, CHECK или же EXCLUDE ограничение) - который вы не упомянули в своем вопросе. Но это то, что вы, вероятно, беспокоитесь.

Предполагая UNIQUE или ограничение PK на (col1,col2) Вы имеете дело с учебником UPSERT ситуация. Много связанных вопросов и ответов найти здесь.

Как правило, если какое-либо ограничение нарушается, возникает исключение, которое (если оно не захвачено процедурным языком на стороне сервера, таким как plpgsql) откатит не только оператор, но и всю транзакцию.

Без одновременных записей

Т.е. никакие другие транзакции не будут пытаться записать в одну и ту же таблицу одновременно.

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

  • И не забудьте также удалить дубликаты во вставленном наборе, которые не будут исключены при полужидком соединении WHERE NOT EXISTS ...

Одна техника, чтобы иметь дело с обоими одновременно, была бы EXCEPT:

INSERT INTO tbl (col1, col2)
VALUES
  (text 'v1', text 'v2')  -- explicit type cast may be needed in 1st row
, ('v3', 'v4')
, ('v3', 'v4')  -- beware of dupes in source
EXCEPT SELECT col1, col2 FROM tbl;

EXCEPT без ключевого слова ALL складывает дублирующиеся строки в источнике. Если вы знаете, что нет дураков, EXCEPT ALL или один из других методов будет быстрее. Связанные с:

Как правило, если целевой стол большой, WHERE NOT EXISTS в комбинации с DISTINCT на источнике, вероятно, будет быстрее:

INSERT INTO tbl (col1, col2)
SELECT *
FROM  (
   SELECT DISTINCT *
   FROM  (
       VALUES
         (text 'v1', text'v2')
       , ('v3', 'v4')
       , ('v3', 'v4')  -- dupes in source
      ) t(c1, c2)
   ) t
WHERE NOT EXISTS (
   SELECT 1
   FROM   tbl
   WHERE  col1 = t.c1 AND col2 = t.c2
   );

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

Связанные с:

С одновременной записью

Используйте Postgres UPSERT реализация INSERT ... ON CONFLICT ... в Postgres 9.5 или позже:

INSERT INTO tbl (col1,col2)
SELECT DISTINCT *  -- still can't insert the same row more than once
FROM  (
   VALUES
     (text 'v1', text 'v2')
   , ('v3','v4')
   , ('v3','v4')  -- you still need to fold dupes in source!
  ) t(c1, c2)
ON CONFLICT DO NOTHING;  -- ignores rows with *any* conflict!

Более сложный связанный ответ:

Документация:

Ссылочный ответ Крейга для UPSERT проблемы:

Источник

Это остановит весь запрос и выдаст исключение? Да.

Чтобы избежать этого, вы можете посмотреть на следующий SO вопрос, который описывает, как избежать, чтобы Postgres выдавал ошибку для нескольких вставок, когда некоторые из вставленных ключей уже существуют в БД.

Вы должны в основном сделать это:

INSERT INTO DBtable
        (id, field1)
    SELECT 1, 'value'
    WHERE
        NOT EXISTS (
            SELECT id FROM DBtable WHERE id = 1
);
Источник
Другие вопросы по тегам