Forums PostgreSQL.fr

damalaan

Membre

clause not exists dans une requete

Bonjour

j'ai la fonction suivante qui me permet d’importer un fichier csv dans deux tables finales (en 1--n)
Le principe est le suivant :
-j'importe le fichier brute dans la tbl_import
-je mets en forme mes valeurs dans la tbl_temp2_tm2
-je mets un certain nb de valeurs dans la table tbl_tournee_trn (avec une clause WHERE NOT EXISTS pour ne pas avoir de doublon)
-et je termine par mettre dans la table tbl_valeur_mg les valeurs (du côté n de la relation)

Mon problème est sur cette dernière étape: je viens de m'apercevoir que je ne gère pas les doublons possibles dans cette table. Je l'ai constaté en en important plusqieurs fois le même fichier.

Je ne sais pas où placer cette condition qui je pense doit être un truc du genre WHERE NOT EXISTS

-- Function: import(text)

-- DROP FUNCTION import(text);

CREATE OR REPLACE FUNCTION import(text)
  RETURNS void AS
$BODY$

DECLARE
ordre_sql varchar;

BEGIN
-- on efface les tables temporaires
delete from tbl_import;
delete from tbl_temp2_tm2;

-- on importe le fichier
SET client_encoding TO 'LATIN1';
ordre_sql := 'COPY tbl_import FROM ''' || $1 || ''' WITH CSV HEADER DELIMITER '';''';
execute ordre_sql;

-- on transforme tous les champs en données numériques et on les insère dans la 2è table temp 
INSERT INTO tbl_temp2_tm2 (tm2_date_prel, tm2_tournee, tm2_esp, tm2_usine, tm2_matricule, tm2_mg)


SELECT  
	
		to_number((
		(substring(annnee,3,2) ||
		(case CHAR_LENGTH(mois)
		when 1 then '0'||mois
		when 2 then mois
		end)
		|| decade)), '999999') as date_prel, 
		
		to_number(tournee || tour, '9999') as tournee, 
		
		(case espece
		when 'Vache ' then 1
		Else  2
		end) as esp,
		
		to_number(usine, '9999'),

		to_number(matricule, '999999'),

		to_number(replace(mg, ',', '.'),'99.9')
from tbl_import where
annnee not like 'an%'
and
decade like '01' or decade like '02' or decade like '03'
and
usine not like '9999';

-- on insère les tournées dans la table tbl_tournee_trn

INSERT INTO 
tbl_tournee_trn (trn_date_prel, trn_tournee, trn_esp, trn_usine)


SELECT DISTINCT
tm2_date_prel, tm2_tournee, tm2_esp, tm2_usine FROM tbl_temp2_tm2
WHERE NOT EXISTS 
	(SELECT *
	FROM tbl_tournee_trn
	WHERE 
	tbl_tournee_trn.trn_date_prel = tbl_temp2_tm2.tm2_date_prel AND
	tbl_tournee_trn.trn_esp = tbl_temp2_tm2.tm2_esp AND
	tbl_tournee_trn.trn_tournee = tbl_temp2_tm2.tm2_tournee AND
	tbl_tournee_trn.trn_usine = tbl_temp2_tm2.tm2_usine);

-- on insère les valeurs mg dans la tbl_valeur_mg --et mon problème est là!!!!!
INSERT INTO tbl_valeur_mg (trn_id, mg_matricule, mg_val)

SELECT 
  tbl_tournee_trn.trn_id, 
  tbl_temp2_tm2.tm2_matricule, 
  tbl_temp2_tm2.tm2_mg
FROM 
  tbl_tournee_trn, 
  tbl_temp2_tm2
WHERE 
  tbl_tournee_trn.trn_date_prel = tbl_temp2_tm2.tm2_date_prel AND
  tbl_tournee_trn.trn_esp = tbl_temp2_tm2.tm2_esp AND
  tbl_tournee_trn.trn_tournee = tbl_temp2_tm2.tm2_tournee AND
  tbl_tournee_trn.trn_usine = tbl_temp2_tm2.tm2_usine;
      
            
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION import(text) OWNER TO postgres;

code de la table _tournee_trn

-- Table: tbl_tournee_trn

-- DROP TABLE tbl_tournee_trn;

