Вставить, при повторном обновлении в PostgreSQL?

Несколько месяцев назад я узнал из ответа на Stack Overflow, как выполнять несколько обновлений одновременно в MySQL, используя следующий синтаксис:

INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z)
ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2);

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

Чтобы уточнить, я хочу вставить несколько вещей, и если они уже существуют, чтобы обновить их.

Источник

18 ответов

Решение

PostgreSQL начиная с версии 9.5 имеет синтаксис UPSERT с предложением ON CONFLICT. со следующим синтаксисом (аналогично MySQL)

INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;

Поиск в почтовых архивах postgresql "upsert" приводит к тому, что в руководстве вы найдете пример того, что вы, возможно, захотите сделать:

Пример 38-2. Исключения с UPDATE/INSERT

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

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        -- note that "a" must be unique
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

В списке рассылки хакеров, возможно, есть пример того, как сделать это массово, используя CTE в 9.1 и выше.

WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;

Смотрите ответ a_horse_with_no_name для более ясного примера.

Источник

Предупреждение: это не безопасно, если выполняется из нескольких сеансов одновременно (см. Предостережения ниже).

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

UPDATE table SET field='C', field2='Z' WHERE id=3;
INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

ОБНОВЛЕНИЕ будет успешным, если строка с "id=3" уже существует, в противном случае это не имеет никакого эффекта.

INSERT будет успешным, только если строка с "id=3" еще не существует.

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

Это работает очень хорошо, когда выполняется изолированно или на заблокированной таблице, но в зависимости от условий гонки, которые могут означать, что он все равно может потерпеть неудачу с ошибкой повторяющегося ключа, если строка вставлена ​​одновременно, или может завершиться, если строка не будет вставлена, если строка удалена одновременно, SERIALIZABLE Транзакция на PostgreSQL 9.1 или выше будет надежно обрабатывать ее за счет очень высокой частоты отказов сериализации, что означает, что вам придется много повторяться. Посмотрите, почему так сложно, что обсуждает этот случай более подробно.

Этот подход также может быть потерян обновления в read committed изоляция, если приложение не проверяет число строк, подверженных влиянию, и проверяет, что либо insert или update повлиял на ряд.

Источник

В PostgreSQL 9.1 этого можно достичь с помощью записываемого CTE ( общее табличное выражение):

WITH new_values (id, field1, field2) as (
  values 
     (1, 'A', 'X'),
     (2, 'B', 'Y'),
     (3, 'C', 'Z')

),
upsert as
( 
    update mytable m 
        set field1 = nv.field1,
            field2 = nv.field2
    FROM new_values nv
    WHERE m.id = nv.id
    RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1 
                  FROM upsert up 
                  WHERE up.id = new_values.id)

Смотрите эти записи в блоге:

Обратите внимание, что это решение не предотвращает нарушение уникального ключа, но оно не уязвимо для потерянных обновлений.
Смотрите продолжение Крейг Рингер на dba.stackexchange.com

Источник

В PostgreSQL 9.5 и новее вы можете использовать INSERT ... ON CONFLICT UPDATE,

Смотрите документацию.

MySQL INSERT ... ON DUPLICATE KEY UPDATE может быть непосредственно перефразировано в ON CONFLICT UPDATE, Синтаксис стандарта SQL также не является, они оба являются специфичными для базы данных расширениями. Есть веские причины MERGE не был использован для этого, новый синтаксис не был создан просто для удовольствия. (Синтаксис MySQL также имеет проблемы, которые означают, что он не был принят напрямую).

например, данная настройка:

CREATE TABLE tablename (a integer primary key, b integer, c integer);
INSERT INTO tablename (a, b, c) values (1, 2, 3);

запрос MySQL:

INSERT INTO tablename (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

будет выглядеть так:

INSERT INTO tablename (a, b, c) values (1, 2, 10)
ON CONFLICT (a) DO UPDATE SET c = tablename.c + 1;

Отличия:

  • Необходимо указать имя столбца (или уникальное имя ограничения), которое будет использоваться для проверки уникальности. Это ON CONFLICT (columnname) DO

  • Ключевое слово SET должны быть использованы, как если бы это было нормально UPDATE заявление

Он также имеет несколько приятных особенностей:

  • Вы можете иметь WHERE пункт на вашем UPDATE (позволяя вам эффективно повернуть ON CONFLICT UPDATE в ON CONFLICT IGNORE для определенных значений)

  • Предложенные для вставки значения доступны как переменная строки EXCLUDED, которая имеет ту же структуру, что и таблица назначения. Вы можете получить исходные значения в таблице, используя имя таблицы. Так что в этом случае EXCLUDED.c будет 10 (потому что это то, что мы пытались вставить) и "table".c будет 3 потому что это текущее значение в таблице. Вы можете использовать один или оба в SET выражения и WHERE пункт.

Для справки по upsert смотрите Как сделать UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) в PostgreSQL?

Источник

Я искал то же самое, когда пришел сюда, но отсутствие общей функции "upsert" беспокоило меня немного, поэтому я подумал, что вы можете просто пропустить обновление и вставить sql в качестве аргументов этой функции из руководства

