Vous trouverez en ligne les différentes versions complètes de ce document.

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cette licence interdit la réutilisation pour l’apprentissage d’une IA. Elle couvre les diapositives, les manuels eux-mêmes et les travaux pratiques.

Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

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');

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

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

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

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.

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

SELECT now()::date ;

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

SELECT now()::time ;

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

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.

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.

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.

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.)

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.

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.

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
 æ

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         | …

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.

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.

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).

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]

Une contrainte d’exclusion s’apparente à une contrainte d’unicité, mais pour des intervalles de valeurs. Le principe consiste à identifier les chevauchements entre deux lignes pour prévenir l’insertion d’un doublon sur un intervalle commun.

Dans l’exemple suivant, nous utilisons le type personnalisé timerange, présenté ci-dessus. La table vendeurs reprend les agents de vente d’un magasin et leurs plages horaires de travail, valables pour tous les jours ouvrés de la semaine.

CREATE TABLE vendeurs (
  nickname varchar NOT NULL,
  plage_horaire timerange NOT NULL,
  EXCLUDE USING GIST (plage_horaire WITH &&)
);

INSERT INTO vendeurs (nickname, plage_horaire)
VALUES
  ('john', '[09:00:00,11:00:00)'::timerange),
  ('bobby', '[11:00:00,14:00:00)'::timerange),
  ('jessy', '[14:00:00,17:00:00)'::timerange),
  ('thomas', '[17:00:00,20:00:00]'::timerange);

Un index GiST est créé automatiquement pour la colonne plage_horaire.

\x on
\di+

List of relations
-[ RECORD 1 ]-+----------------------------
Schema        | public
Name          | vendeurs_plage_horaire_excl
Type          | index
Owner         | postgres
Table         | vendeurs
Persistence   | permanent
Access method | gist
Size          | 8192 bytes
Description   |

L’ajout d’un nouveau vendeur pour une plage déjà couverte par l’un de ces collègues est impossible, avec une violation de contrainte d’exclusion, gérée par l’opérateur de chevauchement &&.

INSERT INTO vendeurs (nickname, plage_horaire)
VALUES ('georges', '[10:00:00,12:00:00)'::timerange);

ERROR:  conflicting key value violates exclusion constraint
        "vendeurs_plage_horaire_excl"
DETAIL:  Key (plage_horaire)=([10:00:00,12:00:00)) conflicts
         with existing key (plage_horaire)=([09:00:00,11:00:00)).

Il est aussi possible de mixer les contraintes d’unicité et d’exclusion grâce à l’extension btree_gist. Dans l’exemple précédent, nous imaginons qu’un nouveau magasin ouvre et recrute de nouveaux vendeurs. La contrainte d’exclusion doit évoluer pour prendre en compte une nouvelle colonne, magasin_id.

CREATE EXTENSION btree_gist;
ALTER TABLE vendeurs
  DROP CONSTRAINT IF EXISTS vendeurs_plage_horaire_excl,
  ADD COLUMN magasin_id int NOT NULL DEFAULT 1,
  ADD EXCLUDE USING GIST (magasin_id WITH =, plage_horaire WITH &&);

INSERT INTO vendeurs (magasin_id, nickname, plage_horaire)
VALUES (2, 'georges', '[10:00:00,12:00:00)'::timerange);

En cas de recrutement pour une plage horaire déjà couverte par le nouveau magasin, la contrainte d’exclusion lèvera toujours une erreur, comme attendu.

INSERT INTO vendeurs (magasin_id, nickname, plage_horaire)
VALUES (2, 'laura', '[09:00:00,11:00:00)'::timerange);

ERROR:  conflicting key value violates exclusion constraint
        "vendeurs_magasin_id_plage_horaire_excl"
DETAIL:  Key (magasin_id, plage_horaire)=(2, [09:00:00,11:00:00)) conflicts
         with existing key (magasin_id, plage_horaire)=(2, [10:00:00,12:00:00)).

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.

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.

Référence :

• Type énumération