Vous trouverez en ligne les différentes versions complètes de ce document. Les solutions de TP ne figurent pas forcément dans la version imprimée, mais sont dans les versions numériques (PDF ou HTML).

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.

Ce module a pour but de présenter le standard SQL. Il se concentre sur la lecture de données déjà présentes en base. Ceci permet aussi d’aborder la question des types de données disponibles.

Si des données sont récoltées, organisées et stockées afin de répondre à un besoin spécifique, alors on parle de base de données. Une base de données peut utiliser différents supports : papier, fichiers informatiques, etc.

Un Système de Gestion de Bases de Données (SGBD), ou Database Management System (DBMS) en anglais, assure la gestion d’une base de données informatisée. Il permet l’accès aux données, leur modification, et assure que ces opérations en conservent la cohérence.

On voit aussi parfois le sigle SGBDR pour les bases « relationnelles » comme MySQL/MariaDB, Oracle, PostgreSQL…

Au fil des années ont été développés plusieurs modèles de données, que nous allons décrire.

Les modèles hiérarchiques et réseaux ont été les premiers modèles de données utilisées dans les années 60 sur les mainframes IBM ou Bull. Ils ont été rapidement supplantés par le modèle relationnel car les requêtes étaient dépendantes du modèle de données. Il était nécessaire de connaître les liens entre les différents nœuds de l’arborescence pour concevoir les requêtes. Les programmes sont donc complètement dépendants de la structure de la base de données. Cependant, des recherches cherchaient déjà à rendre indépendantes la vue logique et l’implémentation physique de la base de données.

Le modèle relationnel est issu des travaux d’Edgar F. Codd menés dans les laboratoires d’IBM à la fin des années 60. Ses travaux avaient pour but de rendre indépendant le stockage physique de la vue logique de la base de données. Mathématicien de formation, Codd s’est appuyé sur la théorie des ensembles et la logique des prédicats pour établir les fondements des bases de données relationnelles. Pour manipuler les données de façon ensembliste, il a mis au point le langage SQL. Ce langage a été développé dans les années 70, et est à l’origine du standard SQL, qui s’est répandu dans les années 80 et qui a rendu le modèle relationnel très populaire. La puissance de calcul du matériel de cette époque a permis la concrétisation de l’implémentation de cette couche d’abstraction entre modèle logique et stockage physique des données.

Le modèle objet est, quant à lui, issu de la mouvance autour des langages objets. Du fait de l’absence d’un standard avéré, le modèle objet n’a jamais été populaire et est toujours resté dans l’ombre du modèle relationnel.

La norme du modèle relationnel a néanmoins été étendue pour intégrer des fonctionnalités objets. On parle alors de modèle relationnel-objet. PostgreSQL en est un exemple, c’est un SGBDRO (Système de Gestion de Bases de Données Relationnel-Objet).

Les bases NoSQL sont une famille de bases de données qui répondent à d’autres besoins et contraintes que les bases relationnelles. Les bases NoSQL sont souvent des bases « sans schéma », la base ne vérifiant plus l’intégrité des données selon des contraintes définies dans le modèle de données. Chaque base de ce segment dispose d’un langage de requête spécifique, il n’y a ni standard ni norme.

Ce type de base offre souvent la possibilité d’offrir du sharding simple à mettre en œuvre. Le sharding consiste à répartir les données physiquement sur plusieurs serveurs pour paralléliser les traitements. En contrepartie, la durabilité des données n’est pas assurée, au contraire d’une base relationnelle qui assure la durabilité dès la réponse à un COMMIT. La cohérence des données entre nœuds est un autre sujet. Toute amélioration d’un outil sur ces points a évidemment un impact sur les performances.

Un des avantages des technologies NoSQL est qu’un modèle clé-valeur permet facilement d’utiliser des algorithmes de type MapReduce : diviser le problème en sous-problèmes traités parallèlement par différents nœuds (phase Map), puis synthétisés de façon centralisée (phase Reduce). Les bases de données relationnelles ne sont pas incompatibles avec MapReduce en soi. Simplement, le langage SQL étant déclaratif, il est conceptuellement opposé à la description fine des traitements qu’on doit réaliser avec MapReduce. C’est le travail de l’optimiseur d’effectuer ce genre d’opérations, et non celui du développeur.

Un meilleur argument des bases NoSQL est le côté schemaless, permettant d’enregistrer des documents sans trop se soucier de leur cohérence, de leur contenu, sans devoir réfléchir par avance à un format de données aussi rigide que peut l’être une table SQL. La flexibilité est un atout, mais il faut être conscient que le manque de cohérence ou de rigueur se paie. forcément plus tard.

La « mode » du NoSQL est un peu retombée ces dernières années. En effet, les bases de données classiques ont profité des progrès matériels (disques NVMe et nombreux cœurs sur un serveur, par exemple) ; la parallélisation est mieux gérée : par requête, sur des secondaires, voir avec du sharding; et les algorithmes se sont améliorés. Tout cela permet de procéder à des tâches de plus en plus lourdes tout en conservant les avantages d’un SGBDR : SQL standardisé, verrous bien gérés, cohérence forte des données.

Enfin, les SGBDR ont intégré quelques fonctionnalités NoSQL, en premier lieu le stockage des documents JSON, ce qui permet de les utiliser là où ils sont pertinents (schémas flexibles).

Le choix d’un outil résulte donc d’un arbitrage entre différentes priorités.

Le modèle relationnel garantit l’indépendance entre la vue logique et la vue physique. L’utilisateur ne se préoccupe que des objets logiques (pour lire ou écrire des enregistrements), et le SGBD traduit la demande exprimée avec des objets logiques (tables, vues, fonctions…) en actions à réaliser sur des objets physiques (fichiers, sockets, mémoire…).

Les objets logiques sont appelés des relations. Ce sont généralement les tables, mais il existe d’autres objets qui sont aussi des relations (les vues par exemple, mais aussi les index et les séquences).

Le modèle relationnel se base sur la théorie des ensembles. Chaque relation contient un ensemble de données et ces différents ensembles peuvent se joindre suivant certaines conditions.

La logique des prédicats est un sous-ensemble de la théorie des ensembles. Elle sert à exprimer des formules logiques qui permettent de filtrer les ensembles de départ pour créer de nouveaux ensembles (autrement dit, filtrer les enregistrements d’une relation).

Cependant, tout élément d’un enregistrement n’est pas forcément connu à un instant t. Les filtres et les jointures doivent donc gérer trois états lors d’un calcul de prédicat : vrai, faux ou « inconnu ».

Pour un humain ou pour l’application, NULL peut signifier : « inconnu », « non pertinent » ou « non encore renseigné ».

Les propriétés ACID sont le fondement même de toute bonne base de données. Il s’agit de l’acronyme des quatre règles que toute transaction (c’est-à-dire une suite d’ordres modifiant les données) doit respecter :

• A : Une transaction est appliquée en « tout ou rien ».
• C : Une transaction amène la base d’un état stable à un autre.
• I : Les transactions n’agissent pas les unes sur les autres.
• D : Une transaction validée sera conservée de manière permanente.

Les bases de données relationnelles les plus courantes depuis des décennies (PostgreSQL bien sûr, mais aussi Oracle, MySQL, SQL Server, SQLite…) se basent sur ces principes, même si elles font chacune des compromis différents suivant leurs cas d’usage, les compromis acceptés à chaque époque avec la performance et les versions.

Atomicité :

Une transaction doit être exécutée entièrement ou pas du tout, et surtout pas partiellement, même si elle est longue et complexe, même en cas d’incident majeur sur la base de données. L’exemple basique est une transaction bancaire : le montant d’un virement doit être sur un compte ou un autre, et en cas de problème ne pas disparaître ou apparaître en double. Ce principe garantit que les données modifiées par des transactions valides seront toujours visibles dans un état stable, et évite nombre de problèmes fonctionnels comme techniques.

Cohérence :

Un état cohérent respecte les règles de validité définies dans le modèle, c’est-à-dire les contraintes définies dans le modèle : types, plages de valeurs admissibles, unicité, liens entre tables (clés étrangères), etc. Le non-respect de ces règles par l’applicatif entraîne une erreur et un rejet de la transaction.

Isolation :

Des transactions simultanées doivent agir comme si elles étaient seules sur la base. Surtout, elles ne voient pas les données non validées des autres transactions. Ainsi une transaction peut travailler sur un état stable et fixe, et durer assez longtemps sans risque de gêner les autres transactions.

Il existe plusieurs « niveaux d’isolation » pour définir précisément le comportement en cas de lectures ou écritures simultanées sur les mêmes données et pour arbitrer avec les contraintes de performances ; le niveau le plus contraignant exige que tout se passe comme si toutes les transactions se déroulaient successivement.

Durabilité :

Une fois une transaction validée par le serveur (typiquement : COMMIT ne retourne pas d’erreur, ce qui valide la cohérence et l’enregistrement physique), l’utilisateur doit avoir la garantie que la donnée ne sera pas perdue ; du moins jusqu’à ce qu’il décide de la modifier à nouveau. Cette garantie doit valoir même en cas d’événement catastrophique : plantage de la base, perte d’un disque… C’est donc au serveur de s’assurer autant que possible que les différents éléments (disque, système d’exploitation…) ont bien rempli leur office. C’est à l’humain d’arbitrer entre le niveau de criticité requis et les contraintes de performances et de ressources adéquates (et fiables) à fournir à la base de données.

NoSQL :

À l’inverse, les outils de la mouvance (« NoSQL », par exemple MongoDB ou Cassandra), ne fournissent pas les garanties ACID. C’est le cas de la plupart des bases non-relationnelles, qui reprennent le modèle BASE (Basically Available, Soft State, Eventually Consistent, soit succintement : disponibilité d’abord ; incohérence possible entre les réplicas ; cohérence… à terme, après un délai). Un intérêt est de débarrasser le développeur de certaines lourdeurs apparentes liées à la modélisation assez stricte d’une base de données relationnelle. Cependant, la plupart des applications ont d’abord besoin des garanties de sécurité et cohérence qu’offrent un moteur transactionnel classique, et la décision d’utiliser un système ne les garantissant pas ne doit pas être prise à la légère ; sans parler d’autres critères comme la fragmentation du domaine par rapport au monde relationnel et son SQL (à peu près) standardisé. Avec le temps, les moteurs transactionnels ont acquis des fonctionnalités qui faisaient l’intérêt des bases NoSQL (en premier lieu la facilité de réplication et le stockage de JSON), et ces dernières ont tenté d’intégrer un peu plus de sécurité dans leur modèle.

Le langage SQL a été normalisé par l’ANSI en 1986 et est devenu une norme ISO internationale en 1987. La norme a subi plusieurs évolutions dans le but d’ajouter des fonctionnalités correspondantes aux attentes de l’industrie logicielle. Parmi ces améliorations, notons l’intégration de quelques fonctionnalités objets pour le modèle relationnel-objet (SQL-99), ou les puissantes « fonctions de fenêtrage » (SQL:2003, SQL:2008). Le dernier standard est SQL:2023.

Hormis la syntaxe, il n’y a pas de règles strictes concernant l’écriture de requêtes SQL (majuscules, minuscules, espaces…). Il faut néanmoins avoir à l’esprit qu’il s’agit d’un langage à part entière et, au même titre que ce qu’un développeur fait avec n’importe quel code source, il convient de l’écrire de façon lisible.

Cet exemple est tiré du forum postgresql.fr. Quelle est la requête la plus lisible ?

• celle-ci ?

select  groupeid,datecreationitem from itemagenda where typeitemagenda = 5  and
groupeid in(12225,12376) and datecreationitem > now() order by groupeid,
datecreationitem ;

• ou celle-ci ?

SELECT groupeid, datecreationitem
  FROM itemagenda
 WHERE typeitemagenda = 5
   AND groupeid IN (12225,12376)
   AND datecreationitem > now()
 ORDER BY groupeid, datecreationitem ;

• ou encore celle-ci, réécrite avec pgFormatter ?

SELECT
    groupeid,
    datecreationitem
FROM
    itemagenda
WHERE
    typeitemagenda = 5
    AND groupeid IN (12225, 12376)
    AND datecreationitem > now()
ORDER BY
    groupeid,
    datecreationitem;

La base de données exécutera sans souci l’un comme l’autre code, avec bien sûr le même résultat.

Sans se tenir forcément à un format précis, il convient surtout d’adopter un style lisible, et cohérent avec le code SQL existant.

Une requête SQL peut être commentée au même titre qu’un programme standard.

Le marqueur -- permet de signifier à l’analyseur syntaxique que le reste de la ligne est commenté, il n’en tiendra donc pas compte dans l’analyse de la requête.

Un commentaire peut aussi se présenter sous la forme d’un bloc de commentaire, le bloc pouvant occuper plusieurs lignes :

/* Ceci est un commentaire
   sur plusieurs
   lignes
*/

Aucun des éléments compris entre le marqueur de début de bloc /* et le marqueur de fin de bloc */ ne sera pris en compte. Certains SGBDR propriétaires utilisent ces commentaires pour y placer des informations (appelées parfois hints) qui influencent le comportement de l’optimiseur, mais PostgreSQL ne possède pas nativement ce genre de mécanisme.

