Forums PostgreSQL.fr

carole_31

Membre

Faire "copy from csv" qui effectue un "insert or ignore + update"

Bonjour,

J'ai besoin d'alimenter une des tables de ma bdd via un fichier csv qui est alimenté au fur et à mesure.
Lorsque j'effectue la commande

 COPY users_test from 'C://users.csv' using delimiters ','  WITH CSV HEADER;

l'insertion des données dans la table users_test s'effectue mais mon soucis est que les données se rajoutent dans la table à chaque "copy", effectuant donc des doublons.
Si je rajoute une contrainte unique sur un champ de la table "users_test", alors la copy ne s'effectue pas car elle n'effectue pas un "insert or ignore".

sauriez vous comment je pourrais arriver à résoudre ce problème svp?
Je n'ai pas trouvé dans l'aide sur la commande "copy" pour l'instant. Si cela n'est pas possible avec cette commande, est ce qu'il existe une autre commande qui me permet d'obtenir le résultat souhaité?

amicalement
Carole

meles

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Re,
  voila le man de COPY : http://docs.postgresqlfr.org/9.0/sql-copy.html

Pour ton pb, c'est logique qu'il insère l'intégralité du fichier à chaque fois puisqu'il le relis entièrement.

Si ton CSV est alimenté au fur et a mesure, tu peux peut-être tenter un TRUNCATE de la table avant le copy, mais ça implique de recharger la table intégralement à chaque fois.

L'autre solution serait peut être d'effacer le CSV à chaque lecture (ou du moins, le renommer)

Cordialement

Dernière modification par meles (04/02/2011 17:49:46)

gleu

Administrateur

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Oui, COPY n'est pas prévu pour ce cas. Soit, comme le propose meles, vous pouvez vider la table avec TRUNCATE puis utiliser COPY, soit vous devez coder un outil qui fera les insertions si les lignes n'existent pas déjà.

---

Guillaume.

Marc Cousin

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

À mon avis, le plus simple c'est de charger les données dans une table temporaire, puis, par jointure, de remplacer les enregistrements déjà en place par les nouveaux. Sauf si vous pouvez vous permettre le truncate évidemment.

---

Marc.

meles

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Bonne nuit ,
  y'a quand même un truc qui me turlupine dans cette histoire,  c'est que si j'ai bien suivi, le fichier CSV qui sert à alimenter la table est alimenté en permanence. Que ce passera-il si le COPY des données dans postgresql se passait en court d'écriture de données dans ce fichier? Ne risquerait on pas de se retrouver avec des données corrompues.

Bref, je trouve cette histoire risquée. De plus, si l'idéee est d'alimenter postgreql en continu, pourquoi ne pas écrire les données directement dans les tables au fur et à mesure plutôt que de passer par un fichier CSV intermédiaire.

Avez vous la main sur l'application qui écrit ses données en continues dans le CSV ? Est-ce que ça ne vaudrais pas le coup de passer par un ETL (talend au hasasrd) pour charger ces données.

Bref, on en sait vraiment pas assez pour donner des pistes.

Cordialement

Marc Cousin

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Ah, oui, évidemment. il faut lire un fichier propre. Si il y a un enregistrement pas fini à la fin quand le COPY arrive dessus, ça va péter. On va pas avoir de la corruption, on va avoir echec de copy et rollback de l'opération.

---

Marc.

carole_31

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Bonjour,
Tout d'abord je vous remercie pour votre aide.

