PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 31/12/2021 01:53:04

Lamethode
Membre

Chevauchement de données

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

#2 31/12/2021 10:12:48

gleu
Administrateur

Re : Chevauchement de données

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

#3 31/12/2021 11:16:19

rjuju
Administrateur

Re : Chevauchement de données

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.

Hors ligne

#4 02/01/2022 12:24:44

Lamethode
Membre

Re : Chevauchement de données

Merci Guillaume pour ta contribution.
Stp peux-tu me mettre sur la voie pour le trigger ou la contrainte exclude?

Hors ligne

#5 02/01/2022 12:26:24

Lamethode
Membre

Re : Chevauchement de données

Merci julien.
Je vais transformer les doubles précision en numerique

Hors ligne

#6 02/01/2022 16:17:19

rjuju
Administrateur

Re : Chevauchement de données

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

Hors ligne

#7 02/01/2022 23:54:30

Lamethode
Membre

Re : Chevauchement de données

Merci Julien

Hors ligne

#8 06/03/2022 20:36:08

Lamethode
Membre

Re : Chevauchement de données

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

#9 07/03/2022 05:28:41

rjuju
Administrateur

Re : Chevauchement de données

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.

Hors ligne

#10 07/03/2022 16:45:40

Lamethode
Membre

Re : Chevauchement de données

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

#11 07/03/2022 17:47:43

gleu
Administrateur

Re : Chevauchement de données

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

#12 07/03/2022 18:54:13

Lamethode
Membre

Re : Chevauchement de données

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

#13 07/03/2022 18:58:52

Lamethode
Membre

Re : Chevauchement de données

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

#14 07/03/2022 19:03:29

Lamethode
Membre

Re : Chevauchement de données

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

#15 07/03/2022 19:11:34

rjuju
Administrateur

Re : Chevauchement de données

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.

Hors ligne

#16 08/03/2022 10:54:39

Lamethode
Membre

Re : Chevauchement de données

Ouf rjuju grand merci.
Problème résolu.

Hors ligne

Pied de page des forums