Le langage SQL est divisé en quatre sous-ensembles qui ont chacun un but différent.

Les ordres DDL (pour Data Definition Language) permettent de définir les structures de données. On y retrouve les ordres suivants :

• CREATE : crée un objet ;
• ALTER : modifie la définition d’un objet ;
• DROP : supprime un objet ;
• TRUNCATE : vide un objet ;
• COMMENT : ajoute un commentaire sur un objet.

Les ordres DML (pour Data Manipulation Language) permettent l’accès et la modification des données. On y retrouve les ordres suivants :

• SELECT : lit les données d’une ou plusieurs tables ;
• INSERT : ajoute des données dans une table ;
• UPDATE : modifie les données d’une table ;
• MERGE : ajoute ou met à jour une ligne selon une condition ;
• DELETE : supprime les données d’une table.

Les ordres DCL (pour Data Control Language) permettent de contrôler l’accès aux données. Ils permettent plus précisément de donner ou retirer des droits à des utilisateurs ou des groupes sur les objets de la base de données :

• GRANT : donne un droit d’accès à un rôle sur un objet
• REVOKE : retire un droit d’accès d’un rôle sur un objet

Enfin, les ordres TCL (pour Transaction Control Language) permettent de contrôler les transactions :

• BEGIN : ouvre une transaction ;
• COMMIT : valide les traitements d’une transaction ;
• ROLLBACK : annule les traitements d’une transaction ;
• SAVEPOINT : crée un point de reprise dans une transaction ;
• SET TRANSACTION : modifie les propriétés d’une transaction en cours.

Les ordres BEGIN et COMMIT sont souvent implicites dans le cas d’ordres isolés, si l’« autocommit » est activé. Vous devez encadrer manuellement vos commandes avec BEGIN ; et COMMIT ; pour créer une transaction de plus d’un ordre. Le mode d’autocommit dépend en fait de l’outil client, et psql a un paramètre autocommit à on par défaut. Mais ce n’est pas forcément le cas sur votre configuration précise, et d’autres bases de données peuvent avoir un comportement par défaut inverse.

Le ROLLBACK est implicite en cas de sortie brutale (plantage…).

Noter que, contrairement à d’autres bases, PostgreSQL n’effectue pas de COMMIT implicite sur certaines opérations : les ordres CREATE TABLE, DROP TABLE, TRUNCATE TABLE… sont transactionnels, n’effectuent aucun COMMIT et peuvent être annulés par ROLLBACK.

La lecture des données se fait via l’ordre SELECT. Il permet de récupérer des données d’une ou plusieurs tables (il faudra dans ce cas joindre les tables). Il permet aussi de faire appel à des fonctions stockées en base.

L’ordre SELECT est composé de différents éléments dont la plupart sont optionnels. L’exemple de syntaxe donné ici n’est pas complet.

La syntaxe complète de l’ordre SELECT est disponible dans le manuel de PostgreSQL.

La liste de sélection décrit le format de la table virtuelle qui est retournée par l’ordre SELECT. Les types de données des colonnes retournées seront conformes au type des éléments donnés dans la liste de sélection.

Les exemples ci-dessous utilisent la base de données cave qu’il vous faudra peut-être installer. La base cave (dump de 2,6 Mo, pour 71 Mo sur le disque au final) peut être téléchargée et restaurée ainsi :

curl -kL https://dali.bo/tp_cave -o cave.dump
psql -c "CREATE ROLE caviste LOGIN PASSWORD 'caviste'"
psql -c "CREATE DATABASE cave OWNER caviste"
pg_restore -d cave cave.dump
# NB : une erreur sur un schéma 'public' existant est normale

La liste de sélection décrit le format de la table virtuelle qui est retournée par l’ordre SELECT. Cette liste est composée d’expressions séparées par une virgule.

Chaque expression peut être une simple constante, peut faire référence à des colonnes d’une table lue par la requête, et peut être un appel à une fonction.

Une expression peut être plus complexe. Par exemple, elle peut combiner plusieurs constantes et/ou colonnes à l’aide d’opérations. Parmi les opérations les plus classiques, les opérateurs arithmétiques classiques sont utilisables pour les données numériques. L’opérateur de concaténation permet de concaténer des chaînes de caractères.

L’expression d’une colonne peut être une constante :

SELECT 1;

 ?column?
----------
        1

Elle peut aussi être une référence à une colonne d’une table :

SELECT appellation.libelle
  FROM appellation;

Comme il n’y a pas d’ambiguïté avec la colonne libelle, la référence de la colonne appellation.libelle peut être simplifiée en libelle :

SELECT libelle
  FROM appellation;

Le SGBD saura déduire la table et la colonne mises en œuvre dans cette requête. Il faudra néanmoins utiliser la forme complète table.colonne si la requête met en œuvre des tables qui possèdent des colonnes qui portent des noms identiques.

Une requête peut sélectionner plusieurs colonnes. Dans ce cas, les expressions de colonnes sont définies sous la forme d’une liste dont chaque élément est séparé par une virgule :

SELECT id, libelle, region_id
  FROM appellation;

Le joker * permet de sélectionner l’ensemble des colonnes d’une table, elles apparaîtront dans leur ordre physique (attention si l’ordre change !).

SELECT *
  FROM appellation;

Si une requête met en œuvre plusieurs tables, on peut choisir de retourner toutes les colonnes d’une seule table :

SELECT appellation.*
  FROM appellation;

Enfin, on peut récupérer un tuple entier de la façon suivante :

SELECT appellation
  FROM appellation;

Une expression de colonne peut également être une opération, par exemple une addition :

SELECT 1 + 1;

 ?column?
----------
        2
(1 row)

Ou une soustraction :

SELECT annee, nombre - 10
  FROM stock;

Afin de pouvoir nommer de manière adéquate les colonnes du résultat d’une requête SELECT, le mot clé AS permet de définir un alias de colonne. Cet alias sera utilisé dans le résultat pour nommer la colonne en sortie :

SELECT 1 + 1 AS somme;

 somme
-------
     2
(1 row)

Cet alias n’est pas utilisable dans le reste de la requête (par exemple dans la clause WHERE).

Par défaut, SELECT retourne tous les résultats d’une requête. Parfois, des doublons peuvent se présenter dans le résultat. La clause DISTINCT permet de les éviter en réalisant un dédoublonnage des données avant de retourner le résultat de la requête.

Il faut néanmoins faire attention à l’utilisation systématique de la clause DISTINCT. En effet, elle entraîne une déduplication systématique des données juste avant de retourner les résultats de la requête, ce qui va souvent consommer de la ressource mémoire, voire de la ressource disque si le volume de données à trier est important. De plus, cela va augmenter le temps de réponse de la requête du fait de cette opération supplémentaire.

En règle générale, la clause DISTINCT devient inutile lorsqu’elle doit trier un ensemble qui contient des colonnes qui sont déjà uniques. Si une requête récupère une clé primaire, les données sont uniques par définition. Le SELECT DISTINCT sera alors transformé en simple SELECT.

Les constantes et valeurs des colonnes peuvent être dérivées selon le type des données manipulées.

Les données numériques peuvent être dérivées à l’aide des opérateurs arithmétiques standards : +, -, /, *. Elles peuvent faire l’objet d’autres calculs à l’aide de fonctions internes et de fonctions définies par l’utilisateur.

La requête suivante permet de calculer le volume total en litres de vin disponible dans le stock du caviste :

SELECT SUM(c.contenance * s.nombre)
  FROM stock s
  JOIN contenant c
    ON (contenant_id=c.id);

Les données de type chaînes de caractères peuvent être concaténées à l’aide de l’opérateur dédié ||. Cet opérateur permet de concaténer deux chaînes de caractères mais également des données numériques avec une chaîne de caractères.

Dans la requête suivante, l’opérateur de concaténation est utilisé pour ajouter l’unité. Le résultat est ainsi implicitement converti en chaîne de caractères.

SELECT SUM(s.contenance * s.nombre) || ' litres'
  FROM stock AS s
  JOIN contenant c
    ON (contenant_id=c.id);

De manière générale, il n’est pas recommandé de réaliser les opérations de formatage des données dans la base de données. La base de données ne doit servir qu’à récupérer les résultats, le formatage étant assuré par l’application.

Différentes fonctions sont également applicables aux chaînes de caractères, de même qu’aux autres types de données.

Parmi les fonctions les plus couramment utilisées, la fonction now() permet d’obtenir la date et l’heure courante. Elle ne prend aucun argument. Elle est souvent utilisée, notamment pour affecter automatiquement la valeur de l’heure courante à une colonne.

La fonction age(timestamp) permet de connaître l’âge d’une date par rapport à la date courante.

La fonction char_length(varchar) permet de connaître la longueur d’une chaîne de caractère.

Enfin, la fonction count(*) permet de compter le nombre de lignes. Il s’agit d’une fonction d’agrégat, il n’est donc pas possible d’afficher les valeurs d’autres colonnes sans faire appel aux capacités de regroupement des lignes de SQL.

Exemples

Affichage de l’heure courante (noter la présence du fuseau horaire) :

SELECT now();
             now
------------------------------
  2025-08-04 18:03:08.396493+02

Affichage de l’âge du 1ᵉʳ janvier 2000 :

SELECT age(date '2000-01-01');
          age           
------------------------
 25 years 7 mons 3 days

Affichage de la longueur de la chaîne « Dalibo » :

SELECT char_length('Dalibo');

 char_length 
-------------
           6

Affichage du nombre de lignes de la table vin :

SELECT count(*) FROM vin;

 count
-------
  6067

La clause FROM permet de lister les tables qui sont mises en œuvres dans la requêtes SELECT. Il s’agit souvent d’une table physique ou d’une vue, mais ce peut être aussi une vue matérialisée ou une sous-requête. Le résultat de leur lecture sera une table du point de vue de la requête qui la met en œuvre.

Plusieurs tables peuvent être précisées ici, généralement pour les associer avec une jointure.

De la même façon qu’on peut créer des alias de colonnes, on peut créer des alias de tables. La table sera ensuite référencée uniquement par cet alias dans la requête. Elle ne pourra plus être référencée par son nom réel. L’utilisation du nom réel provoquera d’ailleurs une erreur.

Le mot clé AS permet de définir un alias de table. Le nom réel de la table se trouve à gauche, l’alias se trouve à droite. L’exemple suivant définit un alias reg sur la table region :

SELECT id, libelle
  FROM region AS reg;

Le mot clé AS est optionnel :

SELECT id, libelle
  FROM region reg;

La requête suivante montre l’utilisation d’un alias pour les deux tables mises en œuvre dans la requête. La table stock a pour alias s et la table contenant a pour alias c. Les deux tables possèdent toutes les deux une colonnes id, ce qui peut poser une ambiguïté dans la clause de jointure (ON (contenant_id=c.id)). La condition de jointure portant sur la colonne contenant_id de la table stock, son nom est unique et ne porte pas à ambiguïté. La condition de jointure porte également sur la colonne id de table contenant, il faut préciser le nom complet de la colonne en utilisant le préfixe c pour la nommer : c.id.

SELECT SUM(c.contenance * s.nombre) AS volume_total
  FROM stock s
  JOIN contenant c
    ON (contenant_id=c.id);

Avec PostgreSQL, les noms des objets sont automatiquement convertis en minuscule, sauf s’ils sont englobés entre des guillemets doubles. Si jamais ils sont créés avec une casse mixte en utilisant les guillemets doubles, chaque appel à cet objet devra utiliser la bonne casse et les guillemets doubles.

Même si c’est parfaitement possible, il est préférable de ne pas utiliser d’accents ou de caractères exotiques dans les noms des objets, car il y a parfois des surprises en fonction des outils et systèmes d’exploitation utilisés. Les noms de tables et de champs sont généralement masqués par les outils de requêtage, ou peuvent être aliasés pour la présentation.

Dans les données par contre, rien ne vous interdit d’utiliser tout ce que permet l’encodage Unicode :

SELECT nom AS "Nom", prenom as "Prénom"
  FROM eleves ;

      Nom       |   Prénom    
----------------+-------------
 L'Huillier     | Jean-Pierre
 Sigurðardóttir | Þór
 Einarsson      | Björn

La clause WHERE permet de définir des conditions de filtrage des données. Ces conditions de filtrage sont appelées des prédicats.

Après le traitement de la clause FROM, chaque ligne de la table virtuelle dérivée est vérifiée avec la condition de recherche. Si le résultat de la vérification est positif (true), la ligne est conservée dans la table de sortie, sinon (c’est-à-dire si le résultat est faux ou nul) la ligne est ignorée.

La condition de recherche référence typiquement au moins une colonne de la table générée dans la clause FROM ; ceci n’est pas requis mais, dans le cas contraire, la clause WHERE n’aurait aucune utilité.

Un prédicat est composé d’une expression qui est soumise à un opérateur de prédicat pour être éventuellement comparé à une autre expression. L’opérateur de prédicat retourne alors true si la condition est vérifiée ou false si elle ne l’est pas, ou NULL si son résultat ne peut être calculé.