CREATE TABLE tbl_tournee_trn
(
  trn_date_prel integer NOT NULL,
  trn_esp integer NOT NULL,
  trn_tournee integer NOT NULL,
  trn_usine integer NOT NULL,
  trn_id bigserial NOT NULL,
  trn_chauffeur integer,
  CONSTRAINT "clé_primaire" PRIMARY KEY (trn_id),
  CONSTRAINT "clé_2" UNIQUE (trn_date_prel, trn_esp, trn_tournee, trn_usine)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tbl_tournee_trn OWNER TO postgres;

code de la table tbl_valeur_mg

-- Table: tbl_valeur_mg

-- DROP TABLE tbl_valeur_mg;

CREATE TABLE tbl_valeur_mg
(
  mg_id bigserial NOT NULL,
  trn_id bigserial NOT NULL,
  mg_matricule integer NOT NULL,
  mg_val numeric(4,1) NOT NULL,
  CONSTRAINT pk PRIMARY KEY (mg_id),
  CONSTRAINT fk FOREIGN KEY (trn_id)
      REFERENCES tbl_tournee_trn (trn_id) MATCH FULL
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tbl_valeur_mg OWNER TO postgres;

Dernière modification par damalaan (31/03/2011 14:52:06)

gleu

Administrateur

Re : clause not exists dans une requete

Les doublons par rapport à quels colonnes ? rien n'empêche leur insertion au niveau de PostgreSQL en tout cas.

---

Guillaume.

damalaan

Membre

Re : clause not exists dans une requete

il s'agit des doublons par rapport aux colonnes
  trn_id bigserial NOT NULL,
  mg_matricule integer NOT NULL,
  mg_val numeric(4,1) NOT NULL,

de la table tbl_valeur_mg

"rien n'empêche leur insertion au niveau de PostgreSQL en tout cas."
et effectivement c'est bien là mon problème!!!

autant je l'ai bien fait sur la table tbl_tournee_trn autant sur tbl_valeur_mg je ne sais pas comment le mettre en place
je peux mettre un index (c'est d'ailleurs ce que j'ai fait sur la première table) mais ça me fera planter la fonction en cas de doublons, il faut donc que je le gère lors de l'insertion.

Marc Cousin

Membre

Re : clause not exists dans une requete

Non, il faut avant tout que vous mettiez des contraintes d'intégrité dans la base. Vous n'arriverez jamais à garantir n'avoir pas de bug dans le code applicatif.

Par contre, il faut AUSSI que la fonction PL supprime les doublons.

C'est un doublon sur l'ensemble des 3 colonnes ? qu'est ce qui définit exactement un doublon dans la table tbl_valeur_mg ?

---

Marc.

damalaan

Membre

Re : clause not exists dans une requete

ok pour la contrainte (même s'il n'y aura jamais de saisie manuelle, uniquement de l'import de masse)

Le doublon est effectivement basé sur les 2 premières colonnes
trn_id         mg_matricule         mg_val
53205        1                           42.3
53205        2                           42.3 -->pas doublon
53210        1                           42.3-->pas doublon
53205        1                           42.3-->doublon

Marc Cousin

Membre

Re : clause not exists dans une requete

Ok, donc dans l'insert, que voulez vous faire en cas de doublon ? Remplacer l'enregistrement par la nouvelle valeur, ou ignorer la nouvelle valeur ?

---

Marc.

damalaan

Membre

Re : clause not exists dans une requete

...ignorer...

ceci dit pour ma culture perso, je suis intéressé par les 2!!

Marc Cousin

Membre

Re : clause not exists dans une requete

Ignorer (à l'arrache, sans tests, évidemment):

INSERT INTO tbl_valeur_mg (trn_id, mg_matricule, mg_val)

SELECT
  tbl_tournee_trn.trn_id,
  tbl_temp2_tm2.tm2_matricule,
  tbl_temp2_tm2.tm2_mg
FROM
  tbl_tournee_trn,
  tbl_temp2_tm2
WHERE
  tbl_tournee_trn.trn_date_prel = tbl_temp2_tm2.tm2_date_prel AND
  tbl_tournee_trn.trn_esp = tbl_temp2_tm2.tm2_esp AND
  tbl_tournee_trn.trn_tournee = tbl_temp2_tm2.tm2_tournee AND
  tbl_tournee_trn.trn_usine = tbl_temp2_tm2.tm2_usine AND
  NOT EXISTS (SELECT 1 FROM tbl_valeur_mg mg_tmp WHERE mg_tmp.trn_id= tbl_tournee_trn.trn_id AND mg_tmp.mg_matricule=tbl_temp2_tm2.tm2_matricule)

Au passage, on est content d'avoir la contrainte, puisque la sous-requête va pouvoir s'appuyer sur son index au besoin… et il faut que tbl_valeur_mg ne bouge pas pendant l'opération, donc pas d'autre batch en train de faire pareil en même temps.

Dernière modification par Marc Cousin (31/03/2011 15:39:23)

---

Marc.

Marc Cousin

Membre

Re : clause not exists dans une requete

Pour l'UPDATE (à faire dans une seconde passe, une fois qu'on a inséré ce qui manque), quelque chose comme ça:

UPDATE tbl_valeur_mg
SET mg_val=tbl_temp2_tm2.tm2_mg
FROM
  tbl_tournee_trn,
  tbl_temp2_tm2
WHERE
  tbl_tournee_trn.trn_date_prel = tbl_temp2_tm2.tm2_date_prel AND
  tbl_tournee_trn.trn_esp = tbl_temp2_tm2.tm2_esp AND
  tbl_tournee_trn.trn_tournee = tbl_temp2_tm2.tm2_tournee AND
  tbl_tournee_trn.trn_usine = tbl_temp2_tm2.tm2_usine AND
  tbl_valeur_mg.trn_id= tbl_tournee_trn.trn_id AND
  tbl_valeur_mg.mg_matricule=tbl_temp2_tm2.tm2_matricule

Évidemment, on constate qu'il manque toujours un UPSERT ou MERGE chez PostgreSQL. C'est gênant dans ce genre de requête…

---

Marc.

damalaan

Membre

Re : clause not exists dans une requete

Evidemment ça marche nickel!!!!

Ces fichues requêtes imbriquées, je ne sais pas si j'y arriverai un jour, mon cerveau ne suit pas!!!!

merci