sql - update - upsert syntax




Einfügen, bei doppeltem Update in PostgreSQL? (11)

Ähnlich wie die am meisten gemachte Antwort, funktioniert aber etwas schneller:

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)

(Quelle: http://www.the-art-of-web.com/sql/upsert/ )

Vor einigen Monaten habe ich von einer Antwort auf Stack Overflow erfahren, wie man mehrere Aktualisierungen gleichzeitig in MySQL unter Verwendung der folgenden Syntax durchführt:

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

Ich bin jetzt zu PostgreSQL gewechselt und das ist offensichtlich nicht korrekt. Es bezieht sich auf alle korrekten Tabellen, also nehme ich an, dass es sich um verschiedene Schlüsselwörter handelt, aber ich bin mir nicht sicher, wo in der PostgreSQL-Dokumentation das behandelt wird.

Um zu verdeutlichen, möchte ich einige Dinge einfügen und wenn sie bereits existieren, um sie zu aktualisieren.



Ich benutze diese Funktion 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

Ich benutzerdefinierte "upsert" -Funktion oben, wenn Sie einfügen und ersetzen möchten:

`

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

Und nach der Ausführung, tun Sie etwas wie folgt:

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

Es ist wichtig, doppelte Dollar-Kommas zu setzen, um Compiler-Fehler zu vermeiden

  • überprüfe die Geschwindigkeit ...

Ich war auf der Suche nach der gleichen Sache, als ich hierher kam, aber das Fehlen einer generischen "upsert" -Funktion hat mich ein bisschen gestört, so dass ich dachte, Sie könnten nur das Update übergeben und sql als Argumente für diese Funktion aus dem Handbuch einfügen

das würde so aussehen:

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

und vielleicht zu tun, was Sie ursprünglich tun wollten, Batch "upsert", könnten Sie Tcl verwenden, um die sql_update zu teilen und die einzelnen Updates, der Preformance-Hit wird sehr klein sein, siehe http://archives.postgresql.org/pgsql-performance/2006-04/msg00557.php

Die höchsten Kosten sind die Ausführung der Abfrage aus Ihrem Code, auf der Datenbankseite sind die Ausführungskosten viel geringer


In PostgreSQL 9.5 und neuer können Sie INSERT ... ON CONFLICT UPDATE .

Siehe die Dokumentation .

Ein MySQL INSERT ... ON DUPLICATE KEY UPDATE kann direkt in ein ON CONFLICT UPDATE . Weder ist die SQL-Standardsyntax, sie sind beide datenbankspezifische Erweiterungen. Es gibt gute Gründe dafür, dass MERGE nicht verwendet wurde , eine neue Syntax wurde nicht nur zum Spaß erstellt. (Die MySQL-Syntax hat auch Probleme, die bedeuten, dass sie nicht direkt übernommen wurde).

zB gegebenes Setup:

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

die MySQL-Abfrage:

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

wird:

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

Unterschiede:

  • Sie müssen den Spaltennamen (oder den eindeutigen Einschränkungsnamen) angeben, der für die Eindeutigkeitsprüfung verwendet werden soll. Das ist der ON CONFLICT (columnname) DO

  • Das Schlüsselwort SET muss verwendet werden, als ob dies eine normale UPDATE Anweisung wäre

Es hat auch einige nette Eigenschaften:

  • Sie können eine WHERE Klausel in Ihrem UPDATE (wodurch Sie ON CONFLICT UPDATE für bestimmte Werte effektiv in ON CONFLICT IGNORE können)

  • Die für die Einfügung vorgeschlagenen Werte sind als EXCLUDED , die dieselbe Struktur wie die EXCLUDED hat. Sie können die ursprünglichen Werte in der Tabelle mithilfe des Tabellennamens abrufen. In diesem Fall wird also EXCLUDED.c 10 (weil wir genau das versucht haben) und "table".c ist 3 weil das der aktuelle Wert in der Tabelle ist. Sie können beides oder beide in den SET Ausdrücken und der WHERE Klausel verwenden.

Hintergrundinformationen zu Upsert finden Sie unter UMSCHALTEN (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?


Persönlich habe ich eine "Regel" eingerichtet, die der Insert-Anweisung angehängt ist. Angenommen, Sie hatten eine "DNS" -Tabelle, die DNS-Treffer pro Kunde auf Zeitbasis aufzeichnet:

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

Sie wollten Zeilen mit aktualisierten Werten erneut einfügen oder sie erstellen, wenn sie nicht bereits vorhanden waren. Keyed auf die customer_id und die Uhrzeit. Etwas wie das:

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: Dies kann zum Scheitern führen, wenn gleichzeitige Einfügungen stattfinden, da dies zu unique_violation-Ausnahmen führt. Die nicht abgeschlossene Transaktion wird jedoch fortgesetzt und erfolgreich ausgeführt, und Sie müssen nur die beendete Transaktion wiederholen.

Wenn jedoch ständig Unmengen an Einfügungen auftreten, sollten Sie die Einfügeanweisungen um eine Tabellensperre erweitern: SHARE ROW EXCLUSIVE-Sperre verhindert Vorgänge, die Zeilen in der Zieltabelle einfügen, löschen oder aktualisieren können. Updates, die den eindeutigen Schlüssel nicht aktualisieren, sind jedoch sicher. Wenn Sie also keine Operation ausführen, verwenden Sie stattdessen advisory locks.

Außerdem verwendet der COPY-Befehl keine Regeln. Wenn Sie also mit COPY einfügen, müssen Sie stattdessen Trigger verwenden.


PostgreSQL seit Version 9.5 hat UPSERT Syntax mit ON CONFLICT- Klausel. mit folgender Syntax (ähnlich wie 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;

Das Durchsuchen der E-Mail-Gruppenarchive von postgresql nach "upsert" führt dazu, dass Sie im Handbuch ein Beispiel dafür finden, was Sie möglicherweise tun möchten :

Beispiel 38-2. Ausnahmen mit UPDATE / INSERT

In diesem Beispiel wird die Ausnahmebehandlung verwendet, um entweder UPDATE oder INSERT auszuführen:

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

Es gibt möglicherweise ein Beispiel dafür, wie dies in großen Mengen mit CTEs in 9.1 und höher in der Hacker-Mailing-Liste zu tun ist:

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;

Siehe die Antwort von a_horse_with_no_name für ein klareres Beispiel.


Zum Zusammenführen von kleinen Sets ist die obige Funktion in Ordnung. Wenn Sie jedoch große Datenmengen zusammenführen, rate ich Ihnen, sich http://mbk.projects.postgresql.org anzuschauen

Die aktuell beste Praxis, die mir bekannt ist, ist:

  1. Kopieren Sie neue / aktualisierte Daten in die temporäre Tabelle (sicher, oder Sie können INSERT, wenn die Kosten in Ordnung sind)
  2. Acquire Lock [optional] (Advisory ist Tabellensperren vorzuziehen, IMO)
  3. Verschmelzen. (Der lustige Teil)

Bearbeiten: Dies funktioniert nicht wie erwartet. Im Gegensatz zur akzeptierten Antwort führt dies zu eindeutigen Schlüsselverletzungen, wenn zwei Prozesse wiederholt gleichzeitig upsert_foo aufrufen.

Eureka! Ich habe einen Weg gefunden, dies in einer Abfrage zu tun: UPDATE ... RETURNING , um zu testen, ob Zeilen betroffen sind:

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;

Das UPDATE muss in einem separaten Verfahren durchgeführt werden, da dies leider ein Syntaxfehler ist:

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

Jetzt funktioniert es wie gewünscht:

SELECT upsert_foo(1, 'hi');
SELECT upsert_foo(1, 'bye');
SELECT upsert_foo(3, 'hi');
SELECT upsert_foo(3, 'bye');

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




sql-merge