Les opérateurs de comparaison sont les opérateurs de prédicats les plus souvent utilisés. L’opérateur d’égalité = peut être utilisé pour vérifier l’égalité de l’ensemble des types de données supportés par PostgreSQL. Il faudra faire attention à ce que les données comparées soient de même type.

L’opérateur de différence != peut aussi s’écrire <>.

L’opérateur de négation NOT permet d’inverser la véracité des prédicats. Un prédicat faux retournera donc vrai et inversement. La clause NOT se place devant l’expression entière.

Exemples

Sélection de la région dont l’identifiant est égal à 3 (et ensuite différent de 3) :

SELECT *
  FROM region
 WHERE id = 3;

SELECT *
  FROM region
 WHERE NOT id = 3;

Les opérateurs logiques OR et AND permettent de combiner plusieurs prédicats dans la clause WHERE.

L’opérateur OR est un OU logique. Il retourne vrai si au moins un des deux prédicats combinés est vrai. L’opérateur AND est un ET logique. Il retourne vrai si et seulement si les deux prédicats combinés sont vrais.

Au même titre qu’une multiplication ou une division sont prioritaires sur une addition ou une soustraction dans un calcul, l’évaluation de l’opérateur AND est prioritaire sur celle de l’opérateur OR. Et, tout comme dans un calcul, il est possible de protéger les opérations prioritaires en les encadrant de parenthèses.

Exemples

Dans le stock, affiche les vins dont le nombre de bouteilles est inférieur à 2 ou supérieur à 16 :

SELECT *
  FROM stock
 WHERE nombre < 2
    OR nombre > 16;

L’opérateur LIKE permet de réaliser une recherche simple sur motif. La chaîne exprimant le motif de recherche peut utiliser deux caractères joker : _ et %. Le caractère _ prend la place d’un caractère inconnu, qui doit toujours être présent. Le caractère % est un joker qui permet d’exprimer que PostgreSQL doit trouver entre 0 et plusieurs caractères.

Exploiter la clause LIKE avec un motif sans joker ne présente pas d’intérêt. Il est préférable dans ce cas d’utiliser l’opérateur d’égalité.

Le mot clé ESCAPE 'c' permet de définir un caractère d’échappement pour protéger les caractères _ et % qui font légitimement partie de la chaîne de caractère du motif évalué. Lorsque PostgreSQL rencontre le caractère d’échappement indiqué, les caractères _ et % seront évalués comme étant les caractères _ et % et non comme des jokers.

L’opérateur LIKE dispose d’une déclinaison qui n’est pas sensible à la casse. Il s’agit de l’opérateur ILIKE.

Exemples

Création d’un jeu d’essai :

CREATE TABLE motif (chaine varchar(30));
INSERT INTO motif (chaine) VALUES ('Durand'), ('Dupont'), ('Dupond'),
    ('Dupon'), ('Dupuis');

Toutes les chaînes commençant par la suite de caractères Dur :

SELECT * FROM motif WHERE chaine LIKE 'Dur%';
 chaine
--------
 Durand

Toutes les chaînes terminant par d :

SELECT * FROM motif WHERE chaine LIKE '%d';
 chaine
--------
 Durand
 Dupond

Toutes les chaînes qui commencent par Dupon suivi d’un caractère inconnu. La chaîne Dupon devrait être ignorée :

SELECT * FROM motif WHERE chaine LIKE 'Dupon_';
   chaine
------------
 Dupont
 Dupond

La clause IN permet de vérifier que l’expression de gauche est égale à une valeur présente dans l’expression de droite, qui est une liste d’expressions. La négation peut être utilisée en utilisant la construction NOT IN.

L’opérateur BETWEEN permet de vérifier que la valeur d’une expression est comprise entre deux bornes. Par exemple, l’expression valeur BETWEEN 1 AND 10 revient à exprimer la condition suivante : valeur >= 1 AND valeur<= 10. La négation peut être utilisée en utilisant la construction NOT BETWEEN.

Exemples

Recherche les chaînes qui sont présentes dans la liste IN :

SELECT * FROM motif WHERE chaine IN ('Dupont', 'Dupond', 'Ducobu');

 chaine
--------
 Dupont
 Dupond

La clause ORDER BY permet de trier les lignes du résultat d’une requête selon une ou plusieurs expressions combinées.

L’expression de tri la plus simple est le nom d’une colonne. Dans ce cas, les lignes seront triées selon les valeurs de la colonne indiquée, et par défaut dans l’ordre ascendant, c’est-à-dire de la valeur la plus petite à la plus grande pour une donnée numérique ou temporelle, et dans l’ordre alphabétique pour une donnée textuelle.

Les lignes peuvent être triées selon une expression très complexe, par exemple avec une fonction d’un calcul sur plusieurs colonnes.

L’ordre de tri peut être inversé à l’aide de la clause DESC qui permet un tri dans l’ordre descendant, donc de la valeur la plus grande à la plus petite (ou alphabétique inverse le cas échéant).

La clause NULLS permet de contrôler l’ordre d’apparition des valeurs NULL. La clause NULLS FIRST permet de faire apparaître d’abord les valeurs NULL puis les valeurs non NULL selon l’ordre de tri. La clause NULLS LAST permet de faire apparaître d’abord les valeurs non NULL selon l’ordre de tri suivies par les valeurs NULL. Si cette clause n’est pas précisée, alors PostgreSQL utilise implicitement NULLS LAST dans le cas d’un tri ascendant (ASC, par défaut) ou NULLS FIRST dans le cas d’un tri descendant (DESC, par défaut).

Exemples

Tri de la table region selon le nom de la région :

SELECT *
  FROM region
 ORDER BY libelle;

Tri de la table stock selon le nombre de bouteille, dans l’ordre décroissant :

SELECT *
  FROM stock
 ORDER BY nombre DESC;

Enfin, la clause COLLATE permet d’influencer sur l’ordre de tri des chaînes de caractères.

La clause OFFSET permet d’exclure les n premières lignes du résultat. Toutes les autres lignes sont ramenées.

La clause FETCH permet de limiter le résultat d’une requête. La requête retournera au maximum n lignes de résultats. Elle en retournera moins, voire aucune, si la requête ne peut ramener suffisamment de lignes. La clause FIRST ou NEXT est obligatoire, mais le choix de l’une ou l’autre n’a aucune conséquence sur le résultat.

La clause FETCH est synonyme de la clause LIMIT. Mais LIMIT est une clause propre à PostgreSQL et quelques autres SGBD. Il est recommandé d’utiliser FETCH pour se conformer au standard.

Ces deux opérations peuvent être combinées. La norme impose de faire apparaître la clause OFFSET avant la clause FETCH. PostgreSQL permet néanmoins d’exprimer ces clauses dans un ordre différent.

Il faut faire attention au fait que ces fonctions ne permettent pas d’obtenir des résultats stables si les données ne sont pas triées explicitement. Rappelons que le standard SQL ne garantit en aucune façon l’ordre des résultats à moins d’employer la clause ORDER BY.

Exemples

La fonction generate_series() permet de générer une suite de valeurs numériques. Par exemple, une suite comprise entre 1 et 10 :

SELECT * FROM generate_series(1, 10);

 generate_series
-----------------
               1
(…)
              10
(10 rows)

La clause FETCH permet donc de limiter le nombre de lignes du résultats :

SELECT * FROM generate_series(1, 10) FETCH FIRST 5 ROWS ONLY;

 generate_series
-----------------
               1
               2
               3
               4
               5
(5 rows)

La clause LIMIT donne un résultat équivalent :

SELECT * FROM generate_series(1, 10) LIMIT 5;

 generate_series
-----------------
               1
               2
               3
               4
               5
(5 rows)

La clause OFFSET 4 permet d’exclure les quatre premières lignes et de retourner les autres lignes du résultat :

SELECT * FROM generate_series(1, 10) OFFSET 4;

 generate_series
-----------------
               5
               6
               7
               8
               9
              10
(6 rows)

Les clauses LIMIT et OFFSET peuvent être combinées pour ramener les deux lignes en excluant les quatre premières :

SELECT * FROM generate_series(1, 10) OFFSET 4 LIMIT 2;

 generate_series
-----------------
               5
               6
(2 rows)

Il est possible d’utiliser plusieurs tables dans une requête SELECT. Lorsque c’est le cas, et sauf cas particulier, on fera correspondre les lignes d’une table avec les lignes d’une autre table selon certains critères. Cette mise en correspondance s’appelle une jointure et les critères de correspondances s’appellent une condition de jointure.

Si aucune condition de jointure n’est donnée, chaque ligne de la première table est mise en correspondance avec toutes les lignes de la seconde table. C’est un produit cartésien. En général, un produit cartésien n’est pas souhaitable et est généralement le résultat d’une erreur de conception de la requête.

Exemples

Création d’un jeu de données simple :

CREATE TABLE mere (id integer PRIMARY KEY, val_mere text);
CREATE TABLE fille (
    id_fille integer PRIMARY KEY,
    id_mere integer REFERENCES mere(id),
    val_fille text
);

INSERT INTO mere (id, val_mere) VALUES (1, 'mere 1');
INSERT INTO mere (id, val_mere) VALUES (2, 'mere 2');

INSERT INTO fille (id_fille, id_mere, val_fille) VALUES (1, 1, 'fille 1');
INSERT INTO fille (id_fille, id_mere, val_fille) VALUES (2, 1, 'fille 2');

Pour procéder à une jointure entre les tables mere et fille, les identifiants id_mere de la table fille doivent correspondre avec les identifiants id de la table mere :

SELECT * FROM mere, fille
 WHERE mere.id = fille.id_mere;
 id | val_mere | id_fille | id_mere | val_fille
----+----------+----------+---------+-----------
  1 | mere 1   |        1 |       1 | fille 1
  1 | mere 1   |        2 |       1 | fille 2
(2 rows)

Un produit cartésien est créé en omettant la condition de jointure, le résultat n’a plus de sens :

SELECT * FROM mere, fille;
 id | val_mere | id_fille | id_mere | val_fille
----+----------+----------+---------+-----------
  1 | mere 1   |        1 |       1 | fille 1
  1 | mere 1   |        2 |       1 | fille 2
  2 | mere 2   |        1 |       1 | fille 1
  2 | mere 2   |        2 |       1 | fille 2
(4 rows)

PostgreSQL propose l’ensemble des types de données du standard SQL, à l’exception du type BLOB qui a toutefois un équivalent. Mais PostgreSQL a été conçu pour être extensible et permet de créer facilement des types de données spécifiques. C’est pourquoi PostgreSQL propose un certain nombre de types de données spécifiques qui peuvent être intéressants.

On utilise des types de données pour représenter une information de manière contrainte et cohérente. Les valeurs possibles d’une donnée vont dépendre de son type. Un entier long ne permet par exemple pas de coder des valeurs décimales. De la même façon, un type entier ne permet pas de représenter une chaîne de caractère, mais l’inverse est possible.

L’intérêt du typage des données est qu’il permet également à la base de données de valider les données manipulées. Ainsi un entier (integer) permet de représenter des valeurs comprises entre -2 147 483 648 et 2 147 483 647. Si l’utilisateur tente d’insérer une donnée qui dépasse les capacités de ce type de données, une erreur lui sera retournée. On retrouve ainsi la notion d’intégrité des données. Comme pour les langages de programmation fortement typés, cela permet de détecter davantage d’erreurs, plus tôt : à la compilation dans les langages typés, ou ici des la première exécution d’une requête, plutôt que plus tard, quand une chaîne de caractère ne pourra pas être convertie à la volée en entier par exemple.

Le choix d’un type de données va également influencer la façon dont les données sont représentées. En effet, toute donnée a une représentation textuelle, une représentation en mémoire et sur disque. Ainsi, un integer est représenté en interne sous la forme d’une suite de 4 octets, manipulables directement par le processeur, alors que sa représentation textuelle est une suite de caractères. Cela a une implication forte sur les performances de la base de données.

Le type de données choisi permet également de déterminer les opérations que l’on pourra appliquer. Tous les types de données permettent d’utiliser des opérateurs qui leur sont propres. Ainsi il est possible d’additionner des entiers, de concaténer des chaînes de caractères, etc. Si une opération ne peut être réalisée nativement sur le type de données, il faudra utiliser des conversions coûteuses. Vaut-il mieux additionner deux entiers issus d’une conversion d’une chaîne de caractère vers un entier ou additionner directement deux entiers ? Vaut-il mieux stocker une adresse IP avec un varchar ou avec un type de données dédié ?

Il est à noter que l’utilisateur peut contrôler lui-même certains types de données paramétrés. Le paramètre représente la longueur ou la précision du type de données. Ainsi, un type varchar(15) permettra de représenter des chaînes de caractères de 15 caractères maximum.

Les types de données standards permettent de traiter la plupart des situations qui peuvent survenir. Dans certains cas, il peut être nécessaire de faire appel aux types spécifiques à PostgreSQL, par exemple pour stocker des adresses IP avec le type spécifique et bénéficier par la même occasion de toutes les classes d’opérateurs qui permettent de manipuler simplement ce type de données.

