comment émuler "insert ignore" et "on duplicate key update" (fusion sql) avec postgresql?

avec PostgreSQL 9.5, c'est maintenant fonctionnalité native (comme MySQL a eu depuis plusieurs années):

insérer ... ON CONFLICT DO NOTHING / UPDATE ("UPSERT")

9.5 apporte un soutien pour les opérations "UPSERT". INSERT est étendu pour accepter une clause on CONFLICT DO UPDATE/IGNORE. Cette clause spécifie une alternative mesures à prendre en cas de violation possible en double.

...

autre exemple de nouvelle syntaxe:

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

Edit: dans le cas où vous avez manqué warren réponse , PG9.5 a maintenant ce mode natif; le temps de mettre à niveau!

en S'appuyant sur la réponse de Bill Karwin, pour préciser à quoi ressemblerait une approche basée sur des règles (transfert à partir d'un autre schéma dans le même DB, et avec une clé primaire à plusieurs colonnes):

CREATE RULE "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table"
  WHERE EXISTS(SELECT 1 FROM my_table 
                WHERE (pk_col_1, pk_col_2)=(NEW.pk_col_1, NEW.pk_col_2))
  DO INSTEAD NOTHING;
INSERT INTO my_table SELECT * FROM another_schema.my_table WHERE some_cond;
DROP RULE "my_table_on_duplicate_ignore" ON "my_table";

Note: La règle s'applique à toutes les opérations INSERT jusqu'à ce que la règle soit abandonnée, donc pas tout à fait ad hoc.

pour obtenir la logique insérez ignorer vous pouvez faire quelque chose comme ci-dessous. J'ai trouvé simplement insérant à partir d'une déclaration select de valeurs littérales a fonctionné le mieux, alors vous pouvez masquer les clés dupliquées avec une clause N'existe pas. Pour obtenir la mise à jour sur la logique dupliquée, je soupçonne qu'une boucle pl/pgsql serait nécessaire.

INSERT INTO manager.vin_manufacturer
(SELECT * FROM( VALUES
  ('935',' Citroën Brazil','Citroën'),
  ('ABC', 'Toyota', 'Toyota'),
  ('ZOM',' OM','OM')
  ) as tmp (vin_manufacturer_id, manufacturer_desc, make_desc)
  WHERE NOT EXISTS (
    --ignore anything that has already been inserted
    SELECT 1 FROM manager.vin_manufacturer m where m.vin_manufacturer_id = tmp.vin_manufacturer_id)
)

INSERT INTO mytable(col1,col2) 
    SELECT 'val1','val2' 
    WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE col1='val1')

pour ceux d'entre vous qui ont des Postgres 9.5 ou plus, le nouveau sur le conflit ne font rien la syntaxe devrait fonctionner:

INSERT INTO target_table (field_one, field_two, field_three ) 
SELECT field_one, field_two, field_three
FROM source_table
ON CONFLICT (field_one) DO NOTHING;

pour ceux d'entre nous qui ont une version antérieure, ce droit join fonctionnera à la place:

INSERT INTO target_table (field_one, field_two, field_three )
SELECT source_table.field_one, source_table.field_two, source_table.field_three
FROM source_table 
LEFT JOIN target_table ON source_table.field_one = target_table.field_one
WHERE target_table.field_one IS NULL;

ressemble à PostgreSQL supporte un objet de schéma appelé règle .

http://www.postgresql.org/docs/current/static/rules-update.html

vous pouvez créer une règle ON INSERT pour une table donnée, en la faisant faire NOTHING si une rangée existe avec la valeur clé primaire donnée, ou bien en la faisant faire un UPDATE au lieu du INSERT si une rangée existe avec la valeur clé primaire donnée.

Je n'ai pas essayé moi-même, donc je ne peux pas parler par expérience ou donner un exemple.

cette solution évite d'utiliser des règles:

BEGIN
   INSERT INTO tableA (unique_column,c2,c3) VALUES (1,2,3);
EXCEPTION 
   WHEN unique_violation THEN
     UPDATE tableA SET c2 = 2, c3 = 3 WHERE unique_column = 1;
END;

mais il présente un inconvénient de performance (voir PostgreSQL.org ):

un bloc contenant une clause D'EXCEPTION est beaucoup plus cher pour entrer et sortir qu'un bloc sans un. Par conséquent, ne pas utiliser EXCEPTION sans nécessité.

comme @hansari l'a mentionné dans son commentaire. en insérant dans un Postgres tables, l'on conflict (..) do nothing est le meilleur code à utiliser pour ne pas insérer de données en double.:

query = "INSERT INTO db_table_name(column_name)
         VALUES(%s) ON CONFLICT (column_name) DO NOTHING;"

la ligne de code "on CONFLICT" permettra à la déclaration insert d'insérer encore des lignes de données. Le code de requête et de valeurs est un exemple de date insérée à partir D'un Excel dans une table de Postgres db. J'ai ajouté des contraintes à un tableau postgres que j'utilise pour m'assurer que le champ ID est unique. Au lieu d'exécuter une suppression sur les lignes de données qui est la même, j'ajoute une ligne de code sql qui renumérote la colonne ID à partir de 1. Exemple:

q = 'ALTER id_column serial RESTART WITH 1'

si mes données ont un champ ID, Je ne l'utilise pas comme ID primaire/ID série, je crée une colonne ID et je la mets en série. J'espère que cette information sera utile à tous. *Je n'ai pas de diplôme d'études collégiales en développement/codage de logiciels. Tout ce que je sais en codage, Je l'étudie moi-même.

en vrac, vous pouvez toujours supprimer la ligne avant de l'insérer. Une suppression d'une rangée qui n'existe pas ne cause pas d'erreur, donc il a sauté en toute sécurité.

pour les scripts d'importation de données, pour remplacer "IF NOT EXISTS", d'une certaine manière, il y a une formulation légèrement maladroite qui fonctionne néanmoins:

DO
$do$
BEGIN
PERFORM id
FROM whatever_table;

IF NOT FOUND THEN
-- INSERT stuff
END IF;
END
$do$;