Forums PostgreSQL.fr

Selzig

Membre

ON DUPLICATE KEY UPDATE mySQL-->postgreSQL

Bojour,

j'ai beaucoup de mal à comprendre le remplacement de l'inexistant 'ON DUPLICATE KEY UPDATE' de mySQL.

Au départ la requête est celle-ci : 'INSERT INTO myTABLE (xxID, siID, tvTABLE, tvVERSION, xxUSER, xxPOSTE, xxSTAMP) VALUES (:paxxID, :pasiID, :patvTABLE, :patvVERSION, :paxxUSER, :paxxPOSTE, :paxxSTAMP) ON DUPLICATE KEY UPDATE tvVERSION = :patvVERSION, xxUSER = :paxxUSER, xxPOSTE = :paxxPOSTE, xxSTAMP = :paxxSTAMP;

J'ai lu quelque chose qui me semblait simple mais dont je n'arrive pas à faire fonctionner à partir de mon langage (Lazarus) :

BEGIN;
  EXECUTE '';
        EXCEPTION when unique_violation THEN
                EXECUTE 'la requête UPDATE';
END;

Ce qui ferait

BEGIN;
  EXECUTE 'INSERT INTO myTABLE (xxID, siID, tvTABLE, tvVERSION, xxUSER, xxPOSTE, xxSTAMP) VALUES (:paxxID, :pasiID, :patvTABLE, :patvVERSION, :paxxUSER, :paxxPOSTE, :paxxSTAMP);';
EXCEPTION when unique_violation THEN
  EXECUTE 'UPDATE myTABLE SET tvVERSION = :patvVERSION, xxUSER = :paxxUSER, 'xxPOSTE = :paxxPOSTE, xxSTAMP = :paxxSTAMP WHERE (siID = :pasiID) AND (tvTABLE = :patvTABLE);';
END;

[Dans myTable CONSTRAINT mt_TableVersion UNIQUE(tvTABLE, tvVERSION)]

J'espère que la première syntaxe est exacte. Je n'ai pas pu vérifier car dans la seconde, je me heurte à un premier problème : le fait que les 2 requêtes (INS et UPD) soient quotées génère une erreur au niveau de l'injection... Les paramètres :paxxx sont cachés.

Un avis sur la question ? Une autre approche ?...

...sachant que d'une manière je devrais remplacer des ON DUPLICATE KEY UPDATE et que dans ce cas particulier, j'ai besoin d'un indicateur qui me permette de déterminer si la table a été modifiée depuis ma dernière consultation. Peut-être en postgreSQL, existe-t-il, pour chaque table,  un indicateur qui est modifié à chaque DEL, INS, UPD de la dite table...

Sachant également que ce n'est pas un problème crucial : Actuellement avec Lazarus, sur la base postgreSQL,un dataset effectue un SELECT  : si "isEmpty" alors  le Dataset effectue un INSERT sinon un UPDATE... mais je préférerais progresser un peu en postgreSQL...

Merci. Gilles

Dernière modification par Selzig (02/09/2011 15:47:47)

gleu

Administrateur

Re : ON DUPLICATE KEY UPDATE mySQL-->postgreSQL

Vous n'avez pas dû comprendre que le code entre BEGIN et END doit se trouver dans une procédure stockée codée en PL/pgsql. Ce n'est pas une requête à exécuter directement. La requête à exécuter est un appel à cette procédure stockée.

---

Guillaume.

Selzig

Membre

Re : ON DUPLICATE KEY UPDATE mySQL-->postgreSQL

Bonjour,

Euhhhh... Avec le recul puis le réveil... normalement pas de confusion... mais à la fin, en désespoir de cause, on essaye tout et n'importe quoi. J'ai essayé initialement avec une procédure stockée de manière classique avec un appel de la forme EXECUTE PROCEDURE xxxx() mais je n'arrive pas à y gérer les injections (dans la procédure d'appel) qui est ma méthode de travail habituelle.

Donc, après beaucoup d'essais infructueux, je me suis demandé s'il existait une méthode SIMPLE bâtie comme une requête avec injection sur le mode d'une "espèce" de procédure stockée puisqu'en pgSQL, il semble que cela soit le seul chemin possible... et je dois reconnaître que pour faire un simple ON DUPLICATE... UPDATE, cela me paraît d'une complexité invraisemblable. Aussi invraisemblable que la requête proposée, certainement...  Mais entre invraisemblances...

J'apprécie pour plein de raisons pgSQL mais enfin... je dois reconnaître également que même si un spécialiste me vantera toutes les qualités des procédures stockées, être obligé d'en faire une parce qu'on ne peut pas faire un ON DUPLICATE... UPDATE en requête directe, c'est très perfectible "pour le meilleur SGDBR du monde"... comme le fait de pouvoir créer des colonnes à l'endroit où l'on veut dans une table.