Et si cela ne s’avère pas suffisant, PostgreSQL permet à l’utilisateur de créer lui-même ses propres types de données, ainsi que les classes d’opérateurs et fonctions permettant d’indexer ces données.

Le standard SQL propose des types standards pour stocker des chaînes de caractères (de taille fixe ou variable), des données numériques (entières, à virgule flottante) et des booléens.

La norme propose également des types standards pour stocker des éléments temporels (date, heure, la combinaison des deux avec ou sans fuseau horaire, intervalle).

D’utilisation plus rare, SQL permet également de stocker des chaînes de bit et des données validées au format XML. Le format JSON est de plus en plus courant.

Le type char(n) permet de stocker des chaînes de caractères de taille fixe, donnée par l’argument n. Si la chaîne que l’on souhaite stocker est plus petite que la taille donnée à la déclaration de la colonne, elle sera complétée par des espaces à droite. Si la chaîne que l’on souhaite stocker est trop grande, une erreur sera levée.

Le type varchar(n) permet de stocker des chaînes de caractères de taille variable. La taille maximale de la chaîne est donnée par l’argument n. Toute chaîne qui excédera cette taille ne sera pas prise en compte et générera une erreur. Les chaînes de taille inférieure à la taille limite seront stockées sans altérations.

La longueur de chaîne est mesurée en nombre de caractères sous PostgreSQL. Ce n’est pas forcément le cas dans d’autres SGBD, qui peuvent compter en octets. En effet, de nombreux caractères peuvent nécessiter plusieurs octets.

La norme SQL définit que les chaînes de caractères sont représentées encadrées de guillemets simples (caractère '). Le guillemet double (caractère ") ne peut être utilisé car il sert à protéger la casse des noms d’objets. PostgreSQL interprétera alors la chaîne comme un nom d’objet et générera une erreur.

Une représentation correcte d’une chaîne de caractères est donc de la forme suivante :

'chaîne de caractères'

Les caractères ' doivent être doublés s’ils apparaissent dans la chaîne :

'J''ai acheté des croissants'

Une extension de la norme par PostgreSQL permet d’utiliser les méta-caractères des langages tels que le C, par exemple \n pour un retour de ligne, \t pour une tabulation, etc. :

E'chaîne avec un retour \nde ligne et une \ttabulation'

Le standard SQL propose des types spécifiques pour stocker des entiers signés. Le type smallint permet de stocker des valeurs codées sur 2 octets, soit des valeurs comprises entre -32 768 et +32 767. Le type integer ou int, codé sur 4 octets, permet de stocker des valeurs comprises entre -2 147 483 648 et +2 147 483 647. Enfin, le type bigint, codé sur 8 octets, permet de stocker des valeurs comprises entre -9 223 372 036 854 775 808 et 9 223 372 036 854 775 807. Le standard SQL ne propose pas de stockage d’entiers non signés.

Le standard SQL permet de stocker des valeurs décimales en utilisant les types à virgules flottantes. Avant de les utiliser, il faut avoir à l’esprit que ces types de données ne permettent pas de stocker des valeurs exactes, des différences peuvent donc apparaître entre la donnée insérée et la donnée restituée. Le type real permet d’exprimer des valeurs à virgules flottantes sur 4 octets, avec une précision relative de six décimales. Le type double precision permet d’exprimer des valeurs à virgules flottantes sur huit octets, avec une précision relative de 15 décimales.

Beaucoup d’applications, notamment les applications financières, ne se satisfont pas de valeurs inexactes. Pour cela, le standard SQL propose le type numeric, ou son synonyme decimal, qui permet de stocker des valeurs exactes, selon la précision arbitraire donnée. Dans la déclaration numeric(precision, echelle), la partie precision indique combien de chiffres significatifs sont stockés, la partie echelle exprime le nombre de chiffres après la virgule. Au niveau du stockage, PostgreSQL ne permet pas d’insérer des valeurs qui dépassent les capacités du type déclaré. En revanche, si l’échelle de la valeur à stocker dépasse l’échelle déclarée de la colonne, alors sa valeur est simplement arrondie.

On peut aussi utiliser numeric sans aucune contrainte de taille, pour stocker de façon exacte n’importe quel nombre.

Au moins un chiffre doit être placé avant ou après le point décimal, s’il est utilisé. Au moins un chiffre doit suivre l’indicateur d’exponentiel (caractère e), s’il est présent. Il peut ne pas y avoir d’espaces ou d’autres caractères imbriqués dans la constante. Notez que tout signe + ou - en avant n’est pas forcément considéré comme faisant partie de la constante ; il est un opérateur appliqué à la constante. PostgreSQL accepte des _ intercalaires pour la lisibilité depuis sa version 16.

Une constante numérique contenant soit un point décimal soit un exposant est tout d’abord présumée du type integer si sa valeur est contenue dans le type integer (4 octets). Dans le cas contraire, il est présumé de type bigint si sa valeur entre dans un type bigint (8 octets). Dans le cas contraire, il est pris pour un type numeric. Les constantes contenant des points décimaux et/ou des exposants sont toujours présumées de type numeric.

Des notations hexadécimales, octales, binaires sont possibles :

SELECT 0xFF AS hexa, 0o377 AS octal, 0b1111_1111 AS binaire ;

 hexa | octal | binaire 
------+-------+---------
  255 |   255 |     255

Le type de données affecté initialement à une constante numérique est seulement un point de départ pour les algorithmes de résolution de types. Dans la plupart des cas, la constante sera automatiquement convertie dans le type le plus approprié suivant le contexte. Si nécessaire, vous pouvez forcer l’interprétation d’une valeur numérique sur un type de données spécifiques en la convertissant. Par exemple, vous pouvez forcer une valeur numérique à être traitée comme un type real (float4) de plusieurs manières différentes :

SELECT '1.23', 1.23 , REAL '1.23', cast ('1.23' AS real), '1.23'::float4 \gdesc

  Column  |  Type   
----------+---------
 ?column? | text
 ?column? | numeric
 float4   | real
 float4   | real
 float4   | real

La première syntaxe ne fonctionne que pour des constantes. La syntaxe :: se rencontre fréquemment et est très pratique, mais est propre à PostgreSQL. La norme SQL conseille l’opérateur cast.

• Documentation officielle :
• Conversions de type
• Constantes d’autres types

Le type boolean permet d’exprimer des valeurs booléennes, c’est-à-dire une valeur exprimant vrai ou faux. Comme tous les types de données en SQL, une colonne booléenne peut aussi ne pas avoir de valeur, auquel cas sa valeur sera NULL.

Un des intérêts des types booléens est de pouvoir écrire :

SELECT * FROM ma_table WHERE valide;
SELECT * FROM ma_table WHERE NOT consulte;

Le type date exprime une date. Ce type ne connaît pas la notion de fuseau horaire.

Le type time exprime une heure. Par défaut, il ne connaît pas la notion de fuseau horaire. En revanche, lorsque le type est déclaré comme time with time zone, il prend en compte un fuseau horaire. Mais cet emploi n’est pas recommandé. En effet, une heure convertie d’un fuseau horaire vers un autre pose de nombreux problèmes. En effet, le décalage horaire dépend également de la date : quand il est 6 h 00, heure d’été, à Paris, il est 21 h 00 sur la côte Pacifique aux États-Unis, mais encore à la date de la veille.

Le type timestamp permet d’exprimer une date et une heure. Par défaut, il ne connaît pas la notion de fuseau horaire. Lorsque le type est déclaré timestamp with time zone, il est adapté aux conversions d’heure d’un fuseau horaire vers un autre car le changement de date sera répercuté dans la composante date du type de données. Il est précis à la microseconde.

Le format de saisie et de restitution des dates et heures dépend du paramètre DateStyle. La documentation de ce paramètre permet de connaître les différentes valeurs possibles. Il reste néanmoins recommandé d’utiliser les fonctions de formatage de date qui permettent de rendre l’application indépendante de la configuration du SGBD.

La norme ISO (ISO-8601) impose le format de date « année-mois-jour ». La norme SQL est plus permissive et permet de restituer une date au format « jour/mois/année » si DateStyle est égal à 'SQL, DMY'.

SET datestyle = 'ISO, DMY';

SELECT current_timestamp;

              now
-------------------------------
 2017-08-29 16:11:58.290174+02

SET datestyle = 'SQL, DMY';

SELECT current_timestamp;

              now
--------------------------------
 29/08/2017 16:12:25.650716 CEST

Expression d’une date, forcément sans gestion du fuseau horaire :

DATE '2017-08-29'

Expression d’une heure sans fuseau horaire :

TIME '10:20:10'

Ou, en spécifiant explicitement l’absence de fuseau horaire :

TIME WITHOUT TIME ZONE '10:20:10'

Expression d’une heure, avec fuseau horaire invariant. Cette forme est déconseillée :

TIME WITH TIME ZONE '10:20:10' AT TIME ZONE 'CEST'

Expression d’un timestamp sans fuseau horaire :

TIMESTAMP '2017-08-29 10:20:10'

Ou, en spécifiant explicitement l’absence de fuseau horaire :

TIMESTAMP WITHOUT TIME ZONE '2017-08-29 10:20:10'

Expression d’un timestamp avec fuseau horaire, avec microseconde :

SELECT TIMESTAMP WITH TIME ZONE '2017-08-29 10:20:10.123321'
AT TIME ZONE 'Europe/Paris' ;

          timezone          
----------------------------
 2017-08-29 10:20:10.123321

Expression d’un intervalle d’une journée :

INTERVAL '1 day'

Il est possible de cumuler plusieurs expressions :

INTERVAL '1 year 1 day'

Les valeurs possibles sont :

• YEAR pour une année ;
• MONTH pour un mois ;
• DAY pour une journée ;
• HOUR pour une heure ;
• MINUTE pour une minute ;
• SECOND pour une seconde.

Le paramètre timezone du fichier de configuration postgresql.conf permet de positionner le fuseau horaire de l’instance PostgreSQL. Elle est initialisée par défaut en fonction de l’environnement du système d’exploitation.

Le fuseau horaire de l’instance peut également être défini au cours de la session à l’aide de la commande SET TIME ZONE.

La France métropolitaine utilise deux fuseaux horaires normalisés. Le premier, CET, correspond à Central European Time ou autrement dit à l’heure d’hiver en Europe centrale. Le second, CEST, correspond à Central European Summer Time, c’est-à-dire l’heure d’été en Europe centrale.

La liste des fuseaux horaires supportés est disponible dans la table système pg_timezone_names :

SELECT * FROM pg_timezone_names ;

               name               | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
 GB                               | BST    | 01:00:00   | t
 ROK                              | KST    | 09:00:00   | f
 Greenwich                        | GMT    | 00:00:00   | f
(…)

Il est possible de positionner le fuseau horaire au niveau de la session avec l’ordre SET TIME ZONE :

SET TIME ZONE "Europe/Paris";

SELECT now();

              now
-------------------------------
 2017-08-29 10:19:56.640162+02

SET TIME ZONE "Europe/Kiev";

SELECT now();

              now
-------------------------------
 2017-08-29 11:20:17.199983+03

Conversion implicite d’une donnée de type timestamp dans le fuseau horaire courant :

SET TIME ZONE "Europe/Kiev";

SELECT TIMESTAMP WITH TIME ZONE '2017-08-29 10:20:10 CEST';

      timestamptz
------------------------
 2017-08-29 11:20:10+03

ou encore (AT LOCAL TIME est disponible depuis PostgreSQL 17) :

SHOW timezone;

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

SELECT '2024-11-26 00:00:00 +11' AT LOCAL TIME ; -- minuit à Nouméas

        time         
---------------------
 2024-11-26 01:00:00

Conversion explicite d’une donnée de type timestamp dans un autre fuseau horaire :

SELECT '2017-08-29 06:00:00' AT TIME ZONE 'US/Pacific';

      timezone
---------------------
 28/08/2017 21:00:00

ou encore :

SELECT '2023-10-23 6:30:00+02'::timestamptz AT TIME ZONE 'Europe/London';

      timezone
---------------------
 2023-10-23 05:30:00

Les types bit et bit varying permettent de stocker des masques de bits. Le type bit(n) est à longueur fixe alors que le type bit varying(n) est à longueur variable mais avec un maximum de n bits.

Le type xml permet de stocker des documents XML. Par rapport à une chaîne de caractères simple, le type xml apporte la vérification de la structure du document XML ainsi que des fonctions de manipulations spécifiques (voir la documentation officielle).

Les types json et jsonb permettent de stocker des documents JSON. Ces deux types permettent de vérifier la structure du document JSON ainsi que des fonctions de manipulations spécifiques (voir la documentation officielle).

Sous PostgreSQL, on préférera de loin le type jsonb pour son stockage optimisé (en binaire), et ses fonctionnalités supplémentaires, notamment en terme d’indexation.

Les types smallserial, serial et bigserial permettent d’obtenir des fonctionnalités similaires aux types autoincrement rencontrés dans d’autres SGBD.

Néanmoins, ces types restent assez proches de la norme car ils définissent au final une colonne qui utilise un type et des objets standards. Selon le type dérivé utilisé, la colonne sera de type smallint, integer ou bigint. Une séquence sera également créée et la colonne prendra pour valeur par défaut la prochaine valeur de cette séquence.

Il est cependant préférable de passer par un type IDENTITY que par ces types dérivés.

Le type text est l’équivalent du type varchar mais sans limite de taille de la chaîne de caractère.

Les types standards ne sont pas toujours suffisants pour représenter certaines données. Comme tous ses concurrents, PostgreSQL propose des types de données supplémentaires pour répondre à certains besoins.

On notera le type bytea qui permet de stocker des objets binaires dans une table. Le type array permet de stocker des tableaux (de nombres, de dates, de chaînes…) et enum des énumérations.

Les types json et hstore permettent de stocker des documents non structurés dans la base de données. Le premier au format JSON, le second dans un format de type clé/valeur. Le type json a été complété par jsonb qui permet de stocker un document JSON binaire et optimisé, et d’accéder à une propriété sans désérialiser intégralement le document. Le type hstore est un type clé/valeur qui par rapport à JSON a l’intérêt de la simplicité.

Le type range permet de stocker des intervalles de données. Ces données sont ensuite manipulables par un jeu d’opérateurs dédiés et par le biais de méthodes d’indexation permettant d’accélérer les recherches.

PostgreSQL permet de créer ses propres types de données. Les usages les plus courants consistent à créer des types composites pour permettre à des fonctions de retourner des données sous forme tabulaire (retour de type SETOF).

L’utilisation du type énuméré (enum) nécessite aussi la création d’un type spécifique. Le type sera alors employé pour déclarer les objets utilisant une énumération.

Enfin, si l’on souhaite étendre les types intervalles (range) déjà disponibles, il est nécessaire de créer un type spécifique.

La création d’un type scalaire est bien plus marginale. Elle permet en effet d’étendre les types fournis par PostgreSQL mais nécessite d’avoir des connaissances fines des mécanismes de PostgreSQL. De plus, dans la majeure partie des cas, les types standards suffisent en général à résoudre les problèmes qui peuvent se poser à la conception.

Quant aux types tableaux, ils sont créés implicitement par PostgreSQL quand un utilisateur crée un type personnalisé.

Exemples

Utilisation d’un type enum :

CREATE TYPE arc_en_ciel AS ENUM (
    'red', 'orange', 'yellow', 'green', 'blue', 'purple'
);

CREATE TABLE test (id integer, couleur arc_en_ciel);

INSERT INTO test (id, couleur) VALUES (1, 'red');

INSERT INTO test (id, couleur) VALUES (2, 'pink');
ERROR:  invalid input value for enum arc_en_ciel: "pink"
LINE 1: INSERT INTO test (id, couleur) VALUES (2, 'pink');

Création d’un type interval float8_range :

CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);

