Contraintes temporelles dans PostgreSQL 18

Publié le 01/10/2024 par Denis Laxalde

Toulouse, le 1er octobre 2024

Le mois dernier, le support du mot clé WITHOUT OVERLAPS a été ajouté à PostgreSQL pour la définition des contraintes et clés primaires et étrangères. Nous allons voir, grâce à un petit exemple, comment ceci pourrait être utilisé… À partir de PostgreSQL 18 !

Considérons une application de facturation disposant a minima d’une table customers et d’une table invoices, avec une relation entre les deux :

# CREATE TABLE customers (
    id int generated BY DEFAULT AS identity,
    name text NOT NULL,
    PRIMARY KEY (id)
);
CREATE TABLE
# CREATE TABLE invoices (
    id int generated BY DEFAULT AS IDENTITY,
    period daterange NOT NULL,
    customer_id int REFERENCES customers(id),
    cost float NOT NULL,
    CONSTRAINT temporal_invoices_uq UNIQUE (customer_id, period WITHOUT OVERLAPS)
);
CREATE TABLE

En particulier, nous avons défini une contrainte unique sur les colonnes customer_id et period avec la clause WITHOUT OVERLAPS pour garantir l’unicité d’une facture par client sur une période donnée et ainsi éviter une facturation redondante.

La clause WITHOUT OVERLAPS de cette contrainte se matérialise par la valorisation de la colonne conperiod dans la table pg_constraint :

# SELECT conperiod FROM pg_constraint WHERE conname = 'temporal_invoices_uq';
 conperiod
-----------
 t
(1 row)

et on retrouve sa définition via :

# SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_invoices_uq';
             pg_get_constraintdef
-----------------------------------------------
 UNIQUE (customer_id, period WITHOUT OVERLAPS)
(1 row)

(Note : l’extension btree_gist est nécessaire pour cet exemple car notre contrainte contient la colonne – sans WITHOUT OVERLAPS – customer_id qui n’est pas de type “range”.)

Que se passe-t-il si nous essayons de facturer un même client sur deux périodes se recouvrant ?

# INSERT INTO customers (name) VALUES ('dalibo');
INSERT 0 1
# TABLE customers;
 id |  name
----+--------
  1 | dalibo
(1 row)
# INSERT INTO invoices (customer_id, period, cost)
    VALUES (1, '[2024-08-01, 2024-08-31]'::daterange, 123.45);
INSERT 0 1
# INSERT INTO invoices (customer_id, period, cost)
    VALUES (1, '[2024-08-12, 2024-09-23]'::daterange, 678.90);
ERROR:  conflicting key value violates exclusion constraint "temporal_invoices_uq"
DETAIL:  Key (customer_id, period)=(1, [2024-08-12,2024-09-24)) conflicts with existing key (customer_id, period)=(1, [2024-08-01,2024-09-01)).

Comme prévue, une erreur est levée ! Pratique, non ?

A contrario, nous pouvons bien sûr insérer des lignes sans recouvrement :

# INSERT INTO invoices (customer_id, period, cost)
    VALUES (1, '[2024-09-01, 2024-09-30]'::daterange, 678.90);
INSERT 0 1
# TABLE invoices;
 id |         period          | customer_id |  cost
----+-------------------------+-------------+--------
  1 | [2024-08-01,2024-09-01) |           1 | 123.45
  3 | [2024-09-01,2024-10-01) |           1 |  678.9
(2 rows)

Avant l’introduction de cette clause, il était possible d’implémenter le même comportement via une contrainte d’exclusion :

# CREATE TABLE invoices2 (
    id int generated BY DEFAULT AS IDENTITY,
    period daterange NOT NULL,
    customer_id int REFERENCES customers(id),
    cost float NOT NULL,
    CONSTRAINT exclusion_invoices EXCLUDE USING gist (customer_id WITH =, period WITH &&)
);
CREATE TABLE
# INSERT INTO invoices2 (customer_id, period, cost)
    VALUES (1, '[2024-09-01, 2024-09-30]'::daterange, 678.90);
INSERT 0 1
# INSERT INTO invoices2 (customer_id, period, cost)
    VALUES (1, '[2024-08-12, 2024-09-27]'::daterange, 1234.5);
ERROR:  conflicting key value violates exclusion constraint "exclusion_invoices"
DETAIL:  Key (customer_id, period)=(1, [2024-08-12,2024-09-28)) conflicts with existing key (customer_id, period)=(1, [2024-09-01,2024-10-01)).

Ajoutons aussi que cette clause, même si elle se réfère au champ fonctionnel des bases de données temporelles et aux ajouts au standard SQL:2011, s’applique dans le cas de PostgreSQL à tout type d’intervalle, donc pas uniquement les intervalles de types “temporels” comme les dates dans notre exemple mais aussi, par exemple, à des entiers.

Le standard SQL:2011 dénomme “périodes” les colonnes telles que period dans notre exemple et une table incluant une telle colonne est appelée application-time period table. Il propose une autre fonctionnalité intéressante pour les tables temporelles : le découpage automatique des “périodes” lors d’UPDATE/DELETE au travers d’une clause FOR PORTION OF. À suivre pour une intégration future dans PostgreSQL…

Pour aller plus loin :

DALIBO

DALIBO est le spécialiste français de PostgreSQL®. Nous proposons du support, de la formation et du conseil depuis 2005.