это будет выглядеть так:

CREATE FUNCTION upsert (sql_update TEXT, sql_insert TEXT)
    RETURNS VOID
    LANGUAGE plpgsql
AS $$
BEGIN
    LOOP
        -- first try to update
        EXECUTE sql_update;
        -- check if the row is found
        IF FOUND THEN
            RETURN;
        END IF;
        -- not found so insert the row
        BEGIN
            EXECUTE sql_insert;
            RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing and loop
        END;
    END LOOP;
END;
$$;

и, возможно, чтобы сделать то, что вы изначально хотели сделать, пакетный "upsert", вы можете использовать Tcl, чтобы разделить sql_update и зациклить отдельные обновления, попадание в preformance будет очень маленьким, см. http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

самая высокая стоимость выполнения запроса из вашего кода, на стороне базы данных стоимость выполнения намного меньше

Источник

Нет простой команды, чтобы сделать это.

Самый правильный подход - использовать функцию, подобную той, что есть в документации.

Другое решение (хотя и не столь безопасное) - обновить с возвратом, проверить, какие строки были обновлениями, и вставить остальные

Что-то вроде:

update table
set column = x.column
from (values (1,'aa'),(2,'bb'),(3,'cc')) as x (id, column)
where table.id = x.id
returning id;

при условии, что id:2 был возвращен:

insert into table (id, column) values (1, 'aa'), (3, 'cc');

Конечно, это рано или поздно выручит (в параллельной среде), поскольку здесь есть ясное условие гонки, но обычно это будет работать.

Вот более длинная и более полная статья по теме.

Источник

Я использую эту функцию слияния

CREATE OR REPLACE FUNCTION merge_tabla(key INT, data TEXT)
  RETURNS void AS
$BODY$
BEGIN
    IF EXISTS(SELECT a FROM tabla WHERE a = key)
        THEN
            UPDATE tabla SET b = data WHERE a = key;
        RETURN;
    ELSE
        INSERT INTO tabla(a,b) VALUES (key, data);
        RETURN;
    END IF;
END;
$BODY$
LANGUAGE plpgsql
Источник

Лично я установил "правило", прикрепленное к оператору вставки. Допустим, у вас есть таблица "dns", в которой записано количество посещений DNS для каждого клиента в отдельности:

CREATE TABLE dns (
    "time" timestamp without time zone NOT NULL,
    customer_id integer NOT NULL,
    hits integer
);

Вы хотели иметь возможность повторно вставлять строки с обновленными значениями или создавать их, если они еще не существовали. Введите идентификатор клиента и время. Что-то вроде этого:

CREATE RULE replace_dns AS 
    ON INSERT TO dns 
    WHERE (EXISTS (SELECT 1 FROM dns WHERE ((dns."time" = new."time") 
            AND (dns.customer_id = new.customer_id)))) 
    DO INSTEAD UPDATE dns 
        SET hits = new.hits 
        WHERE ((dns."time" = new."time") AND (dns.customer_id = new.customer_id));

Обновление: это может привести к сбою при одновременной вставке, так как это приведет к исключениям unique_violation. Однако незавершенная транзакция будет продолжена и завершится успешно, и вам просто нужно повторить прерванную транзакцию.

Тем не менее, если все время происходит множество операций вставки, вам нужно установить блокировку таблицы вокруг операторов вставки: блокировка SHARE ROW EXCLUSIVE предотвратит любые операции, которые могут вставлять, удалять или обновлять строки в вашей целевой таблице. Однако обновления, которые не обновляют уникальный ключ, безопасны, поэтому, если вы не выполняете никаких действий, используйте вместо этого рекомендательные блокировки.

Кроме того, команда COPY не использует ПРАВИЛА, поэтому, если вы вставляете с помощью COPY, вам нужно будет использовать триггеры.

Источник

Я настраиваю функцию "upsert" выше, если вы хотите вставить и заменить:

`

 CREATE OR REPLACE FUNCTION upsert(sql_insert text, sql_update text)

 RETURNS void AS
 $BODY$
 BEGIN
    -- first try to insert and after to update. Note : insert has pk and update not...

    EXECUTE sql_insert;
    RETURN;
    EXCEPTION WHEN unique_violation THEN
    EXECUTE sql_update; 
    IF FOUND THEN 
        RETURN; 
    END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;
 ALTER FUNCTION upsert(text, text)
 OWNER TO postgres;`

А после выполнения сделайте что-то вроде этого:

SELECT upsert($$INSERT INTO ...$$,$$UPDATE... $$)

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

  • проверь скорость...
Источник

Похож на наиболее понравившийся ответ, но работает немного быстрее:

WITH upsert AS (UPDATE spider_count SET tally=1 WHERE date='today' RETURNING *)
INSERT INTO spider_count (spider, tally) SELECT 'Googlebot', 1 WHERE NOT EXISTS (SELECT * FROM upsert)