Le standard SQL permet de traiter des ensembles d’enregistrements. Un enregistrement correspond à une ligne dans une relation. Il est possible de lire ces relations grâce à l’ordre SELECT.

Bases de données - de la modélisation au SQL

• Auteur : Laurent Audibert
• Éditeur : Ellipses
• ISBN : 978-2729851200

Ce livre présente les notions essentielles pour modéliser une base de données et utiliser le langage SQL pour utiliser les bases de données créées. L’auteur appuie ses exercices sur PostgreSQL.

SQL avancé : programmation et techniques avancées

• Auteur : Joe Celko
• Editeur : Vuibert
• ISBN : 978-2711786503

Ce livre est écrit par une personne ayant participé à l’élaboration du standard SQL. Il a souhaité montré les bonnes pratiques pour utiliser le SQL pour résoudre un certain nombre de problèmes de tous les jours. Le livre s’appuie cependant sur la norme SQL-92, voire SQL-89. L’édition anglaise SQL for Smarties est bien plus à jour. Pour les anglophones, la lecture de l’ensemble des livres de Joe Celko est particulièrement recommandée.

SQL : Au cœur des performances

• Auteur : Markus Winand
• Éditeur : auto-édité
• ISBN : 978-3950307832
• site Internet

Il s’agit du livre de référence sur les performances en SQL. Il dresse un inventaire des différents cas d’utilisation des index par la base de données, ce qui permettra de mieux prévoir l’indexation dès la conception. Ce livre s’adresse à un public avancé.

The Manga Guide to Databases

• Auteur : Takahashi, Mana, Azuma, Shoko
• Éditeur : No Starch Press
• ASIN : B00BUFN70E

The Art of SQL

• Auteur : Stéphane Faroult
• Éditeur : O’Reilly
• ISBN : 978-0-596-00894-9
• ISBN : 978-0-596-15971-9 (e-book)

Ce livre s’adresse également à un public avancé. Il présente également les bonnes pratiques lorsque l’on utilise une base de données.

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/s10_solutions.

Requêtes simples :

Afficher l’heure courante, au méridien de Greenwich.

Afficher la date et l’heure qu’il sera dans 1 mois et 1 jour.

Ajouter 1 au nombre de type réel ‘1.42’. Pourquoi ce résultat ? Quel type de données permet d’obtenir un résultat correct ?

Requêtes sur la base tpc :

Pour les questions suivantes, il faudra se connecter à la base de données tpc. La base tpc (dump de 31 Mo, pour 267 Mo sur le disque au final) et ses utilisateurs peuvent être installés comme suit :

curl -kL https://dali.bo/tp_tpc -o /tmp/tpc.dump
curl -kL https://dali.bo/tp_tpc_roles -o /tmp/tpc_roles.sql
# Exécuter le script de création des rôles
psql < /tmp/tpc_roles.sql
# Création de la base
createdb --owner tpc_owner tpc
# L'erreur sur un schéma 'public' existant est normale
pg_restore -d tpc /tmp/tpc.dump

Les mots de passe sont dans le script /tmp/tpc_roles.sql. Pour vous connecter :

$ psql -U tpc_admin -h localhost -d tpc

Le schéma suivant montre les différentes tables de la base :

Avec psql, pour changer de base, vous pouvez lancer la méta-commande suivante:

\c tpc

Afficher le contenu de la table pays en classant les pays dans l’ordre alphabétique.

Afficher les pays contenant la lettre a, majuscule ou minuscule. Plusieurs solutions sont possibles.

Afficher le nombre lignes de commandes (table lignes_commandes) dont la quantité commandée est comprise entre 5 et 10.

Pour chaque pays, afficher son nom et la région du monde dont il fait partie.

nom_pays            | nom_region
--------------------+-----------
 ALGÉRIE            | Afrique
(…)

Afficher le nombre total de clients français et allemands.

Sortie attendue :

count
-------
 12418

Afficher le numéro de commande et le nom du client ayant passé la commande. Seul un sous-ensemble des résultats sera affiché : les 20 premières lignes du résultat seront exclues et seules les 20 suivantes seront affichées. Il faut penser à ce que le résultat de cette requête soit stable entre plusieurs exécutions.

Sortie attendue :

numero_commande  |  nom_client
-----------------+--------------
              67 | Client112078
              68 | Client33842
(…)
             132 | Client18152

Afficher les noms et codes des pays qui font partie de la région « Europe ».

Sortie attendue :

nom_pays        | code_pays
----------------+-----------
ALLEMAGNE       | DE
(…)

Pour chaque pays, afficher une chaîne de caractères composée de son nom, suivi entre parenthèses de son code puis, séparé par une virgule, du nom de la région dont il fait partie.

Sortie attendue :

detail_pays
----------------------
 ALGÉRIE (DZ), Afrique
(…)

Pour les clients ayant passé des commandes durant le mois de janvier 2011, affichez les identifiants des clients, leur nom, leur numéro de téléphone et le nom de leur pays.

Sortie attendue :

client_id |     nom      |    telephone    |           nom_pays
----------+--------------+-----------------+-------------------------------
    83279 | Client83279  | 12-835-574-2048 | JAPON

Pour les dix premières commandes de l’année 2011, afficher son numéro, sa date ainsi que son âge.

Sortie attendue :

numero_commande | date_commande |            age
----------------+---------------+---------------------------
          54274 | 2011-01-01    | 5336 days 10:09:01.6222
(…)

Requêtes simples :

Afficher l’heure courante, au méridien de Greenwich.

SELECT now() AT TIME ZONE 'GMT';

Afficher la date et l’heure qu’il sera dans 1 mois et 1 jour.

SELECT now() + INTERVAL '1 month 1 day';

Ajouter 1 au nombre de type réel ‘1.42’. Pourquoi ce résultat ? Quel type de données permet d’obtenir un résultat correct ?

SELECT REAL '1.42' + 1 AS resultat;

     resultat
------------------
 2.41999995708466
(1 row)

Le type de données real est un type numérique à virgule flottante, codé sur 4 octets. Il n’offre pas une précision suffisante pour les calculs précis. Son seul avantage est la vitesse de calcul. Pour effectuer des calculs précis, il vaut mieux privilégier le type de données numeric.

Requêtes sur la base tpc :

Pour les questions suivantes, il faudra se connecter à la base de données tpc. La base tpc (dump de 31 Mo, pour 267 Mo sur le disque au final) et ses utilisateurs peuvent être installés comme suit :

curl -kL https://dali.bo/tp_tpc -o /tmp/tpc.dump
curl -kL https://dali.bo/tp_tpc_roles -o /tmp/tpc_roles.sql
# Exécuter le script de création des rôles
psql < /tmp/tpc_roles.sql
# Création de la base
createdb --owner tpc_owner tpc
# L'erreur sur un schéma 'public' existant est normale
pg_restore -d tpc /tmp/tpc.dump

Les mots de passe sont dans le script /tmp/tpc_roles.sql. Pour vous connecter :

$ psql -U tpc_admin -h localhost -d tpc

Le schéma suivant montre les différentes tables de la base :

Avec psql, pour changer de base, vous pouvez lancer la méta-commande suivante:

\c tpc

Afficher le contenu de la table pays en classant les pays dans l’ordre alphabétique.

SELECT * FROM pays ORDER BY nom_pays;

Afficher les pays contenant la lettre a, majuscule ou minuscule. Plusieurs solutions sont possibles.

SELECT * FROM pays WHERE lower(nom_pays) LIKE '%a%';

SELECT * FROM pays WHERE nom_pays ILIKE '%a%';

SELECT * FROM pays WHERE nom_pays LIKE '%a%' OR nom_pays LIKE '%A%';

En terme de performances, la seconde variante sera plus rapide sur un volume de données important si l’on dispose du bon index. La taille de la table pays ne permet pas d’observer de différence significative sur cette requête.

Afficher le nombre lignes de commandes (table lignes_commandes) dont la quantité commandée est comprise entre 5 et 10.

SELECT count(*)
  FROM lignes_commandes
 WHERE quantite BETWEEN 5 AND 10;

Autre écriture possible :

SELECT count(*)
  FROM lignes_commandes
 WHERE quantite >= 5
   AND quantite <= 10;

Pour chaque pays, afficher son nom et la région du monde dont il fait partie.

SELECT nom_pays, nom_region
  FROM pays p, regions r
 WHERE p.region_id = r.region_id;

Afficher le nombre total de clients français et allemands.

SELECT count(*)
  FROM clients cl, contacts cn, pays p
 WHERE cl.contact_id = cn.contact_id
   AND cn.code_pays = p.code_pays
   AND p.nom_pays IN ('FRANCE', 'ALLEMAGNE');

À noter que cette syntaxe est obsolète, il faut utiliser la clause JOIN, plus lisible et plus complète, qui sera vue plus loin :

SELECT count(*)
  FROM clients cl
  JOIN contacts cn ON (cl.contact_id = cn.contact_id)
  JOIN pays p ON (cn.code_pays = p.code_pays)
 WHERE p.nom_pays IN ('FRANCE', 'ALLEMAGNE');

En connaissant les codes de ces pays, il est possible d’éviter la lecture de la table pays :

SELECT count(*)
  FROM clients cl, contacts cn
 WHERE cl.contact_id = cn.contact_id
   AND cn.code_pays IN ('FR', 'DE');

L’équivalent avec la syntaxe JOIN serait :

SELECT count(*)
  FROM clients cl
  JOIN contacts cn ON (cl.contact_id = cn.contact_id)
 WHERE cn.code_pays IN ('FR', 'DE');

Afficher le numéro de commande et le nom du client ayant passé la commande. Seul un sous-ensemble des résultats sera affiché : les 20 premières lignes du résultat seront exclues et seules les 20 suivantes seront affichées. Il faut penser à ce que le résultat de cette requête soit stable entre plusieurs exécutions.

La syntaxe normalisée SQL impose d’écrire la requête de la façon suivante. La stabilité du résultat de la requête est garantie par un tri explicite, s’il n’est pas précisé, la base de données va retourner les lignes dans l’ordre physique qui est susceptible de changer entre deux exécutions :

