как эмулировать " insert ignore "и" on duplicate key update " (SQL merge) с postgresql?

С PostgreSQL 9.5, это теперь стандартные функции (как MySQL имел в течение нескольких лет):

вставить ... НА КОНФЛИКТ НИЧЕГО НЕ ДЕЛАТЬ / ОБНОВИТЬ ("UPSERT")

9.5 обеспечивает поддержку операций "UPSERT". INSERT расширяется, чтобы принять предложение on CONFLICT DO UPDATE/IGNORE. Это предложение определяет альтернативное действие, которое следует предпринять в случае потенциального дублирования нарушение.

...

дальнейший пример нового синтаксиса:

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1) 
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;

правка: в случае, если вы пропустили Уоррен, PG9. 5 теперь имеет Это изначально; время для обновления!

---

основываясь на ответе Билла Карвина, чтобы объяснить, как будет выглядеть подход на основе правил (передача из другой схемы в той же БД и с многоколоночным первичным ключом):

CREATE RULE "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table"
  WHERE EXISTS(SELECT 1 FROM my_table 
                WHERE (pk_col_1, pk_col_2)=(NEW.pk_col_1, NEW.pk_col_2))
  DO INSTEAD NOTHING;
INSERT INTO my_table SELECT * FROM another_schema.my_table WHERE some_cond;
DROP RULE "my_table_on_duplicate_ignore" ON "my_table";

Примечание: правило применяется ко всем INSERT операции до тех пор, пока правило не будет удалено, поэтому не совсем ad hoc.

для получения вставить игнорировать логики вы можете сделать что-то вроде ниже. Я обнаружил, что просто вставка из инструкции select литеральных значений работает лучше всего, тогда вы можете замаскировать дубликаты ключей с помощью предложения NOT EXISTS. Чтобы получить обновление на дублирующую логику, я подозреваю, что цикл pl/pgsql будет необходим.

INSERT INTO manager.vin_manufacturer
(SELECT * FROM( VALUES
  ('935',' Citroën Brazil','Citroën'),
  ('ABC', 'Toyota', 'Toyota'),
  ('ZOM',' OM','OM')
  ) as tmp (vin_manufacturer_id, manufacturer_desc, make_desc)
  WHERE NOT EXISTS (
    --ignore anything that has already been inserted
    SELECT 1 FROM manager.vin_manufacturer m where m.vin_manufacturer_id = tmp.vin_manufacturer_id)
)

INSERT INTO mytable(col1,col2) 
    SELECT 'val1','val2' 
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE col1='val1')

для тех из вас, у кого есть Postgres 9.5 или выше, новый НА КОНФЛИКТ НИЧЕГО НЕ ДЕЛАТЬ синтаксис должен работать:

INSERT INTO target_table (field_one, field_two, field_three ) 
SELECT field_one, field_two, field_three
FROM source_table
ON CONFLICT (field_one) DO NOTHING;

для тех из нас, кто имеет более раннюю версию, это право присоединиться будут работать вместо:

INSERT INTO target_table (field_one, field_two, field_three )
SELECT source_table.field_one, source_table.field_two, source_table.field_three
FROM source_table 
LEFT JOIN target_table ON source_table.field_one = target_table.field_one
WHERE target_table.field_one IS NULL;

похоже, PostgreSQL поддерживает объект схемы под названием A правила.

http://www.postgresql.org/docs/current/static/rules-update.html

вы можете создать правило ON INSERT для данной таблицы, что делает его сделать NOTHING если строка существует с заданным значением первичного ключа, или же сделать это сделать UPDATE вместо INSERT если строка существует с заданным значением первичного ключа.

Я не пробовал это сам, поэтому я не могу говорить из опыта или приведите пример.

Это решение позволяет избежать использования правил:

BEGIN
   INSERT INTO tableA (unique_column,c2,c3) VALUES (1,2,3);
EXCEPTION 
   WHEN unique_violation THEN
     UPDATE tableA SET c2 = 2, c3 = 3 WHERE unique_column = 1;
END;

но он имеет недостаток производительности (см. PostgreSQL.org):

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

Как @hanmari упомянул в своем комментарии. при вставке в таблицы postgres, on conflict (..) ничего не делать-это лучший код для использования, чтобы не вставлять дубликаты данных.:

query = "INSERT INTO db_table_name(column_name)
         VALUES(%s) ON CONFLICT (column_name) DO NOTHING;"

строка кода ON CONFLICT позволит оператору insert по-прежнему вставлять строки данных. Код запроса и значений является примером вставленной даты из Excel в таблицу БД postgres. У меня есть ограничения, добавленные в таблицу postgres, которую я использую, чтобы убедиться, что поле ID уникально. Вместо запуска удаление строк данных, которые совпадают, я добавляю строку кода sql, которая перенумеровывает столбец ID, начиная с 1. Пример:

q = 'ALTER id_column serial RESTART WITH 1'

Если мои данные имеют поле ID, я не использую его в качестве основного ID/serial ID, я создаю столбец ID и устанавливаю его в serial. Я надеюсь, что эта информация будет полезна для всех. *У меня нет высшего образования в области разработки программного обеспечения/кодирования. Все, что я знаю в кодировании, я изучаю самостоятельно.

на bulk, вы всегда можете удалить строку перед вставкой. Удаление строки, которая не существует, не вызывает ошибки, поэтому ее безопасно пропускают.

для сценариев импорта данных, чтобы заменить "если не существует", в некотором смысле, есть немного неудобная формулировка, которая тем не менее работает:

DO
$do$
BEGIN
PERFORM id
FROM whatever_table;

IF NOT FOUND THEN
-- INSERT stuff
END IF;
END
$do$;