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

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

---

еще один умный способ сделать "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" уже существует, в противном случае это не имеет никакого эффекта.

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

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

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

этот подход также С учетом потерянных обновлений в 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)

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

• Upserting через Writeable CTE
• ОЖИДАНИЕ 9.1-ЗАПИСЫВАЕМЫЙ CTE
• ПОЧЕМУ UPSERT ТАК СЛОЖНО?

---

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

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

посмотреть документация.

A 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 (слияние, вставка ... На дубликат обновления) в 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 и цикла отдельных обновлений, предварительный удар будет очень маленьким см. http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

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

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

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

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

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

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');

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

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

лично я установил "правило", прикрепленное к инструкции insert. Скажем, у вас была таблица "dns", которая записывала DNS-хиты на каждого клиента на временной основе:

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

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

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

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

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

кроме того, команда 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... $$)

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

• проверьте скорость...

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

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

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

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

 #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

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

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

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

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

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

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

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

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

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

Эврика! Я придумал способ сделать это в одном запросе: use 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 =  WHERE k =  RETURNING 
$$ LANGUAGE sql;

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

The 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');

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

текущая лучшая практика, о которой я знаю:

1. скопируйте новые / обновленные данные в временную таблицу (конечно, Или вы можете вставить, если стоимость в порядке)
2. получить блокировку [необязательно] (рекомендация предпочтительнее блокировки таблиц, IMO)
3. слияние. (забава часть)