Types de base

• PostgreSQL offre un système de typage complet
  • types standards
  • types avancés propres à PostgreSQL

Préambule

• SQL possède un typage fort
  • le type utilisé décrit la donnée manipulée
  • garantit l’intégrité des données
  • primordial au niveau fonctionnel
  • garantit les performances

Menu

• Qu’est-ce qu’un type ?
• Les types SQL standards
  • numériques
  • temporels
  • textuels et binaires
• Les types avancés de PostgreSQL

Objectifs

• Comprendre le système de typage de PostgreSQL
• Savoir choisir le type adapté à une donnée
• Être capable d’utiliser les types avancés à bon escient

Les types de données

• Qu’est-ce qu’un type ?
• Représentation physique
• Impacts sur l’intégrité
• Impacts fonctionnels

Qu’est-ce qu’un type ?

• Un type définit :
  • les valeurs que peut prendre une donnée
  • les opérateurs applicables à cette donnée

Impact sur les performances

• Choisir le bon type pour :
  • optimiser les performances
  • optimiser le stockage

Impacts sur l’intégrité

• Le bon type de données garantit l’intégrité des données :
  • la bonne représentation
  • le bon intervalle de valeur

Le choix du type employé pour stocker une donnée est primordial pour garantir l’intégrité des données.

Par exemple, sur une base de données mal conçue, il peut arriver que les dates soient stockées sous la forme d’une chaîne de caractère. Ainsi, une date malformée ou invalide pourra être enregistrée dans la base de données, passant outre les mécanismes de contrôle d’intégrité de la base de données. Si une date est stockée dans une colonne de type date, alors ces problèmes ne se posent pas :

CREATE TABLE test_date (dt date);

INSERT INTO test_date VALUES ('2015-0717');

ERROR:  invalid input syntax for type date: "2015-0717"
LINE 1: INSERT INTO test_date VALUES ('2015-0717');

^

INSERT INTO test_date VALUES ('2015-02-30');

ERROR:  date/time field value out of range: "2015-02-30"
LINE 1: INSERT INTO test_date VALUES ('2015-02-30');

INSERT INTO test_date VALUES ('2015-07-17');

Impacts fonctionnels

• Un type de données offre des opérateurs spécifiques :
  • comparaison
  • manipulation
• Exemple: une date est-elle comprise entre deux dates données ?

Types numériques

• Entiers
• Flottants
• Précision fixée

Types numériques : entiers

• 3 types entiers :
  • smallint : 2 octets
  • integer : 4 octets
  • bigint : 8 octets
• Valeur exacte
• Signé
• Utilisation :
  • véritable entier
  • clé technique

Les smallint couvrent des valeurs de -32 768 à +32 767. Attention à ne réserver leur utilisation qu’à ce qui ne dépassera pas cette plage.

Les integer vont de -2,1 à +2,1 milliards environ. Ce n’est pas toujours suffisant. Les bigint sur 8 octets vont jusque environ 9,2 10¹⁸.

• Documentation officielle : Types entiers

Types numériques : flottants

• 2 types flottants :
  • real (= float4)
  • double precision (= float8)
• Données numériques « floues »
  • valeurs non exactes
• Utilisation :
  • stockage des données issues de capteurs

Ces types n’ont pas de bornes mais une précision limitée.

real (4 octets) peut aller de 10⁻³⁷ à 10³⁷ avec une précision d’au moins six chiffres décimaux. Le type double precision a une étendue de 10⁻³⁰⁷ à 10³⁰⁸ avec une précision d’au moins quinze chiffres.

• Documentation officielle : Types flottants

Types numériques : numeric

• 1 type
  • numeric(…, …)
• Type exact
  • mais calcul lent
• Précision choisie : totale, partie décimale
• Utilisation :
  • données financières
  • calculs exacts

Le type numeric est destiné aux calculs précis (financiers ou scientifiques par exemple) avec une précision arbitraire, avec une certaine lenteur et une consommation mémoire ou d’espace de stockage potentiellement plus grande que les types flottants.

• Documentation officielle : Nombres à précision arbitraire

Opérations sur les numériques

