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

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

Каков наилучший способ эмуляции INSERT IGNORE и ON DUPLICATE KEY UPDATE с помощью PostgreSQL?

    Попробуйте сделать ОБНОВЛЕНИЕ. Если он не изменяет ни одну строку, что означает, что она не существует, то и вставка. Очевидно, вы делаете это внутри транзакции.

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

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

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

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

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

    INSERT … НА КОНФЛИКТЕ НИЧЕГО / ОБНОВЛЕНИЕ (“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.

    Чтобы получить логику игнорирования вставки, вы можете сделать что-то вроде ниже. Я просто попробовал вставить из выражения выбора литеральных значений, которые лучше всего работают, тогда вы можете замаскировать дубликаты ключей с помощью предложения 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 или выше, новый синтаксис ON CONFLICT DO NOTING должен работать:

     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 поддерживает объект схемы, называемый правилом .

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

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

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

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

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

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

     q = 'ALTER id_column serial RESTART WITH 1' 

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

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

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

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

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

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

     DO $do$ BEGIN PERFORM id FROM whatever_table; IF NOT FOUND THEN -- INSERT stuff END IF; END $do$; 
    Interesting Posts

    Использование getline () в C ++

    Как создать подписанный файл apk для выпуска с помощью Gradle?

    ограничение отладки java ssl debug

    Почему мои наушники не требуют драйверов?

    Скрипт Powershell для экспорта всех устройств в диспетчере устройств в виде дерева или списка?

    Установите непрозрачность фонового изображения, не затрагивая дочерние элементы.

    Восстановление переменной PATH Windows XP

    Загружать локальные данные JSON в jQgrid без AddJsonRows

    Entity Framework создает множественное имя таблицы, но представление ожидает уникальное имя таблицы?

    IISExpress возвращает ошибку 503 от удаленных компьютеров

    Rsync N новых файлов в каталоге

    Неинициализированная константа ActiveSupport :: Зависимости :: Mutex (NameError)

    Регистрация активности пользователя, телеметрия (и переменные в глобальных обработчиках исключений)

    Исключение MaxJsonLength в ASP.NET MVC во время JavaScriptSerializer

    Отправка 2D-массивов в Fortran с помощью MPI_Gather

    Давайте будем гением компьютера.