En fait il n'y pas pas d'écriture sur le csv directement. Le secrétariat possède le fichier sur un espace propre et en effectue une copie sur un espace partagé avec le serveur nuxeo à la fin de la journée. Ensuite dans la soirée, je lancerais un batch effectuant la "copy" des données du csv sur la base, dans une table tampon. Suivant vos conseils je vais faire un truncate de la table tampon avant que les données soient copiées (before copy c'est bien celà non?) et ensuite faire un "insert or ignore" de la table tampon vers la table "users" (où j'inclue 2 triggers qui génèrent un username + un password lors de l'insert).
Cela parait pas mal non?

Carole

Marc Cousin

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Ça parait bien. Vous pouvez même, histoire de dire que ça soit parfait, utiliser une table temporaire pour la table tampon, si vous pouvez tout faire dans une seule session. Le chargement sera encore plus rapide (ces tables ne sont pas journalisées)

---

Marc.

meles

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Bonjour,
si j'ai bien suivi, dans la table à mettre à jour, il peut y avoir des doublons d'ou le trigger, dont on a parlé tantôt, capable de générer des jean.dupont[1234]. Si on rajoute un jean dupont, comment sait-on que c'est un nouveau pour lequel il va falloir créer un jean.dupont5, ou un qui existe déjà ?

Cordialement

flo

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Ah oui effectivement, si on parle de la même chose il y a un gros souci.
Une solution serait peut-être de calculer les différences en amont, dans le batch, à partir des fichiers texte, pour ne charger que la différence (en gardant à chaque fois l'historique de l'ancien fichier). C'est plutôt de cette manière que j'aurai essayé de faire. Mais sous Windows, je ne sais pas faire...
Dans ce cas, fonctionnellement, on fait confiance au fichier texte pour ne pas envoyer de doublon.

Sinon, il faudrait un identifiant supplémentaire dans la table à charger (la table définitive), et dans la table temporaire.
Chaque soir, faire un truncate de la table temporaire, copier les données dans la table temporaire en donnant un numéro de séquence (premier enregistrement : 1, 2ème : 2, etc...). (est-ce que copy sait faire cela?)
Ensuite, ne charger dans la table finale que les lignes dont l'identifiant n'est pas encore dans la table finale.

Marc Cousin

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

COPY ne sait pas le faire de lui même. Mais si on fait une table avec un champ serial (une séquence), oui, on peut le faire… il suffit de ne pas préciser la colonne dans le copy, elle s'autoincrémente toute seule…

---

Marc.

flo

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Et quand on fait le truncate, est-ce que la séquence repart au début? ou faudra-t-il la réinitialiser manuellement?

Marc Cousin

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Séquence émotion, je vais faire un RTFM à flo :
http://docs.postgresql.fr/9.0/sql-truncate.html
Plus sérieusement, c'est une nouveauté de la 8.4

---

Marc.

carole_31

Membre

Re : Faire "copy from csv" qui effectue un "insert or ignore + update"

Oui dans la table users, il peut y avoir des doublons sur le nom, prenom et c'est pour cela que le trigger générant des logins unique de type 'prenom.nom[1234...]' est nécessaire. Par contre dans les tables users et users_tampon il y a d'autres champs aussi, dont un 'n_candidat' qui est unique, donc je pensais que comme ce n° etait different, lors de l'insert un doublon de login ne sera pas généré.
Voici l'insert que je fais:
Oui dans la table users, il peut y avoir des doublons sur le nom, prenom et c'est pour cela que le trigger générant des logins unique de type 'prenom.nom[1234...]' est nécessaire. Par contre dans les tables users et users_tampon il y a d'autres champs aussi, dont un 'n_candidat' qui est unique, donc je pensais que comme ce n° etait different, lors de l'insert un doublon de login ne sera pas généré.
Lors de l'insert dans la table users je mets une condition 'WHERE not exists users.n_candidat=users_tampon.n_candidat'

Par contre je n'ai pas pu encore le tester en situation car mon trigger faisant le "truncate before copy" ne fonctionne pas.
voici ce que j'ai mis:

CREATE TRIGGER suppression
 BEFORE INSERT OR UPDATE
 ON users_tampon
 FOR EACH STATEMENT
 EXECUTE PROCEDURE vider_table(E'\\x');

CREATE OR REPLACE FUNCTION vider_table()
  RETURNS trigger AS
$BODY$
BEGIN 
   delete from users_tampon;
END;

J'ai essaye du coup en faisant un "delete from " à la place du truncate mais sans succès.