Postgres : INSERT si n'existe pas déjà

Question

J'utilise Python pour écrire dans une base de données Postgres :

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

Mais comme certaines de mes lignes sont identiques, j'obtiens l'erreur suivante :

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

Comment puis-je écrire une instruction SQL "INSERT sauf si cette ligne existe déjà" ?

J'ai vu des déclarations complexes comme celle-ci recommandées :

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

Mais d'abord, est-ce que c'est trop pour ce dont j'ai besoin, et ensuite, comment puis-je exécuter l'une d'entre elles comme une simple chaîne de caractères ?

Answer 1

Comment puis-je écrire une instruction SQL "INSERT sauf si cette ligne existe déjà" ?

Il existe une bonne façon d'effectuer des INSERTS conditionnels dans PostgreSQL :

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

CAVEAT Cette approche n'est pas fiable à 100% pour concurrentes les opérations d'écriture, cependant. Il existe une très petite condition de course entre le SELECT dans le NOT EXISTS anti-semi-join et le INSERT lui-même. Il s'agit de puede échouer dans de telles conditions.

Answer 2

Une approche consisterait à créer une table sans contrainte (pas d'index unique) pour y insérer toutes vos données et à effectuer une sélection distincte de cette table pour insérer vos données dans votre table cent.

Donc le haut niveau serait. Je suppose que les trois colonnes sont distinctes dans mon exemple, donc pour l'étape 3, changez la jointure NOT EXITS pour ne joindre que les colonnes uniques de la table cent.

1. Créer une table temporaire. Voir la documentation aquí .

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

2. INSERER les données dans la table temporaire.

   INSERT INTO temp_data(name, name_slug, status); 

3. Ajoutez tous les index à la table temporaire.

4. Faire l'insertion de la table principale.

   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 3

Malheureusement, PostgreSQL ne supporte ni MERGE ni ON DUPLICATE KEY UPDATE Vous devrez donc le faire en deux déclarations :

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

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

Vous pouvez l'intégrer dans une fonction :

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

et l'appeler simplement :

SELECT  fn_upd_invoices('12345', 'TRUE')

Answer 4

Vous pouvez faire usage de VALUES - disponible dans Postgres :

INSERT INTO person (name)
    SELECT name FROM person
    UNION 
    VALUES ('Bob')
    EXCEPT
    SELECT name FROM person;

Answer 5

Si vous souhaitez simplement insérer ou ne pas insérer (et ne pas mettre à jour par ailleurs), vous pouvez procéder comme suit (en reprenant l'exemple de la facture) :

INSERT INTO invoices (invoiceid, billed) SELECT '12345', 'TRUE'
WHERE NOT EXISTS (SELECT 1 FROM invoices WHERE invoiceid = '12345')