(источник: http://www.the-art-of-web.com/sql/upsert/)

Источник

Согласно документации PostgreSQL INSERT заявление, обработка ON DUPLICATE KEY дело не поддерживается. Эта часть синтаксиса является проприетарным расширением MySQL.

Источник 
CREATE OR REPLACE FUNCTION save_user(_id integer, _name character varying)
  RETURNS boolean AS
$BODY$
BEGIN
    UPDATE users SET name = _name WHERE id = _id;
    IF FOUND THEN
        RETURN true;
    END IF;
    BEGIN
        INSERT INTO users (id, name) VALUES (_id, _name);
    EXCEPTION WHEN OTHERS THEN
            UPDATE users SET name = _name WHERE id = _id;
        END;
    RETURN TRUE;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
Источник

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

Мое решение, похожее на JWP, заключается в массовом удалении и замене, генерируя запись слияния в вашем приложении.

Это довольно пуленепробиваемый, независимый от платформы, и поскольку на клиента не может быть более 20 настроек, это всего лишь 3 вызова с достаточно низкой нагрузкой - вероятно, самый быстрый метод.

Альтернативой обновления отдельных строк - проверки исключений, а затем вставки - или какой-либо комбинации является отвратительный код, медленный и часто ломающийся, потому что (как упомянуто выше) нестандартная обработка исключений SQL изменяется с db на db - или даже выпуск для выпуска.

 #This is pseudo-code - within the application:
 BEGIN TRANSACTION - get transaction lock
 SELECT all current name value pairs where id = $id into a hash record
 create a merge record from the current and update record
  (set intersection where shared keys in new win, and empty values in new are deleted).
 DELETE all name value pairs where id = $id
 COPY/INSERT merged records 
 END TRANSACTION
Источник

Для объединения небольших наборов хорошо использовать вышеуказанную функцию. Однако, если вы объединяете большие объемы данных, я бы посоветовал заглянуть в http://mbk.projects.postgresql.org/

В настоящее время я знаю о наилучшей практике:

  1. КОПИРОВАНИЕ новых / обновленных данных во временную таблицу (конечно, или вы можете сделать INSERT, если стоимость в порядке)
  2. Получить блокировку [необязательно] (рекомендуется использовать для блокировки таблиц, IMO)
  3. Слияние. (забавная часть)
Источник

PostgreSQL >= v15

Большие новости по этой теме, так как в PostgreSQL v15 можно использоватьMERGEкоманда. Фактически, эта долгожданная функция была указана первым из улучшений версии v15.

Это похоже на, но более ориентировано на пакетную обработку. Он имеет мощныйWHEN MATCHEDпротивWHEN NOT MATCHEDструктура, дающая возможностьINSERT,UPDATEилиDELETEна таких условиях.

Это не только облегчает массовые изменения, но даже добавляет больше контроля над традицией иINSERT ... ON CONFLICT

Взгляните на этот очень полный образец с официальной страницы:

      MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE;

PostgreSQL v9, v10, v11, v12, v13, v14

Если версия ниже v15 и выше v9.5, вероятно, лучше всего использоватьUPSERTсинтаксис, сON CONFLICTпункт

Источник

UPDATE вернет количество измененных строк. Если вы используете JDBC (Java), вы можете сравнить это значение с 0 и, если строки не были затронуты, вместо этого запустить INSERT. Если вы используете какой-то другой язык программирования, возможно, число измененных строк все еще можно получить, проверьте документацию.

Это может быть не так элегантно, но у вас гораздо более простой SQL, который более тривиально использовать из вызывающего кода. Иными словами, если вы пишете 10-строчный скрипт на PL/PSQL, вам, вероятно, нужно иметь модульный тест того или иного вида только для него.

Источник

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

Эврика! Я нашел способ сделать это в одном запросе: использовать UPDATE ... RETURNING проверить, были ли затронуты какие-либо строки:

CREATE TABLE foo (k INT PRIMARY KEY, v TEXT);

CREATE FUNCTION update_foo(k INT, v TEXT)
RETURNS SETOF INT AS $$
    UPDATE foo SET v = $2 WHERE k = $1 RETURNING $1
$$ LANGUAGE sql;

CREATE FUNCTION upsert_foo(k INT, v TEXT)
RETURNS VOID AS $$
    INSERT INTO foo
        SELECT $1, $2
        WHERE NOT EXISTS (SELECT update_foo($1, $2))
$$ LANGUAGE sql;

UPDATE должно быть сделано в отдельной процедуре, потому что, к сожалению, это синтаксическая ошибка:

... WHERE NOT EXISTS (UPDATE ...)

Теперь все работает как нужно:

SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');
Источник

Вот пример, как сделать upsert с параметрами и без специальных конструкций sql, если у вас есть особое условие (иногда вы не можете использовать «при конфликте», потому что вы не можете создать ограничение)

      WITH upd AS
(
    update view_layer set metadata=:metadata where layer_id = :layer_id and view_id = :view_id returning id
)
insert into view_layer (layer_id, view_id, metadata)
(select :layer_id layer_id, :view_id view_id, :metadata metadata FROM view_layer l 
where NOT EXISTS(select id FROM upd WHERE id IS NOT NULL) limit 1)
returning id

может быть, это будет полезно

Источник
Другие вопросы по тегам