postgresql - update - upsert postgres 10




Verwenden Sie mehrere conflict_target in der ON CONFLICT-Klausel (6)

Ich habe zwei Spalten in der Tabelle col1 , col2 , beide sind eindeutig indiziert (col1 ist eindeutig und col2 auch).

Ich muss in diese Tabelle einfügen, die ON CONFLICT Syntax verwenden und andere Spalten aktualisieren, kann jedoch nicht beide Spalten in der conflict_target Klausel verwenden.

Es klappt:

INSERT INTO table
...
ON CONFLICT ( col1 ) 
DO UPDATE 
SET 
-- update needed columns here

Aber wie macht man das für mehrere Spalten, etwa so:

...
ON CONFLICT ( col1, col2 )
DO UPDATE 
SET 
....

Eine Beispieltabelle und Daten

CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
   CONSTRAINT col2_unique UNIQUE (col2)
);

INSERT INTO dupes values(1,1,'a'),(2,2,'b');

Das Problem reproduzieren

INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2

Nennen wir das Q1. Das Ergebnis ist

ERROR:  duplicate key value violates unique constraint "col2_unique"
DETAIL:  Key (col2)=(2) already exists.

Was die documentation sagt

conflict_target kann eindeutige Indexinferenzen durchführen. Bei der Durchführung von Inferenzen besteht es aus einer oder mehreren index_column_name-Spalten und / oder index_expression-Ausdrücken und einem optionalen index_predicate. Alle eindeutigen Indizes table_name, die unabhängig von der Reihenfolge genau die von conflict_target angegebenen Spalten / Ausdrücke enthalten, werden als Arbiter-Indizes abgeleitet (ausgewählt). Wenn ein index_predicate angegeben wird, muss es als weitere Voraussetzung für den Rückschluss Arbiter-Indizes erfüllen.

Dies erweckt den Eindruck, dass die folgende Abfrage funktionieren sollte, jedoch nicht, da tatsächlich ein zusammen eindeutiger Index für Spalte 1 und Spalte 2 erforderlich wäre. Ein solcher Index würde jedoch nicht garantieren, dass col1 und col2 individuell eindeutig sind, was eine der Anforderungen des OP ist.

INSERT INTO dupes values(3,2,'c') 
ON CONFLICT (col1,col2) DO UPDATE SET col3 = 'c', col2 = 2

Nennen wir diese Abfrage Q2 (dies schlägt mit einem Syntaxfehler fehl)

Warum?

Postgresql verhält sich so, weil nicht genau definiert ist, was passieren soll, wenn ein Konflikt in der zweiten Spalte auftritt. Es gibt viele Möglichkeiten. Soll beispielsweise in der obigen Q1-Abfrage postgresql col1 aktualisieren, wenn auf col2 ein Konflikt col2 ? Aber was ist, wenn dies zu einem weiteren Konflikt auf col1 ? Wie soll postgresql damit umgehen?

Eine Lösung

Eine Lösung besteht darin, ON CONFLICT mit altmodischem UPSERT zu kombinieren.

CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;
        IF found THEN
            RETURN;
        END IF;

        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently, or key2
        -- already exists in col2,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            BEGIN
                INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- Do nothing, and loop to try the UPDATE again.
            END;
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

Sie müssten die Logik dieser gespeicherten Funktion so ändern, dass die Spalten genau so aktualisiert werden, wie Sie es möchten. Rufe es so auf

SELECT merge_db(3,2,'c');
SELECT merge_db(1,2,'d');

  1. Erstellen Sie eine Einschränkung (z. B. einen Fremdindex).

ODER UND

  1. Sehen Sie sich die vorhandenen Einschränkungen an (\ d in psq).
  2. Verwenden Sie ON CONSTRAINT (constraint_name) in der INSERT-Klausel.

Irgendwie hacky, aber ich habe das gelöst, indem ich die beiden Werte von col1 und col2 in eine neue Spalte, col3 (eine Art Index der beiden), verkettet und damit verglichen habe. Dies funktioniert nur, wenn Sie es für BEIDE Spalten 1 und 2 benötigen.

INSERT INTO table
...
ON CONFLICT ( col3 ) 
DO UPDATE 
SET 
-- update needed columns here

Wobei col3 = die Verkettung der Werte aus col1 und col2.


ON CONFLICT ist eine sehr ungeschickte Lösung

UPDATE dupes SET key1=$1, key2=$2 where key3=$3    
if rowcount > 0    
  INSERT dupes (key1, key2, key3) values ($1,$2,$3);

Funktioniert mit Oracle, Postgres und allen anderen Datenbanken


Vlad hatte die richtige Idee.

Zuerst müssen Sie eine tabellenspezifische Einschränkung für die Spalten col1, col2 Anschließend können Sie Folgendes tun:

INSERT INTO dupes values(3,2,'c') 
ON CONFLICT ON CONSTRAINT dupes_pkey 
DO UPDATE SET col3 = 'c', col2 = 2

Wenn Sie Postgres 9.5 verwenden, können Sie das EXCLUDED-Leerzeichen verwenden.

Beispiel aus Was ist neu in PostgreSQL 9.5 :

INSERT INTO user_logins (username, logins)
VALUES ('Naomi',1),('James',1)
ON CONFLICT (username)
DO UPDATE SET logins = user_logins.logins + EXCLUDED.logins;




postgresql-9.5