Donc je continue à chercher une solution même si le problème est réglé par mon langage de développement en 2 requêtes (1 SELECT puis selon le cas un INSERT ou 1 UPDATE),  non pas seulement par curiosité mais parce que cela me semble un moyen significatif de me permettre d'évaluer plus finement pgSQL. Je retourne à ma doc.

Merci pour votre aide.
Cordialement. Gilles

gleu

Administrateur

Re : ON DUPLICATE KEY UPDATE mySQL-->postgreSQL

En gros, voilà l'idée sur ce code. Disons que j'ai une table avec cette définition :

CREATE TABLE t1 (c1 integer primary key, c2 text);

Je crée une fonction qui se chargera de faire l'insertion ou la mise à jour, suivant que la ligne existe ou pas.

create or replace function insert_into_t1(v1 integer, v2 text)
returns text   
language plpgsql
as $$
begin
insert into t1 values (v1, v2);
return 'i';
EXCEPTION when unique_violation THEN
update t1 set c2=v2 where c1=v1;
return 'u';
end;
$$;

Maintenant, il ne me reste plus qu'à faire appel à la fonction pour insérer ou mettre à jour des lignes:

toto=# select insert_into_t1(1, 'un');
 insert_into_t1 
----------------
 i                                                      --> donc une insertion
(1 row)

toto=# select insert_into_t1(2, 'deux');
 insert_into_t1 
----------------
 i                                                      --> donc une insertion
(1 row)

toto=# select insert_into_t1(1, 'UN');
 insert_into_t1 
----------------
 i                                                      --> donc une mise à jour
(1 row)

trs=# select * from t1;
 c1 |  c2  
----+------
  2 | deux
  1 | UN
(2 rows)

---

Guillaume.

jpargudo

Administrateur

Re : ON DUPLICATE KEY UPDATE mySQL-->postgreSQL

Selzig,

Pas la peine de chercher dans la doc de PostgreSQL un équivalent d'upsert. Ça n'existe pas encore, c'est dans la TODO de PostgreSQL:

voir http://wiki.postgresql.org/wiki/Todo
sous "SQL Commands":
Incomplete itemAdd SQL-standard MERGE/REPLACE/UPSERT command

    MERGE is typically used to merge two tables.
    REPLACE or UPSERT command does UPDATE, or on failure, INSERT.
    See SQL MERGE for notes on the implementation details.

SQL Merge: http://wiki.postgresql.org/wiki/SQL_MERGE

Je n'ai pas encore pris le temps de savoir où ça en est...

La solution de Guillaume fonctionnera, bien qu'elle impose le recours à une fonction PL, et donc, probablement une perte de performances, elle devrait correspondre à votre besoin :-/

---

Jean-Paul Argudo
http://www.postgresql.fr - http://www.postgresql.eu
http://www.dalibo.com - http://www.dalibo.org/

jpargudo

Administrateur

Re : ON DUPLICATE KEY UPDATE mySQL-->postgreSQL

OOps,

Je suis allé trop vite, j'ai cru lire un besoin d'UPSERT alors que c'est ON DUPLICATE UPDATE... Rien à voir.

Désolé

---

Jean-Paul Argudo
http://www.postgresql.fr - http://www.postgresql.eu
http://www.dalibo.com - http://www.dalibo.org/

Selzig

Membre

Re : ON DUPLICATE KEY UPDATE mySQL-->postgreSQL

Bonjour,

Merci à tous deux.

J'ai adapté (et adopté) la solution proposée par Guillaume. Cela fonctionne parfaitement.
Cordialement. Gilles

jpargudo

Administrateur

Re : ON DUPLICATE KEY UPDATE mySQL-->postgreSQL

Selzig,

Si tu peux faire quelques tests de performance et nous dire ce que ça donne, ça serait un super retour pour le forum.

Par avance, merci.

Encore désolé pour ma non-réponse d'hier..

---

Jean-Paul Argudo
http://www.postgresql.fr - http://www.postgresql.eu
http://www.dalibo.com - http://www.dalibo.org/

Selzig

Membre

Re : ON DUPLICATE KEY UPDATE mySQL-->postgreSQL

Bonjour,

Désolé pour le retard de ma réponse... J'avais d'autres priorités et surtout je n'imagine pas les tests à réaliser. Que faudrait-il tester exactement ? Dans la mesure où il ne semble pas y avoir d'autre alternative en postgreSQL au ON DUPLICATE KEY de mySQL, il ne semble pas possible de faire une comparaison entre 2 solutions postgreSQL. Comparer sur 2 bases "identiques", l'une en postgreSQL, l'autre en mySQL serait pour le moins "subjectif". L'environnement rendrait le test non significatif : tout dépend des paramétrages des serveurs, des moteurs utilisés...

Cordialement. Gilles