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

Question

Certains serveurs SQL ont une fonction où INSERT est ignoré s'il viole une contrainte de clé primaire/unique. Par exemple, MySQL a INSERT IGNORE .

Quelle est la meilleure façon d'émuler INSERT IGNORE y ON DUPLICATE KEY UPDATE avec PostgreSQL ?

Answer 1

Edit : au cas où vous auriez manqué réponse de warren PG9.5 est maintenant doté de cette fonctionnalité en mode natif ; il est temps de faire une mise à jour !

---

En s'appuyant sur la réponse de Bill Karwin, expliquer à quoi ressemblerait une approche basée sur des règles (transfert à partir d'un autre schéma dans la même BD, 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";

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

Answer 2

Essayez de faire un UPDATE. Si aucune ligne n'est modifiée, cela signifie qu'elle n'existait pas, alors faites une insertion. Évidemment, vous faites cela à l'intérieur d'une transaction.

Vous pouvez bien sûr intégrer cette opération dans une fonction si vous ne souhaitez pas ajouter de code supplémentaire du côté du client. Vous avez également besoin d'une boucle pour les très rares conditions de course dans ce raisonnement.

Il y a un exemple de ceci dans la documentation : http://www.postgresql.org/docs/9.3/static/plpgsql-control-structures.html l'exemple 40-2 juste en bas.

C'est généralement le moyen le plus simple. Vous pouvez faire de la magie avec des règles, mais ce sera probablement beaucoup plus désordonné. Je recommanderais volontiers l'approche "envelopper dans la fonction".

Cela fonctionne pour les valeurs à une seule ligne ou à quelques lignes. Si vous traitez de grandes quantités de lignes, par exemple à partir d'une sous-requête, il est préférable de la diviser en deux requêtes, une pour INSERT et une pour UPDATE (avec une jointure/sous-sélection appropriée bien sûr - pas besoin d'écrire deux fois votre filtre principal).

Answer 3

Pour obtenir le insérer ignorer logique vous pouvez faire quelque chose comme ci-dessous. J'ai trouvé que le fait d'insérer simplement à partir d'une instruction de sélection de valeurs littérales fonctionnait le mieux, puis vous pouvez masquer les clés dupliquées avec une clause NOT EXISTS. Pour obtenir la logique de mise à jour des doublons, je pense 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)
)

Answer 4

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

Answer 5

Il semble que PostgreSQL supporte un objet de schéma appelé un règle .

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

Vous pourriez créer une règle ON INSERT pour une table donnée, en lui faisant faire NOTHING si une rangée existe avec la valeur de la clé primaire donnée, ou sinon en faisant un UPDATE au lieu de la INSERT si une ligne existe avec la valeur de la clé primaire donnée.

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