Forums PostgreSQL.fr

unbewusst

Membre

delete in select

c'est juste un petit test que je viens de faire.
j'ai une table cities qui a des doublons, en fait toutes les entrées sont en double suite à une fausse manip de ma part...

bon si je fais un :

select cities.name from cities where cities.name like 'Merr%' limit 1;

j'ai un résultat unique (limit 1) :

  name  
--------
 Merris
(1 row)

normal, MAIS ce qui ne "me" semble pas normal c'est que si je fais un delete in avec le précédent select, ça m'endelete 2 :

delete from cities where cities.name in (select cities.name from cities where cities.name like 'San%' limit 1);
DELETE 2

j'sais bien, c'est forcément SQL qui a raison...

youssef

Membre

Re : delete in select

Limit c'est juste pour limiter le résultat qui sera affiché,

pour supprimer les doublons tu peux utiliser la colonne ctid

select ctid, * from cities where cities.name like 'Merr%' ; 

puis  faire un

delete from cities where ctid = .... 

une des deux valeurs récupérée dans la première requête.

Dernière modification par youssef (09/10/2012 09:47:59)

unbewusst

Membre

Re : delete in select

Ah d'accord, merci beaucoup, je ne savais pas du tout que le limit n'était que pour la présentation du résultat.
Dans ma base de test, il n'y a pas de cityid, c'est créé tout seul ?
Je veux dire c'est interne à postgres ?

unbewusst

Membre

Re : delete in select

Je me gouratte, c'est bien "ctid" et non "cityid" comme je l'ai écrit précédemment.
ça retourne d'ailleurs un couple de valeurs :

yt_tests=# select ctid, * from cities where cities.name like 'Merr%' ;
 ctid  |  name  | location 
-------+--------+----------
 (0,2) | Merris | (0.5,49)
 (0,4) | Merris | (0.5,49)
(2 rows)

je dois regarder la doc pour savoir comment faire un delete sur un des couples ctid retournés.

unbewusst

Membre

Re : delete in select

Bon, j'ai trouvé une solution élégante sur le net http://www.postgresonline.com/journal/a … Table.html "DELETING DUPLICATE RECORDS IN A TABLE" :

yt_tests=# select ctid, * from cities where cities.name like 'Merr%' ;
 ctid  |  name  | location 
-------+--------+----------
 (0,2) | Merris | (0.5,49)
 (0,4) | Merris | (0.5,49)
(2 rows)

yt_tests=# select max(ctid) from cities where cities.name like 'Merr%' ;
  max  
-------
 (0,4)
(1 row)

yt_tests=# delete from cities where ctid not in (select max(ctid) from cities where cities.name like 'Merr%');
DELETE 1

yt_tests=# select ctid, * from cities where cities.name like 'Merr%' ;
 ctid  |  name  | location 
-------+--------+----------
 (0,4) | Merris | (0.5,49)
(1 row)

Toute l'astuce est de prendre le max de ctid...

youssef

Membre

Re : delete in select

Exactement,

ctid  est un identifiant unique pour chaque ligne, et ça permet de faire la différence entre les lignes.

gleu

Administrateur

Re : delete in select

Oulala, attention en manipulant les CTID. Au premier UPDATE, ils changent. Donc vous pourriez très bien mettre à jour ou supprimer une ligne qui ne correspond pas à celle que vous voulez.

Dans le cas en question, ça peut être un moyen mais mieux vaut regarder la définition de la table. Si elle contient une contrainte unique ou une clé primaire, il est clairement préférable de passer par ça.

---

Guillaume.

unbewusst

Membre

Re : delete in select

Merci bien pour cette précision, je vais donc devoir lire la doc sur ctid.
M'enfin là c'était juste pour un dépannage suite à fausse manip.
Donc, si je comprends bien il vaut mieux ajouter un rowid ou quelque chose dans le genre.
Justement, je profite de ce fil pour avoir des tuyaux concernant les clés sur une base.
En fait ce que je cherche à faire c'est à synchroniser deux bases sur deux ordis différents.
Je trouve que ce n'est pas facile.
Par exemple le rowid d'une base n'est pas vraiment utile pour la synchro.
Ce que j'ai imaginé :
- créer, à l'insertion d'une nouvelle entrée, une clé qui serait le md5 de la concatenation de tous les champs, appelons-la "cle_insert" , elle sera la clé primaire pour repérer les objets ;
- enregistrer le datetime à la création soit la colonne "dt_insert" ;
- créer à chaque modif d'une ligne une clé, au départ de valeur identique à "cle_insert" et qui serait elle le md5 de la concatenation de la ligne modifiée, appellons-la "cle_modif", bien sûr "cle_insert" ne change jamais.
- enregistrement le datetime à la modif soit la colonne "dt_modif" ;

donc avec ces deux cles je peux repérer très facilement :
- les insertions, "cle_insert" existe d'un côte mais pas de l'autre ;
- les modifications simples càd celles où un objet n'a été changé que d'un côté, les dt_modif seront différents, les cle_modif aussi et ce pour la meme cle -invariable- cle_insert ;
- vient le problème plus délicat où une ligne de même cle_insert a été changée des deux côtés "en même temps" du point de vue de la synchronisation. car la synchro ne pourra se faire que lorsque les deux ordis seront connectés au net, ce qui n'est pas toujours le cas, l'un des deux étant un portable.
dans ce cas, je ne vois pas trop comment faire mis à part avoir une sorte de cache enregistrant l'état de la base à chaque synchro.
aussi je ne vois pas trop comment régler le problème des suppressions de ligne sinon qu'il faut enregistrer qu'une ligne a été supprimée pour que ce soit su au moment de la synchro.

bon tout cela est un peu en vrac...

gleu

Administrateur

Re : delete in select

Ça m'a l'air bien compliqué mais ce que vous essayez de faire est intrinsèquement compliqué. Pour infos, le type uuid peut vous aider à avoir un identifiant "universel".

---

Guillaume.

unbewusst

Membre

Re : delete in select

oui, c'est ce qu'il m'a été répondu par Frédéric BROUARD sur fr.comp.applications.sgbd :
Message-ID: <5075abb8$0$1720$426a74cc@news.free.fr>
uuid a deux algo possible md5 et sha1.
clairement l'uuid/md5 par ex est tout simplement le md5 tronçonné.
voire : http://phpdevblog.free.fr/?p=12
donc, du seul point de vue de mon utilisation c'est kifkif.
je concatène mon objet en string et j'en prend le sha1, plus long que le md5 me semble t'il pour la même chaine.
cerise sur le gateau les algos php et ruby donnent le même résultat, je viens juste de tester "à la main".
yapuka )))

non, ce n'est pas évident de synchroniser deux bases : trouver les insertions, c'est fastoche, trouver les modifs aussi MAIS à condition qu'il n'y en ait que sur un seul ordi, sinon ce n'est pas possible.
DONC, ça implique une discipline : n'utiliser qu'un ordi à la fois et synchroniser dès que la connection est possible...
pour les suppressions, c'est autre chose, il faut que mon code enregistre dans une base à part les suppressions sinon à la synchro suivante le code recréerait de l'autre côté...