SELECT numero_commande, nom AS nom_client
  FROM commandes cm, clients cl, contacts cn
 WHERE cm.client_id = cl.client_id
   AND cl.contact_id = cn.contact_id
 ORDER BY numero_commande
 FETCH FIRST 20 ROWS ONLY
 OFFSET 20;

Mais PostgreSQL supporte également la clause LIMIT :

SELECT numero_commande, nom AS nom_client
  FROM commandes cm, clients cl, contacts cn
 WHERE cm.client_id = cl.client_id
   AND cl.contact_id = cn.contact_id
 ORDER BY numero_commande
 LIMIT 20
 OFFSET 20;

Et l’équivalent avec la syntaxe JOIN serait :

SELECT numero_commande, nom AS nom_client
  FROM commandes cm
  JOIN clients cl ON (cm.client_id = cl.client_id)
  JOIN contacts cn ON (cl.contact_id = cn.contact_id)
 ORDER BY numero_commande
 LIMIT 20
 OFFSET 20;

Afficher les noms et codes des pays qui font partie de la région « Europe ».

SELECT nom_pays, code_pays
  FROM regions r, pays p
 WHERE r.region_id = p.region_id
   AND r.nom_region = 'Europe';

Et l’équivalent avec la syntaxe JOIN serait :

SELECT nom_pays, code_pays
  FROM regions r
  JOIN pays p ON (r.region_id = p.region_id)
 WHERE r.nom_region = 'Europe';

Pour chaque pays, afficher une chaîne de caractères composée de son nom, suivi entre parenthèses de son code puis, séparé par une virgule, du nom de la région dont il fait partie.

SELECT nom_pays || ' (' || code_pays || '), ' || nom_region
  FROM regions r, pays p
 WHERE r.region_id = p.region_id;

Et l’équivalent avec la syntaxe JOIN serait :

SELECT nom_pays || ' (' || code_pays || '), ' || nom_region
  FROM regions r
  JOIN pays p ON (r.region_id = p.region_id);

Pour les clients ayant passé des commandes durant le mois de janvier 2011, affichez les identifiants des clients, leur nom, leur numéro de téléphone et le nom de leur pays.

SELECT cl.client_id, nom, telephone, nom_pays
  FROM clients cl, commandes cm, contacts cn, pays p
 WHERE cl.client_id = cm.client_id
   AND cl.contact_id = cn.contact_id
   AND cn.code_pays = p.code_pays
   AND date_commande BETWEEN '2011-01-01' AND '2011-01-31';

Le troisième module de la formation abordera les jointures et leurs syntaxes. À l’issue de ce prochain module, la requête de cet exercice pourrait être écrite de la façon suivante :

SELECT cl.client_id, nom, telephone, nom_pays
  FROM clients cl
  JOIN commandes cm
       USING (client_id)
  JOIN contacts co
       USING (contact_id)
  JOIN pays p
       USING (code_pays)
 WHERE date_commande BETWEEN '2011-01-01' AND '2011-01-31';

Pour les dix premières commandes de l’année 2011, afficher son numéro, sa date ainsi que son âge.

SELECT numero_commande, date_commande, now() - date_commande AS age
  FROM commandes
 WHERE date_commande BETWEEN '2011-01-01' AND '2011-12-31'
 ORDER BY date_commande
 LIMIT 10;

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

Le module précédent nous a permis de voir comment lire des données à partir de requêtes SQL. Ce module a pour but de présenter la création et la gestion des objets dans la base de données (par exemple les tables), ainsi que l’ajout, la suppression et la modification de données.

Une dernière partie sera consacrée aux transactions.

Les ordres DDL (acronyme de Data Definition Language) permettent de définir des objets dans la base de données et notamment la structure de base du standard SQL : les tables.

La norme SQL définit un certain nombre d’objets standards qu’il est possible de créer en utilisant les ordres DDL. D’autres types d’objets existent bien entendu, comme les domaines. Les ordres DDL permettent également de créer des index, bien qu’ils ne soient pas définis dans la norme SQL.

La seule structure de données possible dans une base de données relationnelle est la table.

La création d’objet passe généralement par l’ordre CREATE. La syntaxe dépend fortement du type d’objet. Voici trois exemples :

CREATE SCHEMA s1;
CREATE TABLE t1 (c1 integer, c2 text);
CREATE SEQUENCE s1 INCREMENT BY 5 START 10;

Pour créer un objet, il faut être propriétaire du schéma ou de la base auquel appartiendra l’objet ou avoir le droit CREATE sur le schéma ou la base.

Modifier un objet veut dire modifier ses propriétés. On utilise dans ce cas l’ordre ALTER. Il faut être propriétaire de l’objet pour pouvoir le faire.

Deux propriétés sont communes à tous les objets : le nom de l’objet et son propriétaire. Deux autres sont fréquentes et dépendent du type de l’objet : le schéma et le tablespace. Les autres propriétés dépendent directement du type de l’objet.

Seul un propriétaire peut supprimer un objet. Il utilise pour cela l’ordre DROP. Pour les objets ayant des dépendances, l’option CASCADE permet de tout supprimer d’un coup. C’est très pratique, et c’est en même temps très dangereux : il faut donc utiliser cette option à bon escient.

Si un objet dépendant de l’objet à supprimer a lui aussi une dépendance, sa dépendance sera également supprimée. Ainsi de suite jusqu’à la dernière dépendance.

La notion de schéma dans PostgreSQL est à rapprocher de la notion d’espace de nommage (ou namespace) de certains langages de programmation. Le catalogue système qui contient la définition des schémas dans PostgreSQL s’appelle d’ailleurs pg_namespace.

Les schémas sont utilisés pour répartir les objets de façon purement logique, suivant un schéma interne à l’organisation. Ils servent aussi à faciliter la gestion des droits (il suffit de révoquer le droit d’utilisation d’un schéma à un utilisateur pour que les objets contenus dans ce schéma ne soient plus accessibles à cet utilisateur).

Un schéma public est créé par défaut dans toute nouvelle base de données.

Il existe des schémas système masqués, par exemple pour des tables temporaires ou les tables système (schéma pg_catalog).

L’ordre CREATE SCHEMA permet de créer un schéma. Il suffit de lui spécifier le nom du schéma. CREATE SCHEMA offre d’autres possibilités qui sont rarement utilisées.

L’ordre ALTER SCHEMA nom_schema RENAME TO nouveau_nom_schema permet de renommer un schéma. L’ordre ALTER SCHEMA nom_schema OWNER TO proprietaire permet de changer le propriétaire d’un schéma.

Enfin, l’ordre DROP SCHEMA permet de supprimer un schéma si il est vide. La clause IF EXISTS permet d’éviter la levée d’une erreur si le schéma n’existe pas (très utile dans les scripts SQL). La clause CASCADE permet de supprimer le schéma ainsi que tous les objets qui sont positionnés dans le schéma.

Exemples

Création d’un schéma reference :

CREATE SCHEMA reference;

Une table peut être créée dans ce schéma :

CREATE TABLE reference.communes (
  commune      text,
  codepostal   char(5),
  departement  text,
  codeinsee    integer
);

La suppression directe du schéma ne fonctionne pas car il porte encore la table communes :

DROP SCHEMA reference;
ERROR:  cannot drop schema reference because other objects depend on it
DETAIL:  table reference.communes depends on schema reference
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

L’option CASCADE permet de supprimer le schéma et ses objets dépendants :

DROP SCHEMA reference CASCADE;
NOTICE:  drop cascades to table reference.communes

Le paramètre search_path permet de définir un chemin de recherche pour pouvoir retrouver les tables dont le nom n’est pas qualifié par le nom de son schéma. PostgreSQL procèdera de la même façon que le système avec la variable $PATH : il recherche la table dans le premier schéma listé. S’il trouve une table portant ce nom dans le schéma, il préfixe le nom de table avec celui du schéma. S’il ne trouve pas de table de ce nom dans le schéma, il effectue la même opération sur le prochain schéma de la liste du search_path. S’il n’a trouvé aucune table de ce nom dans les schémas listés par search_path, PostgreSQL lève une erreur.

Comme beaucoup d’autres paramètres, le search_path peut être positionné à différents endroits. Par défaut, il est assigné à $user, public, c’est-à-dire que le premier schéma de recherche portera le nom de l’utilisateur courant, et le second schéma de recherche est public.

Il est possible de vérifier la configuration de la variable search_path à l’aide de la commande SHOW :

SHOW search_path;

  search_path
----------------
 "$user",public

Pour obtenir une configuration particulière, la variable search_path peut être positionnée dans le fichier postgresql.conf :

search_path = '"$user",public'

Cette variable peut aussi être positionnée au niveau d’un utilisateur. Chaque fois que l’utilisateur se connectera, il prendra le search_path de sa configuration spécifique :

ALTER ROLE nom_role SET search_path = "$user", public;

Cela peut aussi se faire au niveau d’une base de données. Chaque fois qu’un utilisateur se connectera à la base, il prendra le search_path de cette base, sauf si l’utilisateur a déjà une configuration spécifique :

ALTER DATABASE nom_base SET search_path = "$user", public;

La variable search_path peut également être positionnée pour un utilisateur particulier, dans une base particulière :

ALTER ROLE nom_role IN DATABASE nom_base SET search_path = "$user", public;

Enfin, la variable search_path peut être modifiée dynamiquement dans la session avec la commande SET :

SET search_path = "$user", public;

Les séquences sont des objets standards qui permettent de générer des séries de valeur. Elles sont utilisées notamment pour générer un numéro unique pour un identifiant ou, plus rarement, pour disposer d’un compteur informatif, mis à jour au besoin.

Le cache de la séquence a pour effet de générer un certain nombre de valeurs en mémoire afin de les mettre à disposition de la session qui a utilisé la séquence. Même si les valeurs pré-calculées ne sont pas consommées dans la session, elles seront consommées au niveau de la séquence. Cela peut avoir pour effet de créer des trous dans les séquences d’identifiants et de consommer très rapidement les numéros de séquence possibles. Le cache de séquence n’a pas besoin d’être ajusté sur des applications réalisant de petites transactions. Il permet en revanche d’améliorer les performances sur des applications qui utilisent massivement des numéros de séquences, notamment pour réaliser des insertions massives.

La syntaxe complète est donnée dans le slide.

Le mot clé TEMPORARY ou TEMP permet de définir si la séquence est temporaire. Si tel est le cas, elle sera détruite à la déconnexion de l’utilisateur.

Le mot clé INCREMENT définit l’incrément de la séquence, MINVALUE, la valeur minimale de la séquence et MAXVALUE, la valeur maximale. START détermine la valeur de départ initiale de la séquence, c’est-à-dire juste après sa création. La clause CACHE détermine le cache de séquence. CYCLE permet d’indiquer au SGBD que la séquence peut reprendre son compte à MINVALUE lorsqu’elle aura atteint MAXVALUE. La clause NO CYCLE indique que le rebouclage de la séquence est interdit, PostgreSQL lèvera alors une erreur lorsque la séquence aura atteint son MAXVALUE. Enfin, la clause OWNED BY détermine l’appartenance d’une séquence à une colonne d’une table. Ainsi, si la colonne est supprimée, la séquence sera implicitement supprimée.

Exemple de séquence avec rebouclage :

CREATE SEQUENCE testseq INCREMENT BY 1 MINVALUE 3 MAXVALUE 5 CYCLE START WITH 4;

SELECT nextval('testseq');

 nextval
---------
       4

SELECT nextval('testseq');

 nextval
---------
       5

SELECT nextval('testseq');

 nextval
---------
       3

Les propriétés de la séquence peuvent être modifiées avec l’ordre ALTER SEQUENCE.

La séquence peut être affectée à un nouveau propriétaire :

ALTER SEQUENCE [ IF EXISTS ] nom OWNER TO nouveau_propriétaire

Elle peut être renommée :

ALTER SEQUENCE [ IF EXISTS ] nom RENAME TO nouveau_nom

Enfin, elle peut être positionnée dans un nouveau schéma :

ALTER SEQUENCE [ IF EXISTS ] nom SET SCHEMA nouveau_schema

Voici la syntaxe complète de DROP SEQUENCE :

DROP SEQUENCE [ IF EXISTS ] nom [, …] [ CASCADE | RESTRICT ]

Le mot clé CASCADE permet de supprimer la séquence ainsi que tous les objets dépendants (par exemple la valeur par défaut d’une colonne).

La fonction nextval() permet d’obtenir le numéro de séquence suivant. Son comportement n’est pas transactionnel. Une fois qu’un numéro est consommé, il n’est pas possible de revenir dessus, malgré un ROLLBACK de la transaction. La séquence est le seul objet à avoir un comportement de ce type. C’est cependant nécessaire, notamment pour des raisons de performance.

La fonction currval() permet d’obtenir le numéro de séquence courant, mais son usage nécessite d’avoir utilisé nextval() dans la même session.

Il est possible d’interroger une séquence avec une requête SELECT. Cela permet d’obtenir des informations sur la séquence, dont la dernière valeur utilisée dans la colonne last_value. Cet usage n’est pas recommandé en production et doit plutôt être utilisé à titre informatif.

Exemples

