# Types de base > Types numériques, temporels, chaînes, JSON, intervalles de valeurs, types géométriques et types utilisateurs. --- Index LLMS : [llms.txt](/fr/llms.txt) --- # Types de base {#dev1-ch2} - 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 : **Exemple 2-1.** Exemple 2\-1 \- code ou sortie extraite du manuel DEV1\. ```sql CREATE TABLE test_date (dt date); INSERT INTO test_date VALUES ('2015-0717'); ``` **Exemple 2-2.** Exemple 2\-2 \- code ou sortie extraite du manuel DEV1\. ```text ERROR: invalid input syntax for type date: "2015-0717" LINE 1: INSERT INTO test_date VALUES ('2015-0717'); ``` ^ **Exemple 2-3.** Exemple 2\-3 \- code ou sortie extraite du manuel DEV1\. ```sql INSERT INTO test_date VALUES ('2015-02-30'); ``` **Exemple 2-4.** Exemple 2\-4 \- code ou sortie extraite du manuel DEV1\. ```text ERROR: date/time field value out of range: "2015-02-30" LINE 1: INSERT INTO test_date VALUES ('2015-02-30'); ``` **Exemple 2-5.** Exemple 2\-5 \- code ou sortie extraite du manuel DEV1\. ```sql 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](https://docs.postgresql.fr/current/datatype-numeric.html#DATATYPE-INT) ### 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](https://docs.postgresql.fr/current/datatype-numeric.html#DATATYPE-FLOAT) ### 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](https://docs.postgresql.fr/current/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL) ### 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 : **Exemple 2-6.** Exemple 2\-6 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT 2 | 4; ``` **Tableau 2-1.** Tableau 2\-1 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text ?column? ---------- 6 ``` **Exemple 2-7.** Exemple 2\-7 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT 7 & 3; ``` **Tableau 2-2.** Tableau 2\-2 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text ?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 : **Exemple 2-8.** Exemple 2\-8 \- code ou sortie extraite du manuel DEV1\. ```sql EXPLAIN SELECT * FROM t1 WHERE id = 10::int4; ``` **Tableau 2-3.** Tableau 2\-3 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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) ``` **Exemple 2-9.** Exemple 2\-9 \- code ou sortie extraite du manuel DEV1\. ```sql EXPLAIN SELECT * FROM t1 WHERE id = 10::numeric; ``` **Tableau 2-4.** Tableau 2\-4 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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](https://fr.wikipedia.org/wiki/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` : **Exemple 2-10.** Exemple 2\-10 \- code ou sortie extraite du manuel DEV1\. ```sql 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 ; ``` **Tableau 2-5.** Tableau 2\-5 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 : **Exemple 2-11.** Exemple 2\-11 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT sum(cn) FROM t_float ; ``` **Tableau 2-6.** Tableau 2\-6 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text sum --------- 1000.00 Temps : 10,611 ms ``` **Exemple 2-12.** Exemple 2\-12 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT sum(cf) FROM t_float ; ``` **Tableau 2-7.** Tableau 2\-7 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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](https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html)* ## 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 **Exemple 2-13.** Exemple 2\-13 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT now()::date ; ``` **Tableau 2-8.** Tableau 2\-8 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 **Exemple 2-14.** Exemple 2\-14 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT now()::time ; ``` **Tableau 2-9.** Tableau 2\-9 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 **Exemple 2-15.** Exemple 2\-15 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT now()::timestamp ; ``` **Tableau 2-10.** Tableau 2\-10 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 : **Exemple 2-16.** Exemple 2\-16 \- code ou sortie extraite du manuel DEV1\. ```sql SHOW timezone; ``` **Tableau 2-11.** Tableau 2\-11 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text TimeZone -------------- Europe/Paris ``` **Exemple 2-17.** Exemple 2\-17 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT now() ; ``` **Tableau 2-12.** Tableau 2\-12 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text now ------------------------------- 2019-11-13 15:32:09.615455+01 ``` **Exemple 2-18.** Exemple 2\-18 \- code ou sortie extraite du manuel DEV1\. ```sql SHOW timezone; ``` **Tableau 2-13.** Tableau 2\-13 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text TimeZone ------------- Brazil/West ``` **Exemple 2-19.** Exemple 2\-19 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT now() ; ``` **Tableau 2-14.** Tableau 2\-14 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text now ------------------------------- 2019-11-13 10:32:39.536972-04 ``` **Exemple 2-20.** Exemple 2\-20 \- code ou sortie extraite du manuel DEV1\. ```sql SET timezone to 'Europe/Paris' ; SELECT now() ; ``` **Tableau 2-15.** Tableau 2\-15 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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](https://dali.bo/s22_html#bytea). ### Collation - L’ordre de tri dépend des langues & de conventions variables - Collation par colonne / index / requête **Exemple 2-21.** Exemple 2\-21 \- code ou sortie extraite du manuel DEV1\. ```sql 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](https://fr.wikipedia.org/wiki/UTF-8) (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. **Exemple 2-22.** Exemple 2\-22 \- code ou sortie extraite du manuel DEV1\. ```sql \l ``` **Tableau 2-16.** Tableau 2\-16 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 : **Exemple 2-23.** Exemple 2\-23 \- code ou sortie extraite du manuel DEV1\. ```sql 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 ``` **Tableau 2-17.** Tableau 2\-17 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 : **Exemple 2-24.** Exemple 2\-24 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM mots ORDER BY t COLLATE "C" ; ``` **Tableau 2-18.** Tableau 2\-18 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 : **Exemple 2-25.** Exemple 2\-25 \- code ou sortie extraite du manuel DEV1\. ```sql -- 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 ``` **Tableau 2-19.** Tableau 2\-19 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 : **Exemple 2-26.** Exemple 2\-26 \- code ou sortie extraite du manuel DEV1\. ```sql 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 : **Exemple 2-27.** Exemple 2\-27 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM mots WHERE t > 'z' COLLATE "fr_FR"; ``` **Tableau 2-20.** Tableau 2\-20 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text t --- (0 ligne) ``` **Exemple 2-28.** Exemple 2\-28 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT * FROM mots WHERE t > 'z' COLLATE "da_DK"; ``` **Tableau 2-21.** Tableau 2\-21 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 : **Exemple 2-29.** Exemple 2\-29 \- code ou sortie extraite du manuel DEV1\. ```sql 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 ») : **Exemple 2-30.** Exemple 2\-30 \- code ou sortie extraite du manuel DEV1\. ```sql 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 ; ``` **Tableau 2-22.** Tableau 2\-22 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text n ----------- 01 sou 1 sou 02 sous 2 sous 10 sous 0100 sous ``` Alors que, par défaut, « 02 » précéderait « 1 » : **Exemple 2-31.** Exemple 2\-31 \- code ou sortie extraite du manuel DEV1\. ```sql 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 ``` **Tableau 2-23.** Tableau 2\-23 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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](https://www.enterprisedb.com/blog/more-robust-collations-icu-support-postgresql-10) et la [documentation officielle](https://docs.postgresql.fr/current/collation.html). Pour voir les collations disponibles, consulter `pg_collation` : **Exemple 2-32.** Exemple 2\-32 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT collname, collcollate, collprovider, collversion FROM pg_collation WHERE collname LIKE 'fr%' ; ``` **Tableau 2-24.** Tableau 2\-24 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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` : **Exemple 2-33.** Exemple 2\-33 \- code ou sortie extraite du manuel DEV1\. ```text \dO ``` **Tableau 2-25.** Tableau 2\-25 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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](https://dali.bo/s22_html). ### 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](https://dali.bo/s22_html#json). ## 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. **Exemple 2-34.** Exemple 2\-34 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT produit, date_validite FROM produits WHERE date_validite @> '2020-01-01'::date; ``` **Tableau 2-26.** Tableau 2\-26 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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. **Exemple 2-35.** Exemple 2\-35 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT produit, date_validite FROM produits WHERE date_validite && '[2021-01-01,2021-12-31]'::daterange ``` **Tableau 2-27.** Tableau 2\-27 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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. **Exemple 2-36.** Exemple 2\-36 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT '[2021-01-01,2021-12-31]'::daterange + '[2019-07-31,2021-04-01)'::daterange AS intersection; ``` **Tableau 2-28.** Tableau 2\-28 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 : **Exemple 2-37.** Exemple 2\-37 \- code ou sortie extraite du manuel DEV1\. ```sql 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](https://docs.postgresql.fr/current/rangetypes.html#RANGETYPES-DEFINING). **Exemple 2-38.** Exemple 2\-38 \- code ou sortie extraite du manuel DEV1\. ```sql -- 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; ``` **Tableau 2-29.** Tableau 2\-29 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text timerange --------------------- [11:10:00,23:00:00] ``` ### Clés temporelles / contrainte d’exclusion - Éviter le chevauchement d’intervalles (`range`) **Exemple 2-39.** Exemple 2\-39 \- code ou sortie extraite du manuel DEV1\. ```sql 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`. **Exemple 2-40.** Exemple 2\-40 \- code ou sortie extraite du manuel DEV1\. ```sql 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 : **Exemple 2-41.** Exemple 2\-41 \- code ou sortie extraite du manuel DEV1\. ```sql ALTER TABLE reservations ADD CONSTRAINT reservations_pk PRIMARY KEY (salle, periode WITHOUT OVERLAPS); ``` **Tableau 2-30.** Tableau 2\-30 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text \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 : **Exemple 2-42.** Exemple 2\-42 \- code ou sortie extraite du manuel DEV1\. ```sql 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 ; ``` **Tableau 2-31.** Tableau 2\-31 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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 : **Exemple 2-43.** Exemple 2\-43 \- code ou sortie extraite du manuel DEV1\. ```sql INSERT INTO reservations (professeur,salle,periode) VALUES ( 'Damien', 'salle techno', '[2026-06-16 10:30:00, 2026-06-16 11:00:00)'); ``` **Exemple 2-44.** Exemple 2\-44 \- code ou sortie extraite du manuel DEV1\. ```text 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. **Exemple 2-45.** Exemple 2\-45 \- code ou sortie extraite du manuel DEV1\. ```sql 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) ) ; ``` **Tableau 2-32.** Tableau 2\-32 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text \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 : **Exemple 2-46.** Exemple 2\-46 \- code ou sortie extraite du manuel DEV1\. ```sql 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 : **Exemple 2-47.** Exemple 2\-47 \- code ou sortie extraite du manuel DEV1\. ```sql INSERT INTO eleves (eleve,salle,periode) VALUES ( 'Jonathan', 'salle informatique', '[2026-06-16 17:00:00, 2026-06-16 18:00:00)'); ``` **Exemple 2-48.** Exemple 2\-48 \- code ou sortie extraite du manuel DEV1\. ```text 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 : **Exemple 2-49.** Exemple 2\-49 \- code ou sortie extraite du manuel DEV1\. ```sql INSERT INTO eleves (eleve,salle,periode) VALUES ( 'Jonathan', 'salle informatique', '[2026-06-16 10:00:00, 2026-06-16 10:10:00)'); ``` **Exemple 2-50.** Exemple 2\-50 \- code ou sortie extraite du manuel DEV1\. ```text 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 : **Exemple 2-51.** Exemple 2\-51 \- code ou sortie extraite du manuel DEV1\. ```text 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 : **Exemple 2-52.** Exemple 2\-52 \- code ou sortie extraite du manuel DEV1\. ```sql 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 : **Exemple 2-53.** Exemple 2\-53 \- code ou sortie extraite du manuel DEV1\. ```sql SELECT indexdef FROM pg_indexes WHERE tablename in ( 'reservations','eleves') ; ``` **Tableau 2-33.** Tableau 2\-33 \- sortie tabulaire ou résultat extrait du manuel DEV1\. ```text 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](https://docs.postgresql.fr/17/datatype-geometric.html) - [Fonctions et opérateurs de géométrie](https://docs.postgresql.fr/current/functions-geometry.html#FUNCTIONS-GEOMETRY-OP-TABLE) Pour de la cartographie, l’extension [PostGIS](https://postgis.net/) 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](https://docs.postgresql.fr/current/datatype-enum.html) ## Conclusion - Des types de base assez simples - Le choix du type est capital ## Quiz ****