Как эмулировать «вставить игнорировать» и «при обновлении дубликата ключа» (объединение SQL) с postgresql?

Некоторые SQL-серверы имеют функцию, гдеINSERT пропускается, если это нарушит ограничение первичного / уникального ключа. Например, MySQL имеетINSERT IGNORE.

Какой лучший способ подражатьINSERT IGNORE а такжеON DUPLICATE KEY UPDATE с PostgreSQL?

 Dave Jarvis03 апр. 2012 г., 20:16
Смотрите также:stackoverflow.com/questions/5269590/…
 NeverEndingQueue13 окт. 2018 г., 13:55
Эмуляция MySQL:ON DUPLICATE KEY UPDATE на PgSQL 9.5 все еще несколько невозможно, потому что PgSQLON CLAUSE Эквивалент требует, чтобы вы указали имя ограничения, в то время как MySQL может зафиксировать любое ограничение без необходимости его определения. Это мешает мне "эмулировать" эта функция без переписывания запросов.
 a_horse_with_no_name31 авг. 2015 г., 08:41
 warren07 апр. 2016 г., 20:39
по состоянию на 9.5 изначально возможно:stackoverflow.com/a/34639631/4418

Ответы на вопрос(11)

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

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

Строка кода ON CONFLICT позволит оператору вставки вставлять строки данных. Код запроса и значений является примером вставленной даты из Excel в таблицу postgres db. У меня есть ограничения, добавленные в таблицу postgres, которую я использую, чтобы убедиться, что поле идентификатора уникально. Вместо того, чтобы выполнять удаление одинаковых строк данных, я добавляю строку кода SQL, которая перенумеровывает столбец идентификатора, начиная с 1. Пример:

q = 'ALTER id_column serial RESTART WITH 1'

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

rule.

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

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

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

 30 июн. 2015 г., 00:08
@ cdhowie, спасибо, это классный совет.
 29 июн. 2015 г., 22:05
PostgreSQL поддерживает транзакционный DDL, что означает, что если вы создадите правило и отбросите его в пределах одной транзакции, правило никогда не будет видно за пределами (и, следовательно, никогда не окажет никакого влияния вне) этой транзакции.
 18 июн. 2009 г., 01:41
Да, у меня тоже будут такие же вопросы. Механизм правил - это самая близкая вещь, которую я мог бы найти в PostgreSQL к INSERT IGNORE или ON ON DUPLICATE KEY UPDATE. Если мы поищем в Google & quot; postgresql об обновлении дубликата ключа & quot; Вы найдете других людей, рекомендующих механизм Правил, даже если Правило будет применяться к любой ВСТАВКЕ, а не только на специальной основе.
 gpilotino18 июн. 2009 г., 00:21
если я хорошо понял, эти правила - триггеры, которые выполняются каждый раз, когда вызывается оператор Что делать, если я хочу применить правило только для одного запроса? я должен создать правило, а затем немедленно уничтожить его? (как насчет условий гонки?)

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

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

CREATE RULE "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table"
  WHERE EXISTS(SE,LECT 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 операции до тех пор, пока правило не будет удалено, так что не совсем так.

 05 нояб. 2013 г., 17:30
Обратите внимание, это не игнорирует дубликаты в команде вставки.
 26 мая 2012 г., 16:40
Хороший! Это очень полезно.
 10 февр. 2014 г., 18:43
@ сема ты имеешь в виду, еслиanother_schema.my_table содержит дубликаты в соответствии с ограничениямиmy_table?
 21 июн. 2012 г., 01:54
Вау, это сработало как шарм. Это почти так же хорошо, как иметьINSERT IGNORE!
 12 февр. 2014 г., 09:39
@EoghanM Я протестировал правило в postgresql 9.3 и все еще мог вставлять дубликаты с несколькими операторами вставки строк, например, например. INSERT INTO & quot; my_table & quot; (А, б), (а, б); (Предполагая, что строка (a, b) еще не существовала в «my_table».)
Решение Вопроса

что означает, что он не существует, то вставка тоже. Очевидно, вы делаете это внутри транзакции.

Конечно, вы можете заключить это в функцию, если не хотите помещать дополнительный код на стороне клиента. Вам также нужна петля для очень редкого состояния гонки в этом мышлении.

Пример этого в документации:http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.htmlПример 40-2 справа внизу.

Это обычно самый простой способ. Вы можете творить чудеса с правилами, но это, скорее всего, будет намного сложнее. Я рекомендую этот подход в любой день.

Это работает для одной строки или нескольких строк. Если вы имеете дело с большим количеством строк, например, из подзапроса, лучше всего разбить его на два запроса, один для INSERT и один для UPDATE (как, конечно, подходящее соединение / выборка - нет необходимости писать свой основной запрос). фильтр дважды)

 gpilotino18 июн. 2009 г., 15:23
итоговый ответ - "ВСТАВИТЬ ... ГДЕ" :] благодарю вас.
 07 апр. 2016 г., 15:02
Update: с PostgreSQL 9.5 это теперь так же просто, какINSERT ... ON CONFLICT DO NOTHING;, Смотри также ответstackoverflow.com/a/34639631/2091700.
 06 апр. 2018 г., 20:44