• Indexable : >, >=, =, <=,<
• +, -, /, *, modulo (%), puissance (^)
• Pour les entiers :
  • AND, OR, XOR (&, |, #)
  • décalage de bits (shifting): >>, <<
• Attention aux conversions (casts) / promotions !

Toutes les colonnes de types numériques sont indexables avec des index standards B-tree, permettant la recherche avec les opérateurs d’égalité, supérieur ou inférieur.

Pour les entiers, il est possible de réaliser des opérations bit-à-bit :

SELECT 2 | 4;

 ?column?
----------
        6

SELECT 7 & 3;

 ?column?
----------
        3

Il faut toutefois être vigilant face aux opérations de conversion de type implicites et celles de promotion de type numérique. En effet un index portant sur un champ numérique ne sera compatible qu’avec ce type.

Par exemple, les deux requêtes suivantes ramèneront le même résultat, mais l’une sera capable d’utiliser un éventuel index sur id, l’autre non, comme le montrent les plans d’exécution :

EXPLAIN SELECT * FROM t1 WHERE id = 10::int4;

                     QUERY PLAN
-----------------------------------------------------
 Bitmap Heap Scan on t1  (cost=4.67..52.52 rows=50 width=4)
   Recheck Cond: (id = 10)
   ->  Bitmap Index Scan on t1_id_idx  (cost=0.00..4.66 rows=50 width=0)
         Index Cond: (id = 10)

EXPLAIN SELECT * FROM t1 WHERE id = 10::numeric;

                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on t1  (cost=0.00..195.00 rows=50 width=4)
   Filter: ((id)::numeric = 10::numeric)

Cela peut paraître contre-intuitif, mais la conversion est réalisée dans ce sens pour ne pas perdre d’information. Par exemple, si la valeur numérique cherchée n’est pas un entier. Il faut donc faire spécialement attention aux types utilisés côté applicatif. Avec un ORM tel qu’Hibernate, il peut être tentant de faire correspondre un BigInt à un numeric côté SQL, ce qui engendrera des casts implicites, et potentiellement des indexes non utilisés.

Choix d’un type numérique

• integer ou biginteger :
  • identifiants (clés primaires et autre)
  • nombres entiers
• numeric :
  • valeurs décimales exactes
  • performance non critique
• float, real :
  • valeurs flottantes, non exactes
  • performance demandée : SUM(), AVG(), etc.

Pour les identifiants, il est préférable d’utiliser des entiers ou grands entiers. En effet, il n’est pas nécessaire de s’encombrer du bagage technique et de la pénalité en performance dû à l’utilisation de numeric. Contrairement à d’autres SGBD, PostgreSQL ne transforme pas un numeric sans partie décimale en entier, et celui-ci souffre donc des performances inhérentes au type numeric.

De même, lorsque les valeurs sont entières, il faut utiliser le type adéquat.

Pour les nombres décimaux, lorsque la performance n’est pas critique, préférer le type numeric: il est beaucoup plus simple de raisonner sur ceux-ci et leur précision que de garder à l’esprit les subtilités du standard IEEE 754 définissant les opérations sur les flottants. Dans le cas de données décimales nécessitant une précision exacte, il est impératif d’utiliser le type numeric.

Les nombres flottants (float et real) ne devraient être utilisés que lorsque les implications en terme de perte de précision sont intégrées, et que la performance d’un type numeric devient gênante. En pratique, cela est généralement le cas lors d’opérations d’agrégations.

Pour bien montrer les subtilités des types float, et les risques auquels ils nous exposent, considérons l’exemple suivant, en créant une table contenant 25 000 fois la valeur 0.4, stockée soit en float soit en numeric :

CREATE TABLE t_float AS (
    SELECT 0.04::float AS cf,
           0.04::numeric AS cn
    FROM generate_series(1, 25000)
);

SELECT sum(cn), sum(cf) FROM t_float ;

   sum   |       sum
---------+-----------------
 1000.00 | 999.99999999967

Si l’on considère la performance de ces opérations, on remarque des temps d’exécution bien différents :

SELECT sum(cn) FROM t_float ;

   sum
---------
 1000.00

Temps : 10,611 ms

SELECT sum(cf) FROM t_float ;

       sum
-----------------
 999.99999999967

Temps : 6,434 ms

Pour aller (beaucoup) plus loin, le document suivant détaille le comportement des flottants selon le standard :

• What Every Computer Scientist Should Know About Floating-Point Arithmetic

Types temporels

• Date
• Date & heure
  • …avec ou sans fuseau

Types temporels : date

• date
  • représente une date, sans heure
  • affichage format ISO : YYYY-MM-DD
• Utilisation :
  • stockage d’une date lorsque la composante heure n’est pas utilisée
• Cas déconseillés :
  • stockage d’une date lorsque la composante heure est utilisée

SELECT now()::date ;

    now
------------
 2019-11-13

Types temporels : time

• time
  • représente une heure sans date
  • affichage format ISO HH24:MI:SS
• Peu de cas d’utilisation
• À éviter :
  • stockage d’une date et de la composante heure dans deux colonnes

SELECT now()::time ;

       now
-----------------
 15:19:39.947677

Types temporels : timestamp

• timestamp (without time zone !)
  • représente une date et une heure
  • fuseau horaire non précisé
• Utilisation :
  • stockage d’une date et d’une heure

SELECT now()::timestamp ;

            now
----------------------------
 2019-11-13 15:20:54.222233

Le nom réel est timestamp without time zone. Comme on va le voir, il faut lui préférer le type timestamptz.

Types temporels : timestamp with time zone

• timestamp with time zone = timestamptz
  • représente une date et une heure
  • fuseau horaire inclus
  • affichage : 2019-11-13 15:33:00.824096+01
• Utilisation :
  • stockage d’une date et d’une heure, cadre mondial
  • calculs tiennent compte des heures d’été aussi
  • à préférer à timestamp without time zone

Ces deux exemples ont été exécutés à quelques secondes d’intervalle sur des instances en France (heure d’hiver) et au Brésil :

SHOW timezone;

   TimeZone
--------------
 Europe/Paris

SELECT  now() ;

              now
-------------------------------
 2019-11-13 15:32:09.615455+01

SHOW timezone;

  TimeZone
-------------
 Brazil/West

SELECT now() ;

              now
-------------------------------
 2019-11-13 10:32:39.536972-04

SET timezone to 'Europe/Paris' ;

SELECT now() ;

              now
-------------------------------
 2019-11-13 15:33:00.824096+01

On préférera presque tout le temps le type timestamptz à timestamp (sans fuseau horaire). Même si un seul fuseau horaire est utilisé, il permet de s’épargner le calcul des heures d’été et d’hiver !

Les deux types occupent 8 octets, le fuseau horaire ne coûte donc pas plus cher à stocker.

Types temporels : interval

• interval
  • représente une durée
• Utilisation :
  • exprimer une durée
  • dans une requête, pour modifier une date/heure existante

Choix d’un type temporel

• Préférer les types avec timezone
  • toujours plus simple à gérer au début qu’à la fin
• Considérer les types range pour tout couple « début/fin »
• Utiliser interval/generate_series

De manière générale, il est beaucoup plus simple de gérer des dates avec timezone côté base. En effet, dans le cas où un seul fuseau horaire est géré, les clients ne verront pas la différence. Si en revanche les besoins évoluent, il sera beaucoup plus simple de gérer les différents fuseaux à ce moment là.

Les points suivants concernent plus de la modélisation que des types de données à proprement parler, mais il est important de considérer les types range dès lors que l’on souhaite stocker un couple « date de début/date de fin ». Nous aurons l’occasion de revenir sur ces types.

Types chaînes

• Texte à longueur variable
• Binaires

En général, on choisira une chaîne de longueur variable.

(Nous ne parlerons pas ici du type char (à taille fixe), qu’on ne renconte plus guère que dans de très vieilles bases, et qui n’a même pas d’avantage de performance.)

Types chaînes : caractères

• varchar(_n_), text
• Représentent une chaîne de caractères
• Valident l’encodage
• Valident la longueur maximale de la chaîne
  • la taille est une contrainte
• Utilisation :
  • stocker des chaînes de caractères non binaires

Un champ de type varchar(10) stocke une chaîne d’au plus 10 caractères. Une chaîne plus grande sera rejetée, et non tronquée (sauf si ce sont des espaces à la fin, c’est une exigence du standard). 10 est ici une limite, une plus petite chaîne consommera moins de mémoire et d’espace disque.

Il faut considérer la longueur d’une chaîne comme une contrainte fonctionnelle. Si la limite n’est pas vraiment définie (champ commentaire d’un blog, ou même un champ de nom de famille…), préférez un type text à une limite arbitraire.

text ne figure pas dans le standard SQL mais se rencontre fréquemment. C’est un équivalent de varchar sans limite de taille. La limite de taille théorique de 1 Go sera en pratique plus basse, mais un champ text de 200 Mo, par exemple, est possible. Ce n’est pas forcément une bonne idée.

Types chaînes : binaires

• bytea
• Stockage de données binaires
  • encodage en hexadécimal ou séquence d’échappement
• Utilisation :
  • stockage de courtes données binaires
• Cas déconseillés :
  • stockage de fichiers binaires

Le type bytea permet de stocker des données binaires dans une base de données PostgreSQL.

Voir le module de formation sur les types avancés.

Collation

• L’ordre de tri dépend des langues & de conventions variables
• Collation par colonne / index / requête

SELECT * FROM mots ORDER BY t COLLATE "C" ;

CREATE TABLE messages (
       id int,
       fr TEXT COLLATE "fr_FR.utf8",
       de TEXT COLLATE "de_DE.utf8" );

L’ordre de tri des chaînes de caractère (« collation ») peut varier suivant le contenu d’une colonne. Rien que parmi les langues européennes, il existe des spécificités propres à chacune, et même à différents pays pour une même langue. Si l’ordre des lettres est une convention courante, il existe de nombreuses variations propres à chacune (comme é, à, æ, ö, ß, å, ñ…), avec des règles de tri propres. Certaines lettres peuvent être assimilées à une combinaison d’autres lettres. De plus, la place relative des majuscules, celles des chiffres, ou des caractères non alphanumérique est une pure affaire de convention.

La collation dépend de l’encodage (la manière de stocker les caractères), de nos jours généralement UTF8 (standard Unicode). PostgreSQL utilise par défaut UTF8 et il est chaudement conseillé de ne pas changer cela. De vieilles bases peuvent avoir conservé un encodage plus ancien.

La collation par défaut dans une base est définie à sa création, et est visible avec \l (ci-dessous pour une installation en français). Le type de caractères est généralement identique.

\l

                      Liste des bases de données
Nom        | Propriétaire | Encodage | Collationnement | Type caract. |…
-----------+--------------+----------+-----------------+--------------+
 pgbench   | pgbench      | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  |
 postgres  | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  |
 template0 | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | …
 template1 | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | …

Parmi les collations que l’on peut rencontrer, il y a par exemple en_US.UTF-8 (la collation par défaut de beaucoup d’installations), ou C, basée sur les caractères ASCII et les valeurs des octets. De vieilles installations peuvent encore contenir fr_FR.iso885915@euro.

Si le tri par défaut ne convient pas, on peut le changer à la volée dans la requête SQL, au besoin après avoir créé la collation.

Exemple avec du français :

CREATE TABLE mots (t text) ;

INSERT INTO mots
VALUES ('A'),('a'),('aa'),('z'),('ä'),('å'),('Å'),('aa'),('æ'),('ae'),('af'), ('ß'), ('ss') ;

SELECT * FROM mots ORDER BY t ; -- sous-entendu, ordre par défaut en français ici

 t
---
 a
 A
 å
 Å
 ä
 aa
 aa
 ae
 æ
 af
 ss
 ß
 z

Noter que les caractères « æ » et « ß » sont correctement assimilés à « ae » et « ss ». (Ce serait aussi le cas avec en_US.utf8 ou de_DE.utf8).

Avec la collation C, l’ordre est plus basique, soit celui des codes UTF-8 :

SELECT * FROM mots ORDER BY t COLLATE "C" ;

 t
---
 A
 a
 aa
 aa
 ae
 af
 ss
 z
 Å
 ß
 ä
 å
 æ

Un intérêt de la collation C est qu’elle est plus simple et se repose sur la glibc du système, ce qui lui permet d’être souvent plus rapide qu’une des collations ci-dessus. Il suffit donc parfois de remplacer ORDER BY champ_texte par ORDER BY champ_text COLLATE "C", à condition bien sûr que l’ordre ASCII convienne.

Il est possible d’indiquer dans la définition de chaque colonne quelle doit être sa collation par défaut :

Pour du danois :

-- La collation doit exister sur le système d'exploitation
CREATE COLLATION IF NOT EXISTS "da_DK" (locale='da_DK.utf8');

ALTER TABLE mots ALTER COLUMN t TYPE text COLLATE "da_DK" ;

SELECT * FROM mots ORDER BY t ; -- ordre danois

 t
---
 A
 a
 ae
 af
 ss
 ß
 z
 æ
 ä
 Å
 å
 aa

Dans cette langue, les majuscules viennent traditionnellement avant les minuscules, et « å» et « aa » viennent après le « z ».

Avec une collation précisée dans la requête, un index peut ne pas être utilisable. En effet, par défaut, il est trié sur disque dans l’ordre de la collation de la colonne. Un index peut cependant se voir affecter une collation différente de celle de la colonne, par exemple pour un affichage ou une interrogation dans plusieurs langues :

CREATE INDEX ON mots (t);   -- collation par défaut de la colonne
CREATE INDEX ON mots (t COLLATE "de_DE.utf8"); -- tri allemand

La collation n’est pas qu’une question d’affichage. Le tri joue aussi dans la sélection quand il y a des inégalités, et le français et le danois renvoient ici des résultats différents :

SELECT * FROM mots WHERE t > 'z' COLLATE "fr_FR";

 t
---
(0 ligne)

SELECT * FROM mots WHERE t > 'z' COLLATE "da_DK";

 t
---
 aa
 ä
 å
 Å
 aa
 æ

Collation & sources

Source des collations :

• le système : installations séparées nécessaires, différences entre OS
• librairie externe ICU

CREATE COLLATION danois (provider = icu, locale = 'da-x-icu') ;

Des collations comme en_US.UTF-8 ou fr_FR.UTF-8 sont dépendantes des locales installées sur la machine. Cela implique qu’elles peuvent subtilement différer entre deux systèmes, même entre deux versions d’un même système d’exploitation ! De plus, la locale voulue n’est pas forcément présente, et son mode d’installation dépend du système d’exploitation et de sa distribution…

Pour éliminer ces problèmes tout en améliorant la flexibilité, PostgreSQL 10 a introduit les collations ICU, c’est-à-dire standardisées et versionnées dans une librairie séparée. En pratique, les paquets des distributions l’installent automatiquement avec PostgreSQL. Les collations linguistiques sont donc immédiatement disponibles via ICU :

CREATE COLLATION danois (provider = icu, locale = 'da-x-icu') ;

La librairie ICU fournit d’autres collations plus spécifiques liées à un contexte, par exemple l’ordre d’un annuaire ou l’ordre suivant la casse. Par exemple, cette collation très pratique tient compte de la valeur des chiffres (« tri naturel ») :

CREATE COLLATION nombres (provider = icu, locale = 'fr-u-kn-kr-latn-digit');

SELECT * FROM
  (VALUES ('1 sou'),('01 sou'),('02 sous'),('2 sous'),
  ('10 sous'),('0100 sous') ) AS n(n)
ORDER BY n COLLATE nombres ;

     n
-----------
 01 sou
 1 sou
 02 sous
 2 sous
 10 sous
 0100 sous

Alors que, par défaut, « 02 » précéderait « 1 » :

SELECT * FROM
  (VALUES ('1 sou'),('01 sou'),('02 sous'),('2 sous'),
  ('10 sous'),('0100 sous') ) AS n(n)
ORDER BY n ;  -- tri avec la locale par défaut

     n
-----------
 0100 sous
 01 sou
 02 sous
 10 sous
 1 sou
 2 sous

Pour d’autres exemples et les détails, voir ce billet de Peter Eisentraut et la documentation officielle.

Pour voir les collations disponibles, consulter pg_collation :

SELECT collname, collcollate, collprovider, collversion
FROM pg_collation WHERE collname LIKE 'fr%' ;

  collname   | collcollate | collprovider | collversion
-------------+-------------+--------------+-------------
 fr-BE-x-icu | fr-BE       | i            | 153.80
 fr-BF-x-icu | fr-BF       | i            | 153.80
 fr-CA-x-icu | fr-CA       | i            | 153.80.32.1
 fr-x-icu    | fr          | i            | 153.80
 …
 fr_FR       | fr_FR.utf8  | c            | ¤
 fr_FR.utf8  | fr_FR.utf8  | c            | ¤
 fr_LU       | fr_LU.utf8  | c            | ¤
 fr_LU.utf8  | fr_LU.utf8  | c            | ¤
(57 lignes)

Les collations installées dans la base sont visibles avec \dO sous psql :

\dO

                Liste des collationnements
 Schéma |     Nom      |    Collationnement    | … | Fournisseur | …
--------+--------------+-----------------------+---+-------------+----
 public | belge        | fr-BE-x-icu           | … | icu         | …
 public | chiffres_fin | fr-u-kn-kr-latn-digit | … | icu         | …
 public | da_DK        | da_DK.utf8            | … | libc        | …
 public | danois       | da-x-icu              | … | icu         | …
 public | de_DE        | de_DE.utf8            | … | libc        | …
 public | de_phonebook | de-u-co-phonebk       | … | icu         | …
 public | es_ES        | es_ES.utf8            | … | libc        | …
 public | espagnol     | es-x-icu              | … | icu         | …
 public | fr_FR        | fr_FR.utf8            | … | libc        | …
 public | français     | fr-FR-x-icu           | … | icu         | …

Types avancés

• PostgreSQL propose des types plus avancés
  • faiblement structurés (JSON)
  • intervalle
  • géométriques
  • tableaux

Types faiblement structurés

• PostgreSQL propose plusieurs types faiblement structurés :
  • hstore : clé/valeur historique
  • XML : stockage, sans plus
  • JSON

Des types faiblement structurés peuvent apporter une souplesse que ne possède pas un schéma de base de données, par nature assez rigide.

Pour un type clé/valeur simple, hstore peut parfaitement faire l’affaire. Mais PostgreSQL sait manier du JSON depuis des années, qui est plus puissant et plus répandu.

Pour les détails, voir le module de formation sur les types avancés.

JSON

• json
  • stockage sous forme d’une chaîne de caractère
  • valide un document JSON sans modification
• jsonb
  • à préférer
  • stockage binaire optimisé
  • beaucoup plus de fonctions (dont JSONPath)

Pour manipuler du JSON dans PostgreSQL, préférer le type jsonb, compressé et offrant de nombreuses fonctionnalités et possibilités d’indexation. Le type json est historique et plus dédié à l’archivage à l’identique de documents JSON.

Pour les détails, voir le module de formation sur les types avancés.

Types intervalle de valeurs

• Représentation d’intervalle
  • utilisable avec plusieurs types : entiers, dates, timestamps, etc.
  • contrainte d’exclusion

Range

• Représente un intervalle de valeurs continues
  • entre deux bornes
  • incluses ou non
• Plusieurs types natifs
  • int4range, int8range, numrange
  • daterange, tsrange, tstzrange

Les intervalles de valeurs (range) représentent un ensemble de valeurs continues comprises entre deux bornes. Ces dernières sont entourées par des crochets [ et ] lorsqu’elles sont incluses, et par des parenthèses ( et ) lorsqu’elles sont exclues. L’absence de borne est admise et correspond à l’infini.

• [0,10] : toutes les valeurs comprises entre 0 et 10 ;
• (100,200] : toutes les valeurs comprises entre 100 et 200, 100 exclu ;
• [2021-01-01,) : toutes les dates supérieures au 1er janvier 2021 inclus ;
• empty : aucune valeur ou intervalle vide.

Le type abstrait anyrange se décline en int4range (int), int8range (bigint), numrange (numeric), daterange (date), tsrange (timestamp without timezone), tstzrange (timestamp with timezone).

Range : Manipulation

• Opérateurs spécifiques *, &&, <@ ou @>
• Indexation avec GiST ou SP-GiST
• Types personnalisés

Les opérateurs d’inclusion <@ et @> déterminent si une valeur ou un autre intervalle sont contenus dans l’intervalle de gauche ou de droite.

SELECT produit, date_validite FROM produits
 WHERE date_validite @> '2020-01-01'::date;

               produit                |      date_validite
--------------------------------------+-------------------------
 a0fd7a5a-6deb-4454-b7a7-9cd38eef53a4 | [2012-07-12,)
 79eb3a63-eb76-43b9-b1d6-f9f82dd77460 | [2019-07-31,2021-04-01)
 e4edaac4-33f1-426d-b2b0-4ea3b1c6caec | (,2020-01-02)

L’opérateur de chevauchement && détermine si deux intervalles du même type disposent d’au moins une valeur commune.

SELECT produit, date_validite FROM produits
 WHERE date_validite && '[2021-01-01,2021-12-31]'::daterange

               produit                |      date_validite
--------------------------------------+-------------------------
 8791d13f-bdfe-46f8-afc6-8be33acdbfc7 | [2012-07-12,)
 000a72d5-a90f-4030-aa15-f0a05e54b701 | [2019-07-31,2021-04-01)

L’opérateur d’intersection * reconstruit l’intervalle des valeurs continues et communes entre deux intervalles.

SELECT '[2021-01-01,2021-12-31]'::daterange
     + '[2019-07-31,2021-04-01)'::daterange AS intersection;

      intersection
-------------------------
 [2021-01-01,2021-04-01)

Pour garantir des temps de réponse acceptables sur les recherches avancées avec les opérateurs ci-dessus, il est nécessaire d’utiliser les index GiST ou SP-GiST. La syntaxe est la suivante :

CREATE INDEX ON produits USING gist (date_validite);

Enfin, il est possible de créer ses propres types range personnalisés à l’aide d’une fonction de différence. L’exemple ci-dessous permet de manipuler l’intervalle de données pour le type time. La fonction time_subtype_diff() est tirée de la documentation RANGETYPES-DEFINING.

-- fonction utilitaire pour le type personnalisé "timerange"
CREATE FUNCTION time_subtype_diff(x time, y time)
RETURNS float8 AS
  'SELECT EXTRACT(EPOCH FROM (x - y))'
LANGUAGE sql STRICT IMMUTABLE;

-- définition du type "timerange", basé sur le type "time"
CREATE TYPE timerange AS RANGE (
  subtype = time,
  subtype_diff = time_subtype_diff
);

-- Exemple
SELECT '[11:10, 23:00]'::timerange;

      timerange
---------------------
 [11:10:00,23:00:00]

Clés temporelles / contrainte d’exclusion

• Éviter le chevauchement d’intervalles (range)

CREATE TABLE reservations
(
  salle      text,
  professeur text,
  periode    tstzrange, -- période des cours
  UNIQUE (salle, periode WITHOUT OVERLAPS)
);

• Performances : extension btree_gist
• Avant PG18 : utiliser les clauses d’exclusion

Les clés temporelles apparues avec PostgreSQL 18 permettent d’éviter que des intervalles de temps se chevauchent. Ce souci est assez courant dans les réservations de ressources ou pour gestions de dates de validité de lignes.

Un index de type GiST est obligatoire, et nécessite l’extension btree_gist, livrée avec PostgreSQL :

Dans l’exemple suivant, la période est gérée dans un type intervalle avec des bornes de type timestamp with time zone.

CREATE EXTENSION IF NOT EXISTS btree_gist ;

CREATE TABLE reservations
(
  salle      text,
  professeur text,
  periode    tstzrange -- période des cours
);

Pour une même salle, les réservations ne doivent pas se recouvrir. Une clé primaire peut donc porter sur la salle et la période :

ALTER TABLE reservations ADD CONSTRAINT reservations_pk
PRIMARY KEY (salle, periode WITHOUT OVERLAPS);

\d reservations

                   Table « public.reservations »
  Colonne   |   Type    | Collationnement | NULL-able | Par défaut | …
------------+-----------+-----------------+-----------+------------+ …
 salle      | text      |                 | not null  |            | …
 professeur | text      |                 |           |            | …
 periode    | tstzrange |                 | not null  |            | …
Index :
    "reservations_pk" PRIMARY KEY (salle, periode WITHOUT OVERLAPS)

Si l’on a besoin d’une clé primaire technique, une clé unique, et non primaire, fonctionne de la même manière.

Des réservations successives dans les deux salles s’insèrent ainsi :

INSERT INTO reservations (professeur,salle,periode) VALUES
( 'Marc', 'salle techno', '[2026-06-16 09:00:00, 2026-06-16 10:00:00)');

INSERT INTO reservations (professeur,salle,periode) VALUES
( 'Guillaume', 'salle techno', '[2026-06-16 10:00:00, 2026-06-16 11:00:00)');

INSERT INTO reservations (professeur,salle,periode) VALUES
( 'Jean', 'salle informatique', '[2026-06-16 10:00:00, 2026-06-16 11:00:00)');

TABLE reservations ;

      salle        | professeur|                      periode
-------------------+-----------+---------------------------------------------------
salle techno       | Marc      |["2026-06-16 09:00:00+02","2026-06-16 10:00:00+02")
salle techno       | Guillaume |["2026-06-16 10:00:00+02","2026-06-16 11:00:00+02")
salle informatique | Jean      |["2026-06-16 10:00:00+02","2026-06-16 11:00:00+02")

Cette réservation empiète par contre sur la deuxième réservation et provoque donc une erreur :

INSERT INTO reservations (professeur,salle,periode) VALUES
( 'Damien', 'salle techno', '[2026-06-16 10:30:00, 2026-06-16 11:00:00)');

ERROR:  conflicting key value violates exclusion constraint "reservations_pkey"
DÉTAIL : Key (salle, periode)=(salle techno, ["2026-06-16 10:30:00+02","2026-06-16 11:00:00+02")) conflicts with existing key (salle, periode)=(salle techno, ["2026-06-16 10:00:00+02","2026-06-16 11:00:00+02")).

Une clé unique ou primaire permet de créer une clé étrangère pointant dessus. Cette table gère les inscriptions des élèves aux cours. Ceux-ci ne peuvent s’inscrire que pendant les périodes qui existent, ou une fraction du cours, et pas assister à plusieurs cours en même temps. Noter le mot-clé PERIOD nécessaire dans la définition.

CREATE TABLE eleves (eleve text,
                     salle text,
                     periode tstzrange,
    PRIMARY KEY (eleve, periode WITHOUT OVERLAPS),
    FOREIGN KEY (salle, PERIOD periode)
       REFERENCES reservations (salle, PERIOD periode)
    ) ;

\d eleves

                    Table « public.eleves »
 Colonne |   Type    | Collationnement | NULL-able | Par défaut
---------+-----------+-----------------+-----------+------------
 eleve   | text      |                 | not null  |
 salle   | text      |                 |           |
 periode | tstzrange |                 | not null  |
Index :
    "eleves_pkey" PRIMARY KEY (eleve, periode WITHOUT OVERLAPS)
Contraintes de clés étrangères :
    "eleves_salle_periode_fkey" FOREIGN KEY (salle, PERIOD periode) REFERENCES reservations(salle, PERIOD periode)

Deux inscriptions au même cours réussissent :

INSERT INTO eleves (eleve,salle,periode) VALUES
( 'Lucas', 'salle informatique', '[2026-06-16 10:00:00, 2026-06-16 11:00:00)');
INSERT INTO eleves (eleve,salle,periode) VALUES
( 'Jonathan', 'salle informatique', '[2026-06-16 10:00:00, 2026-06-16 11:00:00)');

Le cours doit exister dans la bonne période :

INSERT INTO eleves (eleve,salle,periode) VALUES
( 'Jonathan', 'salle informatique', '[2026-06-16 17:00:00, 2026-06-16 18:00:00)');

ERROR:  insert or update on table "eleves" violates foreign key constraint "eleves_salle_periode_fkey"
DÉTAIL : Key (salle, periode)=(salle informatique, ["2026-06-16 17:00:00+02","2026-06-16 18:00:00+02")) is not present in table "reservations".

Deux inscriptions simultanées de la même personne échouent, y compris pour une période légèrement différente :

INSERT INTO eleves (eleve,salle,periode) VALUES
( 'Jonathan', 'salle informatique', '[2026-06-16 10:00:00, 2026-06-16 10:10:00)');

ERROR:  conflicting key value violates exclusion constraint "eleves_pkey"
DÉTAIL : Key (eleve, periode)=(Jonathan, ["2026-06-16 10:00:00+02","2026-06-16 10:10:00+02")) conflicts with existing key (eleve, periode)=(Jonathan, ["2026-06-16 10:00:00+02","2026-06-16 11:00:00+02")).

Des périodes de taille inférieure comprises dans celles de la clé sont possibles, pour une absence en milieu de période par exemple :

INSERT INTO eleves (eleve,salle,periode) VALUES
('Léa', 'salle informatique', '[2026-06-16 10:00:00, 2026-06-16 10:15:00)');

INSERT INTO eleves (eleve,salle,periode) VALUES
('Léa', 'salle informatique', '[2026-06-16 10:45:00, 2026-06-16 11:00:00)');

Pour gérer ce dernier cas avec plusieurs intervalles disjoints, on pourrait même utiliser des types multirange. Une clé temporelle fonctionne aussi avec eux. Pour cela, remplacer tstzrange par tstzmultirange dans les deux tables, et utiliser une syntaxe comme :

INSERT INTO eleves (eleve,salle,periode)
VALUES ('Léa', 'salle informatique',
       '{[2026-06-16 10:00:00, 2026-06-16 10:15:00),
         [2026-06-16 10:45:00, 2026-06-16 11:00:00)}');

Les index créés automatiquement sur les tables sont ceux-ci :

SELECT indexdef FROM pg_indexes WHERE tablename in ( 'reservations','eleves') ;

                        indexdef
----------------------------------------------------------
 CREATE UNIQUE INDEX periodes_ne_se_recouvrent_pas
   ON public.reservations USING gist (salle, periode)
 CREATE UNIQUE INDEX eleves_pkey
   ON public.eleves USING gist (eleve, periode)

Avant PostgreSQL 18, il est possible d’arriver presque au même résultat avec une autre fonctionnalité, les contraintes d’exclusion. Elles gèrent tout type dont les occurences ne doivent pas se recouper, par exemple des objets géométriques. Les périodes de temps n’en sont qu’un cas particulier.

Types géométriques

• Plusieurs types natifs 2D :
  • point, line, lseg (segment),
  • polygon, circle, box, path
• Utilisation :
  • stockage de géométries simples, sans référentiel de projection
• Pour la géographie :
  • extension PostGIS

Certaines applications peuvent profiter des types géométriques. Voir la documentation officielle :

• Types géométriques
• Fonctions et opérateurs de géométrie

Pour de la cartographie, l’extension PostGIS est la référence. Une fois installée, elle apporte de nombreux types et fonctions dédiés.

Types utilisateurs

• Plusieurs types définissables par l’utilisateur
  • types composites
  • domaines
  • enums

Types composites

• Regroupe plusieurs attributs
  • la création d’une table implique la création d’un type composite associé
• Utilisation :
  • déclarer un tableau de données composites
  • en PL/pgSQL, déclarer une variable de type enregistrement

Les types composites sont assez difficiles à utiliser, car ils nécessitent d’adapter la syntaxe spécifiquement au type composite. S’il ne s’agit que de regrouper quelques attributs ensemble, autant les lister simplement dans la déclaration de la table.

En revanche, il peut être intéressant pour stocker un tableau de données composites dans une table.

Type énumération

• Ensemble fini de valeurs possibles
  • uniquement des chaînes de caractères
  • 63 caractères maximum
• Équivalent des énumérations des autres langages
• Utilisation :
  • listes courtes figées (statuts…)
  • évite des jointures

Référence :

• Type énumération

Conclusion

• Des types de base assez simples
• Le choix du type est capital

Quiz

https://dali.bo/s15_quiz