Vous n'êtes pas identifié(e).
Pages : 1
Bonjour à tous,
Étant nouveau utilisateur de postgres, j'ai créé une base de données sous postgres 13. Elle contient des tables
dont 4 Champs
ID integer (PK)
Numéro entité varchar (20)
début double precision not null
fin double precision not nulle.
Je souhaite créé une contrainte qui vérifiera que pour un numéro d'entité identique, les champs début et fin d'un enregistrement ne doivent pas chevaucher avec un autre enregistrement.
Je précise que la clé primaire définie ou choisi ne règle pas mon problème.
Merci de m'aider.
Hors ligne
C'est généralement possible avec la contrainte EXCLUDE, mais il vous faut un champ de type RANGE dans ce cas, et il n'existe pas de RANGE pour le type double precision. Donc je ne vois que deux possibilités :
* si vous pouvez utiliser un numeric à la place du double precision, transformer les deux champs double precision en un champ numrange, ce qui permettra d'utiliser une contrainte EXCLUDE
* écrire un trigger qui réalisera la contrainte.
La première solution me paraît plus propre et plus simple. C'est en tout cas avec elle que je commencerais.
Guillaume.
Hors ligne
Il devrait être possible de déclarer une contrainte d'exclusion avec un cast explicite des double en numeric pour en faire un numrange. Bien évidemment, cela n'évitera pas les problèmes de précisions liés au type double precision. Si c'est quelque chose qui peut poser problème, alors modifier la table pour stocker des numeric est effectivement la meilleure chose à faire.
Julien.
https://rjuju.github.io/
Hors ligne
Merci Guillaume pour ta contribution.
Stp peux-tu me mettre sur la voie pour le trigger ou la contrainte exclude?
Hors ligne
Merci julien.
Je vais transformer les doubles précision en numerique
Hors ligne
Pour les contraintes d'exclusion vous pouvezz consulter la documentation, par exemple :
* https://www.postgresql.org/docs/current … -EXCLUSION
* https://www.postgresql.org/docs/current … CONSTRAINT
Julien.
https://rjuju.github.io/
Hors ligne
Merci Julien
Hors ligne
Après avoir transformé les champs début et fin en numeric et lu la documentation,
J'ai ajouté un champ Valrange de type numrange[]
j'ai ensuite écrit cette contrainte d'exclusion
EXCLUDE USING GIST ("ID" WITH =, "Valrange"("mFrom","mTo",'[]'::numeric) WITH &&)
mais cela ne fonctionne toujours pas
Hors ligne
mais cela ne fonctionne toujours pas
Serait-il possible d'avoir un peu plus de détails? Idéalement un script contenant le scénario pour reproduire le problème de 0.
Julien.
https://rjuju.github.io/
Hors ligne
Voici le script de la table :
CREATE TABLE "DHSample"
(
"SampleID" integer NOT NULL (PK),
"HoleID" character varying(20) ,
"mFrom" Numeric NOT NULL,
"mTo" Numeric ,
"mLoss" numeric,
"SpleLength" double precision generated always as
(CASE WHEN "SampleCategory"='NO SAMPLE' THEN "mTo"-"mFrom" WHEN "SampleCategory"='QAQC'THEN
NULL ELSE "mTo"-"mFrom"-"mLoss" END) stored,
"SampleCategory" character varying(10) NOT NULL DEFAULT 'SAMPLE'::character varying,
CONSTRAINT "DHSple_mTo_check" CHECK ("mTo" > "mFrom"),
CONSTRAINT "DHSpleTest_mFrom_check" CHECK ("mFrom" >= 0::numeric)
)
;
Il y a un flux important de données et je dois absolument empêcher tout chevauchement entre les données (mFrom et mTo) ayant un même HoleID.
Hors ligne
Ce n'est pas un scénario complet. Il manque au moins la contrainte EXCLUDE et quelques INSERT montrant que les données sont quand même insérées, malgré la présence de la contrainte.
Guillaume.
Hors ligne
Voici le scénario 1 qui fonctionne à merveille sans la contrainte EXCLUDE mais qui accepte éventuellement les chevauchements
CREATE TABLE "DHSample"
(
"SampleID" integer NOT NULL,
"HoleID" character varying(20) ,
"mFrom" Numeric NOT NULL,
"mTo" Numeric ,
"mLoss" numeric,
"SpleLength" double precision generated always as
(CASE WHEN "SampleCategory"='NO SAMPLE' THEN "mTo"-"mFrom" WHEN "SampleCategory"='QAQC'THEN
NULL ELSE "mTo"-"mFrom"-"mLoss" END) stored,
"SampleCategory" character varying(10) NOT NULL DEFAULT 'SAMPLE'::character varying,
CONSTRAINT "DHSample_pkey" PRIMARY KEY ("SampleID"),
CONSTRAINT "DHSple_mTo_check" CHECK ("mTo" > "mFrom"),
CONSTRAINT "DHSpleTest_mFrom_check" CHECK ("mFrom" >= 0::numeric)
)
;
INSERT INTO "DHSample" ("SampleID","HoleID", "mFrom", "mTo", "mLoss", "SampleCategory") VALUES (100,'Z1', 0, 1, -0.2, 'SAMPLE');
INSERT INTO "DHSample" ("SampleID","HoleID", "mFrom", "mTo", "mLoss", "SampleCategory") VALUES (101,'Z1', 1, 3, 0 , 'SAMPLE');
INSERT INTO "DHSample" ("SampleID","HoleID", "mFrom", "mTo", "mLoss", "SampleCategory") VALUES (102,'Z1', 2.63, 6.04, 0.17, 'SAMPLE');
Dernière modification par Lamethode (07/03/2022 18:55:39)
Hors ligne
Voici le scénario 2 avec la contrainte EXCLUDE
CREATE TABLE "DHSample"
(
"SampleID" integer NOT NULL,
"HoleID" character varying(20) ,
"mFrom" Numeric NOT NULL,
"mTo" Numeric ,
"mLoss" numeric,
"SpleLength" double precision generated always as
(CASE WHEN "SampleCategory"='NO SAMPLE' THEN "mTo"-"mFrom" WHEN "SampleCategory"='QAQC'THEN
NULL ELSE "mTo"-"mFrom"-"mLoss" END) stored,
"SampleCategory" character varying(10) NOT NULL DEFAULT 'SAMPLE'::character varying,
CONSTRAINT "DHSample_pkey" PRIMARY KEY ("SampleID"),
CONSTRAINT "DHSple_mTo_check" CHECK ("mTo" > "mFrom"),
CONSTRAINT "DHSpleTest_mFrom_check" CHECK ("mFrom" >= 0::numeric)
EXCLUDE USING GIST ("HoleID" WITH =, ["mFrom","mTo"] ::numrange WITH &&)
)
;
ERROR: ERREUR: erreur de syntaxe sur ou près de « [ »
LINE 15: EXCLUDE USING GIST ("HoleID" WITH =, ["mFrom","mTo"]...
^
SQL state: 42601
Character: 687
La table ne se crée pas du tout... Les champs mFrom et mTo doivent obligatoirement être dans la base de données.
Hors ligne
Je veux bien contourner le problème en créant un champ
"Overlap" numrange[]
Ce qui modifierait la contrainte EXCLUDE sous la forme
EXCLUDE USING GIST ("HoleID" WITH =, "Overlap" WITH &&)
Mais la question est de savoir comment remplir automatiquement le champ "Overlap" en utilisant les champs "mFrom" et "mTo"?
Hors ligne
Cette syntaxe est effectivement invalide. Vous pouvez utiliser
numange("mFrom","mTo")et également ajouter la virgule manquante à votre exemple.
Veuillez noter également qu'il vous faudra installer btree_gist.
Julien.
https://rjuju.github.io/
Hors ligne
Ouf rjuju grand merci.
Problème résolu.
Hors ligne
Pages : 1