С версией 9.5 он теперь является "нативным". пожалуйста, проверьте комментарий @Alphaaa (просто рекламируйте комментарий, который рекламирует ответ)
 18 июн. 2009 г., 13:32
Магнус имел в виду, что вы используете такой запрос: & quot; начать транзакцию; создать временную таблицу временная_таблица как select * from test, где false; скопировать временную таблицу из «data_file.csv»; проверка таблицы блокировки; обновить набор тестов data = временная_таблица.данные из временной_таблицы, где test.id = временная_таблица.id; вставить в тест выберите * из временного_таблицы, где идентификатор не в (выберите идентификатор из теста) как & quot;
 gpilotino18 июн. 2009 г., 11:41
& quot; Если вы имеете дело с большим количеством строк & quot; это точно мой случай. Я хочу массово обновить / вставить строки и с MySQL я могу сделать это только с одним запросом без каких-либо циклов. Теперь мне интересно, возможно ли это и с postgresql: использовать только один запрос для массового обновления или вставки. Вы говорите: «Лучше всего разделить его на два запроса: один для INSERT и один для UPDATE». но как я могу сделать вставку, которая не выбрасывает ошибки на дубликаты ключей? (т.е. "INSERT IGNORE")

insert ignore Логика вы можете сделать что-то вроде ниже. Я обнаружил, что вставка из оператора 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)
)
 04 июл. 2013 г., 06:17
Вы всегда можете выбрать с определенным ключевым словом.
 09 июн. 2015 г., 17:22
Так же, как к вашему сведению, «ГДЕ НЕ СУЩЕСТВУЕТ» трюк не работает для нескольких транзакций, потому что разные транзакции не могут видеть вновь добавленные данные из других транзакций.
 22 апр. 2013 г., 01:40
Что если tmp содержит дублирующую строку, что может произойти?

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):

A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

родная функциональность (лайкMySQL имел в течение нескольких лет):

INSERT ... ON CONFLICT DO NOTHING/UPDATE ("UPSERT")

9.5 brings support for "UPSERT" operations. INSERT is extended to accept an ON CONFLICT DO UPDATE/IGNORE clause. This clause specifies an alternative action to take in the event of a would-be duplicate violation.

...

Further example of new syntax:

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1) 
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;
 13 дек. 2018 г., 18:41
@EoghanM - Альфааа не OP. gpilotino есть.
 07 апр. 2016 г., 15:45
@ Alphaaa - оцените это :) ... хотя, когда этот вопрос был задан, это было не так просто, как сейчас :)
 07 апр. 2016 г., 15:03
Хорошее обновление! Я "рекламировал" Ваш ответ на принятый.
 13 дек. 2018 г., 14:57
@ Альфаа, не могли бы вы изменить свой принятый ответ на этот? Время и версии движутся!

ON CONFLICT DO NOTHING Синтаксис должен работать:

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;
 22 мая 2018 г., 08:32
Второй подход не работает при создании большой вставки в параллельной среде. Вы получаетеUnique violation: 7 ERROR: duplicate key value violates unique constraint когдаtarget_table вставили еще один рядwhile этот запрос выполнялся, если их ключи действительно дублируют друг друга. Я считаю, что блокировкаtarget_table поможет, но параллельность явно пострадает.
 02 окт. 2017 г., 21:12
Это чертовски приятно! Спасибо!

Для сценариев импорта данных, чтобы заменить «ЕСЛИ НЕ СУЩЕСТВУЕТ», в некотором смысле, есть немного неловкая формулировка, которая тем не менее работает:

DO
$do$
BEGIN
PERFORM id
FROM whatever_table;

IF NOT FOUND THEN
-- INSERT stuff
END IF;
END
$do$;
INSERT INTO mytable(col1,col2) 
    SELECT 'val1','val2' 
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE col1='val1')
 23 окт. 2013 г., 22:53
Это хорошо работает.
 09 июн. 2015 г., 17:20
Это не работает с несколькими транзакциями, потому что вновь добавленные данные не видны другим транзакциям.
 07 февр. 2014 г., 17:30
Каково влияние нескольких транзакций, пытающихся сделать одно и то же? Возможно ли, что между выполнением где не существует и выполнением вставки, выполняющей какую-либо другую транзакцию, вставляется строка? И если Postgres может предотвратить это, то не будет ли postgres ввести точку синхронизации для всех этих транзакций, когда они достигнут этого?

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

 28 нояб. 2011 г., 23:33
Такой подход будет весьма подвержен странным гоночным условиям, я бы не рекомендовал его ...
 22 мар. 2013 г., 18:49
Внешний ключ может быть без проблем, если вы создаете их сDEFERRABLE INITIALLY DEFERRED флаги.
 23 нояб. 2012 г., 14:35
Это также не будет работать, когда существующие данные были изменены после вставки (но не для дублирующего ключа), и мы хотим сохранить обновления. Это сценарий, когда существуют сценарии SQL, которые написаны для ряда слегка отличающихся систем, таких как обновления db, которые выполняются в производственных, QA, dev и тестовых системах.
 19 июн. 2012 г., 17:12
... и не работает, если есть нарушения внешнего ключа.
 23 окт. 2012 г., 16:11
+1 Это просто и универсально. При осторожном использовании это может быть простым решением.

Ваш ответ на вопрос