Utilisation d’une séquence simple :

CREATE SEQUENCE testseq
INCREMENT BY 1 MINVALUE 10 MAXVALUE 20 START WITH 15 CACHE 1;

SELECT currval('testseq');

ERROR:  currval of sequence "testseq" is not yet defined in this session

SELECT * FROM testseq ;

- [ RECORD 1 ]-+--------
sequence_name  | testseq
last_value     | 15
start_value    | 15
increment_by   | 1
max_value      | 20
min_value      | 10
cache_value    | 5
log_cnt        | 0
is_cycled      | f
is_called      | f

SELECT nextval('testseq');

 nextval
---------
      15

SELECT currval('testseq');

 currval
---------
      15

SELECT nextval('testseq');

 nextval
---------
      16

ALTER SEQUENCE testseq RESTART WITH 5;

ERROR:  RESTART value (5) cannot be less than MINVALUE (10)

DROP SEQUENCE testseq;

Utilisation d’une séquence simple avec cache :

CREATE SEQUENCE testseq INCREMENT BY 1 CACHE 10;

SELECT nextval('testseq');

 nextval
---------
       1

Déconnexion et reconnexion de l’utilisateur :

SELECT nextval('testseq');

 nextval
---------
      11

Suppression en cascade d’une séquence :

CREATE TABLE t2 (id serial);

\d t2
                           Table "s2.t2"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('t2_id_seq'::regclass)

DROP SEQUENCE t2_id_seq;

ERROR:  cannot drop sequence t2_id_seq because other objects depend on it
DETAIL:  default for table t2 column id depends on sequence t2_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

DROP SEQUENCE t2_id_seq CASCADE;

NOTICE:  drop cascades to default for table t2 column id

\d t2
        Table "s2.t2"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null

Certaines bases de données offrent des colonnes auto-incrémentées (autoincrement de MySQL ou identity de SQL Server).

PostgreSQL possède identity à partir de PostgreSQL 10. Il était déjà possible d’utiliser serial, un équivalent qui s’appuie sur les séquences et la possibilité d’appliquer une valeur par défaut à une colonne.

Par exemple, si l’on crée la table suivante :

CREATE TABLE exemple_serial (
  id SERIAL PRIMARY KEY,
  valeur INTEGER NOT NULL
);

On s’aperçoit que la table a été créée telle que demandé, mais qu’une séquence a aussi été créée. Elle porte un nom dérivé de la table associé à la colonne correspondant au type serial, terminé par seq :

\d
                 List of relations
 Schema |         Name          |   Type   | Owner
--------+-----------------------+----------+--------
 public | exemple_serial        | table    | thomas
 public | exemple_serial_id_seq | sequence | thomas

En examinant plus précisément la définition de la table, on s’aperçoit que la colonne id porte une valeur par défaut qui correspond à l’appel de la fonction nextval() sur la séquence qui a été créée implicitement :

\d exemple_serial
                         Table "public.exemple_serial"
 Column |  Type   |                          Modifiers
--------+---------+-------------------------------------------------------------
 id     | integer | not null default nextval('exemple_serial_id_seq'::regclass)
 valeur | integer | not null
Indexes:
    "exemple_serial_pkey" PRIMARY KEY, btree (id)

smallserial et bigserial sont des variantes de serial s’appuyant sur des types d’entiers plus courts ou plus longs.

Un domaine est un type standard (numérique, texte…) auquel ont été ajoutées des contraintes particulières.

Les domaines sont utiles pour ne pas définir les mêmes contraintes sur plusieurs colonnes. La maintenance en est ainsi facilitée.

Les domaines sont utiles pour ramener la définition de contraintes communes à plusieurs colonnes sur un seul objet. La maintenance en est ainsi facilitée.

L’ordre CREATE DOMAIN permet de créer un domaine, ALTER DOMAIN permet de modifier sa définition, et enfin, DROP DOMAIN permet de supprimer un domaine.

Exemples : gestion d’un domaine salaire :

Commençons par le domaine et une table d’exemple :

CREATE DOMAIN salaire AS integer CHECK (VALUE > 0);
CREATE TABLE employes (id serial, nom text, paye salaire);

\d employes
                               Table « public.employes »
 Colonne |  Type   |  NULL-able |              Par défaut
---------+---------+------------+--------------------------------------
 id      | integer |  not null  | nextval('employes_id_seq'::regclass)
 nom     | text    |            |
 paye    | salaire |            |

Insérons des données dans la table :

INSERT INTO employes (nom, paye) VALUES ('Albert', 1500);
INSERT INTO employes (nom, paye) VALUES ('Alphonse', 0);

ERROR:  value for domain salaire violates check constraint "salaire_check"

L’erreur ci-dessus est logique vu qu’on ne peut avoir qu’un entier strictement positif.

INSERT INTO employes (nom, paye) VALUES ('Alphonse', 1000);
INSERT INTO employes (nom, paye) VALUES ('Bertrand', NULL);

Tous les employés doivent avoir un salaire. Il faut donc modifier la contrainte pour s’assurer qu’aucune valeur NULL (vide) ne soit saisie

ALTER DOMAIN salaire SET NOT NULL;

ERROR:  column "paye" of table "employes" contains null values

En effet, une ligne avec NULL est déjà présente, il faut la corriger pour pouvoir ajouter la contrainte.

UPDATE employes SET paye=1500 WHERE nom='Bertrand';

ALTER DOMAIN salaire SET NOT NULL;
INSERT INTO employes (nom, paye) VALUES ('Delphine', NULL);

ERROR:  domain salaire does not allow null values

La contrainte est donc bien vérifiée, et la ligne avec NULL rejetée.

Supprimons maintenant la contrainte :

DROP DOMAIN salaire;

ERROR:  cannot drop type salaire because other objects depend on it
DETAIL:  table employes column paye depends on type salaire
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Il n’est pas possible de supprimer le domaine car il est référencé dans une table. Il faut donc utiliser l’option CASCADE pour détruire aussi les objets qui dépendent du domaine.

DROP DOMAIN salaire CASCADE;

NOTICE:  drop cascades to table employes column paye
DROP DOMAIN

Le domaine a été supprimée ainsi que toutes les colonnes de ce type :

\d employes
                               Table « public.employes »
 Colonne |  Type   | NULL-able |              Par défaut
---------+---------+-----------+--------------------------------------
 id      | integer | not null  | nextval('employes_id_seq'::regclass)
 nom     | text    |           |

Exemples : création et utilisation d’un domaine code_postal_us :

CREATE DOMAIN code_postal_us AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

CREATE TABLE courrier_us (
  id_adresse SERIAL PRIMARY KEY,
  rue1 TEXT NOT NULL,
  rue2 TEXT,
  rue3 TEXT,
  ville TEXT NOT NULL,
  code_postal code_postal_us NOT NULL
);

INSERT INTO courrier_us (rue1,ville,code_postal)
VALUES ('51 Franklin Street', 'Boston, MA', '02110-1335' );

INSERT INTO courrier_us (rue1,ville,code_postal)
VALUES ('10 rue d''Uzès','Paris','F-75002') ;

ERREUR:  la valeur pour le domaine code_postal_us viole la contrainte de
vérification « code_postal_us_check »

La table est l’élément de base d’une base de données. Elle est composée de colonnes (à sa création) et est remplie avec des enregistrements (lignes de la table). Sa définition peut aussi faire intervenir des contraintes, qui sont au niveau table ou colonne.

Pour créer une table, il faut donner son nom et la liste des colonnes. Une colonne est définie par son nom et son type, mais aussi des contraintes optionnelles.

Des options sont possibles pour les tables, comme les clauses de stockage. Dans ce cas, on sort du contexte logique pour se placer au niveau physique.

La création d’une table passe par l’ordre CREATE TABLE. La définition des colonnes et des contraintes sont entre parenthèse après le nom de la table.

Les colonnes sont indiquées l’une après l’autre, en les séparant par des virgules.

Deux informations sont obligatoires pour chaque colonne : le nom et le type de la colonne. Dans le cas d’une colonne contenant du texte, il est possible de fournir le collationnement de la colonne. Quelle que soit la colonne, il est ensuite possible d’ajouter des contraintes.

La clause DEFAULT permet d’affecter une valeur par défaut lorsqu’une colonne n’est pas référencée dans l’ordre d’insertion ou si une mise à jour réinitialise la valeur de la colonne à sa valeur par défaut.

Les types sériés définissent une valeur par défaut sur les colonnes de ce type. Cette valeur est le retour de la fonction nextval() sur la séquence affectée automatiquement à cette colonne.

Exemples

Assignation d’une valeur par défaut :

CREATE TABLE valdefaut (
  id integer,
  i integer DEFAULT 0,
  j integer DEFAULT 0
);

INSERT INTO valdefaut (id, i) VALUES (1, 10);

SELECT * FROM valdefaut ;
 id | i  | j
----+----+---
  1 | 10 | 0
(1 row)

L’ordre CREATE TABLE permet également de créer une table à partir de la définition d’une table déjà existante en utilisant la clause LIKE en lieu et place de la définition habituelle des colonnes. Par défaut, seule la définition des colonnes avec leur typage est repris.

Les clauses INCLUDING permettent de récupérer d’autres éléments de la définition de la table, comme les valeurs par défaut (INCLUDING DEFAULTS), les contraintes d’intégrité (INCLUDING CONSTRAINTS), les index (INCLUDING INDEXES), les clauses de stockage (INCLUDING STORAGE) ainsi que les commentaires (INCLUDING COMMENTS). Si l’ensemble de ces éléments sont repris, il est possible de résumer la clause INCLUDING à INCLUDING ALL.

La clause CREATE TABLE suivante permet de créer une table archive_evenements_2010 à partir de la définition de la table evenements :

CREATE TABLE archive_evenements_2010
 (LIKE evenements
  INCLUDING DEFAULTS
  INCLUDING CONSTRAINTS
  INCLUDING INDEXES
  INCLUDING STORAGE
  INCLUDING COMMENTS
);

Elle est équivalente à :

CREATE TABLE archive_evenements_2010
 (LIKE evenements
  INCLUDING ALL
);

Pour modifier la définition d’une table (et non pas son contenu), il convient d’utiliser l’ordre ALTER TABLE. Il permet de traiter la définition de la table (nom, propriétaire, schéma, liste des colonnes), la définition des colonnes (ajout, modification de nom et de type, suppression… mais pas de changement au niveau de leur ordre), et la définition des contraintes (ajout et suppression).

Suivant l’opération réalisée, les verrous posés ne seront pas les mêmes, même si le verrou par défaut sera un verrou exclusif. Par exemple, renommer une table nécessite un verrou exclusif mais changer la taille de l’échantillon statistiques bloque uniquement certaines opérations de maintenance (comme VACUUM et ANALYZE) et certaines opérations DDL. L’utilisation de la commande ALTER TABLE sur un serveur en production doit donc souvent passer par une opération de maintenance planifiée.

Certaines opérations nécessitent de vérifier que les données satisfassent les nouvelles contraintes. C’est évident lors de l’ajout d’une clé primaire ou d’une contrainte NOT NULL, par exemple. Or, relire une grosse table peut donc être très coûteux ! Dans certains cas, PostgreSQL sait éviter un contrôle inutile, par exemple lors d’un passage de varchar(10) à varchar(20).

Certaines opérations nécessitent une réécriture de la table. Par exemple, convertir une colonne de type varchar(5) vers le type int impose une réécriture de la table car il n’y a pas de compatibilité binaire entre les deux types.

Il convient donc d’être très prudent lors de l’utilisation de la commande ALTER TABLE. Elle peut poser des problèmes de performances, à cause de verrous posés par d’autres commandes, de verrous qu’elle réclame, de la vérification des données, voire de la réécriture de la table.

L’ordre DROP TABLE permet de supprimer une table. L’ordre DROP TABLE … CASCADE permet de supprimer une table ainsi que tous ses objets dépendants. Il peut s’agir de séquences rattachées à une colonne d’une table, à des colonnes référençant la table à supprimer, etc.

Les données dans les différentes tables ne sont pas indépendantes mais obéissent à des règles sémantiques mises en place au moment de la conception du modèle de données. Les contraintes d’intégrité ont pour principal objectif de garantir la cohérence des données entre elles, et donc de veiller à ce qu’elles respectent ces règles sémantiques. Si une insertion, une mise à jour ou une suppression viole ces règles, l’opération est purement et simplement annulée.

Une « clé primaire » permet d’identifier une ligne de façon unique.

Il n’existe qu’une seule clé primaire par table.

Une clé primaire exige que toutes les valeurs de la ou des colonnes qui composent cette clé soient uniques et non nulles. La clé peut être composée d’une seule colonne ou de plusieurs colonnes, selon le besoin.

La clé primaire est déterminée au moment de la conception du modèle de données. Cette clé peut être « naturelle » et visible de l’utilisateur, ou purement technique et non visible. Le débat entre les deux modélisations a longtemps fait rage.

Clé primaire naturelle :

Par exemple, une table des factures peut avoir comme clé primaire « naturelle » le numéro de la facture. C’est le plus intuitif.

