Postgres: INSERT wenn nicht bereits vorhanden

Question

Ich verwende Python, um in eine Postgres-Datenbank zu schreiben:

sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES ("
sql_string += hundred + ", '" + hundred_slug + "', " + status + ");"
cursor.execute(sql_string)

Da aber einige meiner Zeilen identisch sind, erhalte ich folgende Fehlermeldung:

psycopg2.IntegrityError: duplicate key value  
  violates unique constraint "hundred_pkey"

Wie kann ich eine SQL-Anweisung "INSERT unless this row already exists" schreiben?

Ich habe gesehen, dass komplexe Aussagen wie diese empfohlen werden:

IF EXISTS (SELECT * FROM invoices WHERE invoiceid = '12345')
UPDATE invoices SET billed = 'TRUE' WHERE invoiceid = '12345'
ELSE
INSERT INTO invoices (invoiceid, billed) VALUES ('12345', 'TRUE')
END IF

Aber erstens ist dies overkill für was ich brauche, und zweitens, wie kann ich eine von denen als eine einfache Zeichenfolge ausführen?

Answer 1

Postgres 9.5 (freigegeben seit 2016-01-07) bietet eine "upsert" Befehl, auch bekannt als ON CONFLICT-Klausel zu INSERT :

INSERT ... ON CONFLICT DO NOTHING/UPDATE

Damit werden viele der subtilen Probleme gelöst, die bei der Verwendung von gleichzeitigen Operationen auftreten können und die in einigen anderen Antworten vorgeschlagen werden.

Answer 2

Wie kann ich eine SQL-Anweisung "INSERT unless this row already exists" schreiben?

Es gibt eine gute Möglichkeit, bedingte INSERT in PostgreSQL durchzuführen:

INSERT INTO example_table
    (id, name)
SELECT 1, 'John'
WHERE
    NOT EXISTS (
        SELECT id FROM example_table WHERE id = 1
    );

CAVEAT Dieser Ansatz ist nicht 100% zuverlässig für gleichzeitig Schreiboperationen. Es gibt eine winzige Race Condition zwischen dem SELECT dans le NOT EXISTS Antisemiten-Verband und die INSERT selbst. Sie puede unter solchen Bedingungen versagen.

Answer 3

Ein Ansatz wäre, eine nicht eingeschränkte (keine eindeutigen Indizes) Tabelle zu erstellen, in die alle Daten eingefügt werden, und ein select distinct von dieser Tabelle auszuführen, um die Einfügung in die hundert Tabellen vorzunehmen.

Ein hohes Niveau wäre also. Ich nehme an, dass alle drei Spalten in meinem Beispiel eindeutig sind, also ändern Sie in Schritt 3 die NOT EXITS-Verknüpfung, um nur die eindeutigen Spalten in der Tabelle hundred zu verknüpfen.

1. Erstellen Sie eine temporäre Tabelle. Siehe Doku aquí .

   CREATE TEMPORARY TABLE temp_data(name, name_slug, status);

2. INSERT Daten in temporäre Tabelle.

   INSERT INTO temp_data(name, name_slug, status); 

3. Fügen Sie alle Indizes zur temporären Tabelle hinzu.

4. Haupttabelle einfügen.

   INSERT INTO hundred(name, name_slug, status) 
       SELECT DISTINCT name, name_slug, status
       FROM hundred
       WHERE NOT EXISTS (
           SELECT 'X' 
           FROM temp_data
           WHERE 
               temp_data.name          = hundred.name
               AND temp_data.name_slug = hundred.name_slug
               AND temp_data.status    = status
       );

Answer 4

Das ist genau das Problem, das ich habe, und meine Version ist 9.5

Und ich löse es mit der folgenden SQL-Abfrage.

INSERT INTO example_table (id, name)
SELECT 1 AS id, 'John' AS name FROM example_table
WHERE NOT EXISTS(
            SELECT id FROM example_table WHERE id = 1
    )
LIMIT 1;

Ich hoffe, das hilft jemandem, der das gleiche Problem mit Version >= 9.5 hat.

Vielen Dank für die Lektüre.

Answer 5

Leider, PostgreSQL unterstützt weder MERGE noch ON DUPLICATE KEY UPDATE also müssen Sie es in zwei Erklärungen tun:

UPDATE  invoices
SET     billed = 'TRUE'
WHERE   invoices = '12345'

INSERT
INTO    invoices (invoiceid, billed)
SELECT  '12345', 'TRUE'
WHERE   '12345' NOT IN
        (
        SELECT  invoiceid
        FROM    invoices
        )

Sie können es in eine Funktion verpacken:

CREATE OR REPLACE FUNCTION fn_upd_invoices(id VARCHAR(32), billed VARCHAR(32))
RETURNS VOID
AS
$$
        UPDATE  invoices
        SET     billed = $2
        WHERE   invoices = $1;

        INSERT
        INTO    invoices (invoiceid, billed)
        SELECT  $1, $2
        WHERE   $1 NOT IN
                (
                SELECT  invoiceid
                FROM    invoices
                );
$$
LANGUAGE 'sql';

und rufen Sie es einfach an:

SELECT  fn_upd_invoices('12345', 'TRUE')