Обновление и новые значения в PostgreSQL [дубликат]

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

Подобно большинству ответов, но работает немного быстрее:

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

Лично я установил «правило», прикрепленное к инструкции 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));

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

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

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

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

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

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

MySQL INSERT ... ON DUPLICATE KEY UPDATE можно прямо перефразировать до a 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?

Нет простой команды для этого.

Самый правильный подход - использовать функцию, такую ​​как функция из 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;

: 2 было возвращено:

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

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

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

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

Мое решение, подобно JWP, состоит в том, чтобы объединить стирание и замену, создавая запись слияния в вашем приложении.

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

Альтернатива обновления отдельных строк - проверка исключений то вставка - или некоторая комбинация отвратительного кода, медленная и часто ломающаяся, потому что (как упоминалось выше) нестандартная обработка исключений 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

Я настраиваю функцию «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... $$)

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

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

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

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

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

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

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

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

Eureka! Я понял способ сделать это в одном запросе: используйте 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');

Я использую эту функцию 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

Я искал то же самое, когда я пришел сюда, но отсутствие общей функции «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

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