De nombreuses applications utilisent des « clés naturelles », par exemple un numéro de commande ou de Sécurité Sociale. Ces clés peuvent même être composées de plusieurs champs.

Cela peut poser des soucis techniques. Par exemple, il y a des contraintes légales sur l’incrémentation des numéros de facture qui obligent à le générer tard dans le processus. Enregistrer une facture incomplète sans son numéro devient compliqué. Un numéro provisoire est possible, mais le changer implique de modifier aussi toutes les tables qui y font référence.

Liée à notre table des factures, une table des lignes de facture aurait alors comme clé primaire composée le numéro de la facture et le numéro de la ligne. Cela fonctionne, mais une clé composée, d’ailleurs souvent de type texte, est moins performante pour les jointures qu’un champ monocolonne numérique.

Clé primaire technique :

Cette surrogate keys est donc une clé primaire destinée à résoudre les soucis techniques des clés primaires naturelles.

L’utilisateur final de l’application ne la verra pas.

Cette clé technique doit être générée avec une séquence, ou un UUID (quasi-aléatoire). Ce qui compte est l’unicité.

La clé technique n’a pas à être modifiée puisque sa valeur n’a pas de sens fonctionnel.

Les jointures se font alors sur cet unique champ numérique, de manière efficace.

Les clés « naturelles » restent présentes sous forme d’un champ d’une table, avec une contrainte d’unicité. Si cette clé naturelle doit être modifiée pour une raison ou une autre, il suffit de changer la valeur dans le champ de la table, et il n’y a aucune modification à faire dans les tables possédant une clé étrangère vers cette table.

Dans notre exemple, la table des factures porterait une clé primaire numérique facture_id arbitraire, et un champ numero_facture, unique, qui, lui, peut être modifié ou être temporairement vide. La table des lignes de facture porterait une clé primaire technique facture_ligne_id sans lien, et une clé étrangère (non composée) reprenant facture_id, et un champ unique numero_ligne.

Exemple 2 :

Pour une assurance ou un garagiste, une table des véhicules ne peut avoir pour clé primaire la plaque d’immatriculation : elle peut changer, elle peut être inconnue, provisoire, absente, voire fausse.

Un identifiant technique est largement préférable.

Exemple 3 :

Une table des clients ne peut pas porter de clé primaire naturelle liée aux nom, prénom, date de naissance… à cause des nombreuses homonymies, et des changements et corrections d’état-civil possibles.

Un code client est plus envisageable, mais il pourrait changer aussi (migration de logiciel, fusion de bases clients…). Le code client peut devenir une clé unique dans une table des clients portant une clé purement technique.

Exemple 4 :

Une table de personnes identifiées par le numéro de Sécurité Sociale ne peut utiliser ce dernier comme clé naturelle : valeur parfois absente ou inconnue, changement possible, voire doublons (!), sans parler de la confidentialité.

Un code de personne propre à l’application est plus pertinent.

Là encore, on créera plutôt une clé technique, et le code de personne et le code de Sécurité Sociale sont juste des champs uniques.

Exemple 5 :

Une table des commandes clients porte une référence vers une table des adresses. Cette table des adresses doit impérativement porter une clé technique : il n’y a guère de sens à créer un « code adresse », et surtout les clients changent régulièrement d’adresse.

Un changement d’adresse d’un client consiste à créer une nouvelle ligne dans la table des adresses et à modifier l’identifiant d’adresse dans la table des clients. Les anciennes commandes et factures doivent en effet conserver l’identifiant des anciennes adresses.

Exemple 6 :

La table des adresses possède un champ renvoyant à une table des pays.

Un code pays ISO est ce qui se rapproche le plus d’une clé naturelle acceptable comme clé technique : courte (FR, DE), normalisée et sans risque de changement (le code reste identique et n’est pas recyclé).

Index :

Exemple avec clé technique manuelle :

CREATE TABLE region
  (
    id       int   PRIMARY KEY,
    libelle  text  NOT NULL  UNIQUE
  );

INSERT INTO region VALUES (1, 'Alsace');
INSERT INTO region VALUES (2, 'Île-de-France');

La clé primaire est forcément NOT NULL : ceci va être rejeté :

INSERT INTO region VALUES (NULL, 'Corse');

ERROR:  null value in column "id" of relation "region" violates not-null constraint
DÉTAIL : Failing row contains (null, Corse).

INSERT INTO region VALUES (1, 'Corse');

ERROR:  duplicate key value violates unique constraint "region_pkey"
DÉTAIL : Key (id)=(1) already exists.

TABLE region ;

 id |    libelle 
----+---------------
  1 | Alsace
  2 | Île de France

Exemple avec séquence :

La séquence génère des identifiants que l’on peut utiliser :

CREATE SEQUENCE departement_seq AS int ;

CREATE TABLE departement
  (
    id       int   PRIMARY KEY,
    libelle  text  NOT NULL UNIQUE,
    code     varchar(3) NOT NULL UNIQUE,
    region_id int NOT NULL REFERENCES region (id)
  );

INSERT INTO departement
SELECT nextval ('departement_seq'), 'Bas-Rhin', '67', 1 ;

INSERT INTO departement
VALUES ( nextval ('departement_seq'),  'Haut-Rhin', '68', 1 ),
       ( nextval ('departement_seq'),  'Paris',     '75', 2 ) ;

Noter que cette dernière erreur va « consommer » un numéro de séquence, ce qui en fait n’a pas d’importance pour une clé technique.

INSERT INTO departement
SELECT nextval ('departement_seq'), 'Yvelines', '78', null ;

ERROR:  null value in column "region_id" of relation "departement" violates not-null constraint
DETAIL : Failing row contains (4, Yvelines, 78, null).

WITH nouveaudept AS (
  INSERT INTO departement
  SELECT nextval ('departement_seq'), 'Yvelines', '78', 2
  RETURNING *)
SELECT * FROM nouveaudept ;

 id | libelle  | code | region_id 
----+----------+------+-----------
   | Yvelines | 78   |         2

TABLE departement ;

 id |  libelle  | code | region_id 
----+-----------+------+-----------
  1 | Bas-Rhin  | 67   |         1
  2 | Haut-Rhin | 68   |         1
  3 | Paris     | 75   |         2
  5 | Yvelines  | 78   |         2

Exemples avec serial :

Noter que serial est considéré comme un type (il existe aussi bigserial). Il y a création d’une séquence en arrière-plan, utilisée de manière transparente.

CREATE TABLE ville
  (
    id       serial   PRIMARY KEY,
    libelle  text     NOT NULL,
    departement_id int NOT NULL REFERENCES departement
  );

INSERT INTO ville (libelle, departement_id) VALUES ('Strasbourg',1);
INSERT INTO ville (libelle, departement_id) VALUES ('Paris',3);

Interférer avec l’autoincrémentation est une très mauvaise idée :

INSERT INTO ville VALUES (3, 'Mulhouse', 2);
INSERT INTO ville (libelle, departement_id) VALUES ('Sélestat',1);

ERROR:  duplicate key value violates unique constraint "ville_pkey"
DETAIL : Key (id)=(3) already exists.

Exemple avec IDENTITY :

CREATE TABLE ville2
  (
    id       int   GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    libelle  text  NOT NULL,
    departement_id int NOT NULL REFERENCES departement
  );

Le maniement est identique au serial. Il faut préférer l’identité pour des raisons de respect de standard SQL et à cause de quelques spécificités dans la gestion automatique des séquences sous-jacentes.

Exemple avec une clé composée :

Nous verrons plus loin un exemple avec une clé primaire composée, qui doit se déclarer alors après les colonnes :

CREATE TABLE stock
  (
    vin_id        int    not null,
    contenant_id  int    not null,
    annee         int4   not null,
    nombre        int4   not null,

    PRIMARY KEY (vin_id,contenant_id,annee),

    FOREIGN KEY(vin_id) REFERENCES vin(id) ON DELETE CASCADE,
    FOREIGN KEY(contenant_id) REFERENCES contenant(id) ON DELETE CASCADE
  );

Une contrainte d’unicité permet de garantir que les valeurs de la ou des colonnes sur lesquelles porte la contrainte sont uniques.

Une contrainte d’unicité peut être créée simplement en créant un index UNIQUE approprié. Ceci est fortement déconseillé du fait que la contrainte ne sera pas référencée comme telle dans le schéma de la base de données. Il sera donc très facile de ne pas la remarquer au moment d’une reprise du schéma pour une évolution majeure de l’application. Une colonne possédant juste l’index UNIQUE peut malgré tout être référencée par une clé étrangère.

Les contraintes d’unicité créent implicitement et obligatoirement un index pour implémenter cette unicité.

Voici un exemple complet.

Sans contrainte d’unicité, on peut insérer plusieurs fois la même valeur, sans erreur :

CREATE TABLE utilisateurs(id integer);
INSERT INTO utilisateurs VALUES (10);
INSERT INTO utilisateurs VALUES (10);

Ce n’est plus le cas en déclarant une contrainte d’unicité :

TRUNCATE utilisateurs;
ALTER TABLE utilisateurs ADD UNIQUE(id);
INSERT INTO utilisateurs (id) VALUES (10);
INSERT INTO utilisateurs (id) VALUES (11);
INSERT INTO utilisateurs (id) VALUES (11);

ERROR:  duplicate key value violates unique constraint "utilisateurs_id_key"
DETAIL:  Key (id)=(11) already exists.

Le cas de NULL est un peu particulier. Par défaut, plusieurs valeurs NULL peuvent figurer dans un index car elles ne sont pas considérées comme égales, mais de valeur inconnue (unknown). Par exemple, une table de personnes physiques peut contenir un champ numero_secu qui ne doit pas contenir de doublon, mais n’est pas forcément rempli, donc contient de nombreuses valeurs nulles.

Ici, on peut insérer plusieurs valeurs NULL :

INSERT INTO utilisateurs (id) VALUES (NULL);
INSERT INTO utilisateurs (id) VALUES (NULL);
INSERT INTO utilisateurs (id) VALUES (NULL);

Ce comportement est modifiable en version 15, et une seule valeur NULL sera tolérée. Lors de la création de la contrainte, il faut préciser ce nouveau comportement :

TRUNCATE utilisateurs;
ALTER TABLE utilisateurs DROP CONSTRAINT utilisateurs_id_key;
ALTER TABLE utilisateurs ADD UNIQUE NULLS NOT DISTINCT(id);
INSERT INTO utilisateurs (id) VALUES (10);
INSERT INTO utilisateurs (id) VALUES (10);

ERROR:  duplicate key value violates unique constraint "utilisateurs_id_key"
DETAIL:  Key (id)=(10) already exists.

INSERT INTO utilisateurs (id) VALUES (11);
INSERT INTO utilisateurs (id) VALUES (NULL);
INSERT INTO utilisateurs (id) VALUES (NULL);

ERROR:  duplicate key value violates unique constraint "utilisateurs_id_key"
DETAIL:  Key (id)=(null) already exists.

Une clé étrangère sur une table fait référence à une clé primaire ou une contrainte d’unicité d’une autre table. La clé étrangère garantit que les valeurs des colonnes de cette clé existent également dans la table portant la clé primaire ou la contrainte d’unicité. On parle de contrainte référentielle d’intégrité : la contrainte interdit les valeurs qui n’existent pas dans la table référencée. Toute insertion ou modification qui viole cette règle est rejetée.

C’est ce mécanisme qui permet de garantir qu’une commande sera liée à un client existant dans la table des clients, ou que le pays dans une adresse existe bien dans la table des pays. Et à l’inverse, on ne pourra supprimer un pays ou un client dans les tables des commandes ou adresses qui possèdent une contrainte d’intégrité dessus.

À titre d’exemple nous allons utiliser la base cave. La base cave (dump de 2,6 Mo, pour 71 Mo sur le disque au final) peut être téléchargée et restaurée ainsi :

curl -kL https://dali.bo/tp_cave -o cave.dump
psql -c "CREATE ROLE caviste LOGIN PASSWORD 'caviste'"
psql -c "CREATE DATABASE cave OWNER caviste"
pg_restore -d cave cave.dump
# NB : une erreur sur un schéma 'public' existant est normale

Le schéma suivant montre les différentes tables de la base :

Ainsi, la base cave définit une table region et une table appellation. Une appellation d’origine est liée au terroir, et par extension à son origine géographique. La table appellation est donc liée par une clé étrangère à la table region : la colonne region_id de la table appellation référence la colonne id de la table region.

Cette contrainte permet d’empêcher les utilisateurs d’entrer dans la table appellation des identifiants de région (region_id) qui n’existent pas dans la table region.

Exemples :

Définition de la table stock :

CREATE TABLE stock
  (
    vin_id        int    not null,
    contenant_id  int    not null,
    annee         int4   not null,
    nombre        int4   not null,

    PRIMARY KEY(vin_id,contenant_id,annee),

    FOREIGN KEY(vin_id) REFERENCES vin(id) ON DELETE CASCADE,
    FOREIGN KEY(contenant_id) REFERENCES contenant(id) ON DELETE CASCADE
  );