Plus loin avec SQL

Préambule

• Après la définition des objets, leur lecture et leur écriture
• Aller plus loin dans l’écriture de requêtes avec :
  • les jointures
  • les sous-requêtes
  • les vues
  • les fonctions

Maintenant que nous avons vu comment définir des objets, comment lire des données provenant de relation et comment écrire des données, nous allons pousser vers les perfectionnements du langage SQL. Nous allons notamment aborder la lecture de plusieurs tables en même temps, que ce soit par des jointures ou par des sous-requêtes.

Menu

• Valeur NULL
• Agrégats GROUP BY, HAVING
• Sous-requêtes
• Jointures JOIN
• Expression conditionnelle CASE
• Opérateurs ensemblistes : UNION, EXCEPT, INTERSECT

Menu (suite)

• Fonctions de base
• Vues
• Requêtes préparées

Objectifs

• Comprendre l’intérêt du NULL
• Savoir écrire des requêtes complexes

Les exemples suivants utilisent souvent la base cave. Si vous ne l’avez pas déjà, 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

Valeur NULL

• Comment représenter une valeur que l’on ne connaît pas ?
  • valeur NULL
• Trois sens possibles pour NULL :
  • valeur inconnue
  • valeur inapplicable
  • absence de valeur
• Logique à 3 états

Le standard SQL définit très précisément la valeur que doit avoir une colonne dont on ne connaît pas la valeur. Il faut utiliser le mot clé NULL. En fait, ce mot clé est utilisé dans trois cas : pour les valeurs inconnues, pour les valeurs inapplicables et pour une absence de valeurs.

Avertissement

• Chris J. Date a écrit :
  • La valeur NULL telle qu’elle est implémentée dans SQL peut poser plus de problèmes qu’elle n’en résout. Son comportement est parfois étrange et est source de nombreuses erreurs et de confusions.
• Éviter d’utiliser NULL le plus possible
  • utiliser NULL correctement lorsqu’il le faut

Il ne faut utiliser NULL que lorsque cela est réellement nécessaire. La gestion des valeurs NULL est souvent source de confusions et d’erreurs, ce qui explique qu’il est préférable de l’éviter tant qu’on n’entre pas dans les trois cas vu ci-dessus (valeur inconnue, valeur inapplicable, absence de valeur).

Assignation de NULL

• Assignation de NULL pour INSERT et UPDATE
• Explicitement :
  • NULL est indiqué explicitement dans les assignations
• Implicitement :
  • la colonne n’est pas affectée par INSERT
  • et n’a pas de valeur par défaut
• Empêcher la valeur NULL
  • contrainte NOT NULL

Il est possible de donner le mot-clé NULL pour certaines colonnes dans les INSERT et les UPDATE. Si jamais une colonne n’est pas indiquée dans un INSERT, elle aura comme valeur sa valeur par défaut (très souvent, il s’agit de NULL). Si jamais on veut toujours avoir une valeur dans une colonne particulière, il faut utiliser la clause NOT NULL lors de l’ajout de la colonne. C’est le cas pour les clés primaires par exemple.

Voici quelques exemples d’insertion et de mise à jour :

CREATE TABLE public.personnes
(
   id serial,
   nom character varying(60) NOT NULL,
   prenom character varying(60),
   date_naissance date,
   CONSTRAINT pk_personnes PRIMARY KEY (id)
) ;

INSERT INTO personnes( nom, prenom, date_naissance )
    VALUES ('Lagaffe', 'Gaston', date '1957-02-28') ;

-- assignation explicite

INSERT INTO personnes( nom, prenom, date_naissance )
    VALUES ('Fantasio', NULL, date '1938-01-01') ;

-- assignation implicite

INSERT INTO personnes ( nom, prenom )
    VALUES ('Prunelle', 'Léon') ;

-- observation des résultats
SELECT * FROM personnes ;

 id |   nom    | prenom | date_naissance
----+----------+--------+----------------
  1 | Lagaffe  | Gaston | 1957-02-28
  2 | Fantasio | (null) | 1938-01-01
  3 | Prunelle | Léon   | (null)

L’affichage (null) dans psql est obtenu avec la méta-commande :

\pset null (null)

Calculs avec NULL

• Utilisation dans un calcul
  • propagation de NULL
• NULL est inapplicable
  • le résultat vaut NULL

La valeur NULL est définie comme inapplicable. Ainsi, si elle présente dans un calcul, elle est propagée sur l’ensemble du calcul : le résultat vaudra NULL.

Exemples de calcul

Calculs simples :

SELECT 1 + 2 AS resultat ;

 resultat
----------
        3

SELECT 1 + 2 + NULL AS resultat ;

 resultat
----------
  (null)

Calcul à partir de l’âge :

SELECT nom, prenom,
    1 + extract('year' from age(date_naissance)) AS calcul_age
FROM personnes ;

   nom    | prenom | calcul_age
----------+--------+------------
 Lagaffe  | Gaston |         60
 Fantasio | (null) |         79
 Prunelle | Léon   |     (null)

Exemple d’utilisation de NULL dans une concaténation :

SELECT nom || ' ' || prenom AS nom_complet
FROM personnes ;

  nom_complet
----------------
 Lagaffe Gaston
 (null)
 Prunelle Léon

L’affichage (null) est obtenu avec la méta-commande \pset null (null) du shell psql.

NULL et les prédicats

• Dans un prédicat du WHERE :
  • opérateur IS NULL ou IS NOT NULL
• AND :
  • vaut false si NULL AND false
  • vaut NULL si NULL AND true ou NULL AND NULL
• OR :
  • vaut true si NULL OR true
  • vaut NULL si NULL OR false ou NULL OR NULL

Les opérateurs de comparaisons classiques ne sont pas fonctionnels avec une valeur NULL. Du fait de la logique à trois états de PostgreSQL, une comparaison avec NULL vaut toujours NULL, ainsi expression = NULL vaudra toujours NULL et de même pour expression <> NULL vaudra toujours NULL. Cette comparaison ne vaudra jamais ni vrai, ni faux.

De ce fait, il existe les opérateurs de prédicats IS NULL et IS NOT NULL qui permettent de vérifier qu’une expression est NULL ou n’est pas NULL.

Pour en savoir plus sur la logique ternaire qui régit les règles de calcul des prédicats, se conformer à la page Wikipédia sur la logique ternaire.

Exemples

Comparaison directe avec NULL, qui est invalide :

SELECT * FROM personnes WHERE date_naissance = NULL ;

 id | nom | prenom | date_naissance
----+-----+--------+----------------
(0 rows)

L’opérateur IS NULL permet de retourner les lignes dont la date de naissance n’est pas renseignée :

SELECT * FROM personnes WHERE date_naissance IS NULL ;

 id |   nom    | prenom | date_naissance
----+----------+--------+----------------
  3 | Prunelle | Léon   | (null)

NULL et les agrégats

• Opérateurs d’agrégats
  • ignorent NULL
  • sauf count(*)

Les fonctions d’agrégats ne tiennent pas compte des valeurs NULL :

SELECT sum(extract('year' from age(date_naissance))) AS age_cumule
FROM personnes ;

 age_cumule
------------
        139

Sauf count(*) et uniquement count(*). La fonction count(_expression_) tient quant à elle compte des valeurs NULL :

SELECT count(*) AS compte_lignes, count(date_naissance) AS compte_valeurs
  FROM (SELECT date_naissance
         FROM personnes) date_naissance ;

 compte_lignes | compte_valeurs
---------------+----------------
             3 |              2

COALESCE

• Remplacer NULL par une autre valeur
  • COALESCE(attribut, …);

Cette fonction permet de tester une colonne et de récupérer sa valeur si elle n’est pas NULL et une autre valeur dans le cas contraire. Elle peut avoir plus de deux arguments. Dans ce cas, la première expression de la liste qui ne vaut pas NULL sera retournée par la fonction.

Voici quelques exemples :

Remplace les prénoms non-renseignés par la valeur X dans le résultat :

SELECT nom, COALESCE(prenom, 'X')
FROM personnes ;

   nom    | coalesce
----------+----------
 Lagaffe  | Gaston
 Fantasio | X
 Prunelle | Léon

Cette fonction est efficace également pour la concaténation précédente :

SELECT nom || ' ' || COALESCE(prenom, '') AS nom_complet FROM personnes ;

  nom_complet
----------------
 Lagaffe Gaston
 Fantasio
 Prunelle Léon

Agrégats

• Regroupement de données
• Calculs d’agrégats

Comme son nom l’indique, l’agrégation permet de regrouper des données, qu’elles viennent d’une ou de plusieurs colonnes. Le but est principalement de réaliser des calculs sur les données des lignes regroupées.

Regroupement de données

• Regroupement de données :

GROUP BY expression [, …]

- Chaque groupe de données est ensuite représenté sur une seule ligne

- Permet d’appliquer des calculs sur les ensembles regroupés

- comptage, somme, moyenne, etc.

La clause `GROUP BY` permet de réaliser des regroupements de données. Les données regroupées sont alors représentées sur une seule ligne. Le principal intérêt de ces regroupements est de permettre de réaliser des calculs sur ces données.

### Calculs d’agrégats

- Effectuent un calcul sur un ensemble de valeurs
- somme, moyenne, etc.
- Retournent `NULL` si l’ensemble est vide
- sauf `count()`

Nous allons voir les différentes fonctions d’agrégats disponibles.

### Agrégats simples

- Comptage :

```sql
count (expression)

• compte les lignes : count(*)

  • compte les valeurs renseignées : count (colonne)

• Valeur minimale :

min (expression)

- Valeur maximale :

```sql
max (expression)

La fonction count() permet de compter les éléments. La fonction est appelée de deux façons.

La première forme consiste à utiliser count(*) qui revient à transmettre la ligne complète à la fonction d’agrégat. Ainsi, toute ligne transmise à la fonction sera comptée, même si elle n’est composée que de valeurs NULL. On rencontre parfois une forme du type count(1), qui transmet une valeur arbitraire à la fonction, et qui permettait d’accélérer le temps de traitement sur certains SGBD mais qui reste sans intérêt avec PostgreSQL.

La seconde forme consiste à utiliser une expression, par exemple le nom d’une colonne : count(nom_colonne). Dans ce cas-là, seules les valeurs renseignées, donc non NULL, seront prises en compte. Les valeurs NULL seront exclues du comptage.

La fonction min() permet de déterminer la valeur la plus petite d’un ensemble de valeurs données. La fonction max() permet à l’inverse de déterminer la valeur la plus grande d’un ensemble de valeurs données. Les valeurs NULL sont bien ignorées. Ces deux fonctions permettent de travailler sur des données numériques, mais fonctionnent également sur les autres types de données comme les chaînes de caractères.

La documentation de PostgreSQL permet d’obtenir la liste des fonctions d’agrégats disponibles.

Exemples :

Différences entre count(*) et count(colonne) :

CREATE TABLE test (x INTEGER) ;
-- insertion de cinq lignes dans la table test
INSERT INTO test (x) VALUES (1), (2), (2), (NULL), (NULL) ;

SELECT x, count(*) AS count_etoile, count(x) AS count_x
FROM test
GROUP BY x ;

   x    | count_etoile | count_x
--------+--------------+---------
 (null) |            2 |       0
      1 |            1 |       1
      2 |            2 |       2

Déterminer la date de naissance de la personne la plus jeune :

SELECT MAX(date_naissance) FROM personnes ;

    max
------------
 1957-02-28

Calculs d’agrégats

• Moyenne :

avg (expression)

- Somme :

```sql
sum (expression)

• Écart-type :

stddev (expression)

- Variance :

```sql
variance (expression)

La fonction avg() permet d’obtenir la moyenne d’un ensemble de valeurs données. La fonction sum() permet, quant à elle, d’obtenir la somme d’un ensemble de valeurs données. Enfin, les fonctions stddev() et variance() permettent d’obtenir respectivement l’écart-type et la variance d’un ensemble de valeurs données.

Ces fonctions retournent NULL si aucune donnée n’est applicable. Elles ne prennent en compte que des valeurs numériques.

La documentation de PostgreSQL permet d’obtenir la liste des fonctions d’agrégats disponibles.

Exemples

Quel est le nombre total de bouteilles en stock par millésime ?

SELECT annee, sum(nombre)
FROM stock
GROUP BY annee
ORDER BY annee ;

 annee |  sum
-------+--------
  1950 | 210967
  1951 | 201977
  1952 | 202183
…

Calcul de moyenne avec des valeurs NULL :

CREATE TABLE test (a int, b int) ;

INSERT INTO test VALUES (10,10) ;
INSERT INTO test VALUES (20,20) ;
INSERT INTO test VALUES (30,30) ;
INSERT INTO test VALUES (null,0) ;

SELECT avg(a), avg(b) FROM test ;

         avg         |         avg
---------------------+---------------------
 20.0000000000000000 | 15.0000000000000000

Agrégats sur plusieurs colonnes

• Possible d’avoir plusieurs paramètres sur la même fonction d’agrégat
• Quelques exemples
  • pente : regr_slope(Y,X)
  • intersection avec l’axe des ordonnées : regr_intercept(Y,X)
  • indice de corrélation : corr (Y,X)

Une fonction d’agrégat peut aussi prendre plusieurs variables.

Par exemple concernant la méthode des « moindres carrés » :

• pente : regr_slope(Y,X)
• intersection avec l’axe des ordonnées : regr_intercept(Y,X)
• indice de corrélation : corr (Y,X)

Voici un exemple avec un nuage de points proches d’unefonction affine :

CREATE TABLE test (x real, y real) ;
INSERT INTO test VALUES (0,5.01), (1,6.99), (2,9.03) ;

SELECT regr_slope(y,x) FROM test ;

    regr_slope
------------------
 2.00999975204468

SELECT regr_intercept(y,x) FROM test ;

  regr_intercept
------------------
 5.00000015894572

SELECT corr(y,x) FROM test ;

       corr
-------------------
 0.999962873745297

Clause HAVING

• Filtrer sur des regroupements
  • HAVING
• WHERE s’applique sur les lignes lues
• HAVING s’applique sur les lignes groupées

La clause HAVING permet de filtrer les résultats sur les regroupements réalisés par la clause GROUP BY. Il est possible d’utiliser une fonction d’agrégat dans la clause HAVING.

La clause HAVING est toujours accompagnée de la clause GROUP BY. La clause GROUP BY quant à elle n’est pas toujours accompagnée de la clause HAVING.

Il faudra néanmoins faire attention à ne pas utiliser la clause HAVING comme clause de filtrage des données lues par la requête. La clause HAVING ne doit permettre de filtrer que les données traitées par la requête.

Ainsi, si l’on souhaite connaître le nombre de vins rouge référencés dans le catalogue. La requête va donc exclure toutes les données de la table vin qui ne correspondent pas au filtre type_vin = 3. Pour réaliser cela, on utilisera la clause WHERE.

En revanche, si l’on souhaite connaître le nombre de vins par type de cépage si ce nombre est supérieur à 2030, on utilisera la clause HAVING.

Exemples

SELECT type_vin_id, count(*)
  FROM vin
 GROUP BY type_vin_id
HAVING count(*) > 2030 ;

 type_vin_id | count
-------------+-------
           1 |  2031

Si la colonne correspondant à la fonction d’agrégat est renommée avec la clause AS, il n’est pas possible d’utiliser le nouveau nom au sein de la clause HAVING. Par exemple :

SELECT type_vin_id, count(*) AS nombre
  FROM vin
 GROUP BY type_vin_id
HAVING nombre > 2030 ;

ERROR:  column "nombre" does not exist

Sous-requêtes

• Corrélation requête/sous-requête
• Sous-requêtes retournant une seule ligne
• Sous-requêtes retournant une liste de valeur
• Sous-requêtes retournant un ensemble
• Sous-requêtes retournant un ensemble vide ou non-vide

Corrélation requête/sous-requête

• Fait référence à la requête principale
• Peut utiliser une valeur issue de la requête principale

Une sous-requête peut faire référence à des variables de la requête principale. Ces variables seront ainsi transformées en constante à chaque évaluation de la sous-requête.

La corrélation requête/sous-requête permet notamment de créer des clauses de filtrage dans la sous-requête en utilisant des éléments de la requête principale.

Qu’est-ce qu’une sous-requête ?

• Une requête imbriquée dans une autre requête
• Le résultat de la requête principale dépend du résultat de la sous-requête
• Encadrée par des parenthèses : ( et )

Une sous-requête consiste à exécuter une requête à l’intérieur d’une autre requête. La requête principale peut être une requête de sélection (SELECT) ou une requête de modification (INSERT, UPDATE, DELETE). La sous-requête est obligatoirement un SELECT.

Le résultat de la requête principale dépend du résultat de la sous-requête. La requête suivante effectue la sélection des colonnes d’une autre requête, qui est une sous-requête. La sous-requête effectue une lecture de la table appellation. Son résultat est transformé en un ensemble qui est nommé requete_appellation :

SELECT * FROM
  (SELECT libelle, region_id
   FROM appellation          )  requete_appellation ;

                  libelle                  | region_id
-------------------------------------------+-----------
 Ajaccio                                   |         1
 Aloxe-Corton                              |         2
…

Utiliser une seule ligne

• La sous-requête ne retourne qu’une seule ligne
  • sinon une erreur est levée
• Positionnée
  • au niveau de la liste des expressions retournées par SELECT
  • au niveau de la clause WHERE
  • au niveau d’une clause HAVING

La sous-requête peut être positionnée au niveau de la liste des expressions retournées par SELECT. La sous-requête est alors généralement un calcul d’agrégat qui ne donne en résultat qu’une seule colonne sur une seule ligne. Ce type de sous-requête est peu performant. Elle est en effet appelée pour chaque ligne retournée par la requête principale.

La requête suivante permet d’obtenir le cumul du nombre de bouteilles année par année.

SELECT annee,
       sum(nombre) AS stock,
       (SELECT sum(nombre)
          FROM stock s
         WHERE s.annee <= stock.annee) AS stock_cumule
  FROM stock
 GROUP BY annee
 ORDER BY annee ;

 annee | stock  | stock_cumule
-------+--------+--------------
  1950 | 210967 |       210967
  1951 | 201977 |       412944
  1952 | 202183 |       615127
  1953 | 202489 |       817616
  1954 | 202041 |      1019657
…

Une telle sous-requête peut également être positionnée au niveau de la clause WHERE ou de la clause HAVING.

Par exemple, pour retourner la liste des vins rouge :

SELECT *
  FROM vin
 WHERE type_vin_id = (SELECT id
                        FROM type_vin
                       WHERE libelle = 'rouge') ;

Utiliser une liste de valeurs

• La sous-requête retourne
  • plusieurs lignes
  • sur une seule colonne
• Positionnée
  • avec une clause IN
  • avec une clause ANY
  • avec une clause ALL

Les sous-requêtes retournant une liste de valeur sont plus fréquemment utilisées. Ce type de sous-requête permet de filtrer les résultats de la requête principale à partir des résultats de la sous-requête.

Clause IN

expression IN (sous-requete)

• L’expression de gauche est évaluée et vérifiée avec la liste de valeurs de droite
• IN vaut true
  • si l’expression de gauche correspond à un élément de la liste de droite
• IN vaut false
  • si aucune correspondance n’est trouvée et la liste ne contient pas NULL
• IN vaut NULL
  • si l’expression de gauche vaut NULL
  • si aucune valeur ne correspond et la liste contient NULL

La clause IN dans la requête principale permet alors d’exploiter le résultat de la sous-requête pour sélectionner les lignes dont une colonne correspond à une valeur retournée par la sous-requête.

L’opérateur IN retourne true si la valeur de l’expression de gauche est trouvée au moins une fois dans la liste de droite. La liste de droite peut contenir la valeur NULL dans ce cas :

SELECT 1 IN (1, 2, NULL) AS in ;

 in
---
 t

Si aucune correspondance n’est trouvée entre l’expression de gauche et la liste de droite, alors IN vaut false :

SELECT 1 IN (2, 4) AS in ;

 in
---
 f

Mais IN vaut NULL si aucune correspondance n’est trouvée et que la liste de droite contient au moins une valeur NULL :

SELECT 1 IN (2, 4, NULL) AS in ;

   in
--------
 (null)

IN vaut également NULL si l’expression de gauche vaut NULL :

SELECT NULL IN (2, 4) AS in ;

   in
--------
 (null)

Exemples

La requête suivante permet de sélectionner les bouteilles du stock de la cave dont la contenance est comprise entre 0,3 litre et 1 litre. Pour répondre à la question, la sous-requête retourne les identifiants de contenant qui correspondent à la condition. La requête principale ne retient alors que les lignes dont la colonne contenant_id correspond à une valeur d’identifiant retournée par la sous-requête.

SELECT *
  FROM stock
 WHERE contenant_id IN (SELECT id
                          FROM contenant
                         WHERE contenance
                       BETWEEN 0.3 AND 1.0) ;

Clause NOT IN

expression NOT IN (sous-requete)

• L’expression de droite est évaluée et vérifiée avec la liste de valeurs de gauche
• NOT IN vaut true
  • si aucune correspondance n’est trouvée et la liste ne contient pas NULL
• NOT IN vaut false
  • si l’expression de gauche correspond à un élément de la liste de droite
• NOT IN vaut NULL
  • si l’expression de gauche vaut NULL
  • si aucune valeur ne correspond et la liste contient NULL

À l’inverse, la clause NOT IN permet dans la requête principale de sélectionner les lignes dont la colonne impliquée dans la condition ne correspond pas aux valeurs retournées par la sous-requête.

La requête suivante permet de sélectionner les bouteilles du stock dont la contenance n’est pas inférieure à 2 litres.

SELECT *
  FROM stock
 WHERE contenant_id NOT IN (SELECT id
                              FROM contenant
                             WHERE contenance < 2.0) ;

Il est à noter que les requêtes impliquant les clauses IN ou NOT IN peuvent généralement être réécrites sous la forme d’une jointure.

De plus, les optimiseurs SQL parviennent difficilement à optimiser une requête impliquant NOT IN. Il est préférable d’essayer de réécrire ces requêtes en utilisant une jointure.

Avec NOT IN, la gestion des valeurs NULL est à l’inverse de celle de la clause IN :

Si une correspondance est trouvée, NOT IN vaut false :

SELECT 1 NOT IN (1, 2, NULL) AS notin ;

 notin
-------
 f

Si aucune correspondance n’est trouvée, NOT IN vaut true :

SELECT 1 NOT IN (2, 4) AS notin ;

 notin
-------
 t

Si aucune correspondance n’est trouvée mais que la liste de valeurs de droite contient au moins un NULL, NOT IN vaut NULL :

SELECT 1 NOT IN (2, 4, NULL) AS notin ;

 notin
--------
 (null)

Si l’expression de gauche vaut NULL, alors NOT IN vaut NULL également :

SELECT NULL IN (2, 4) AS notin ;

 notin
--------
 (null)

Les sous-requêtes retournant des valeurs NULL posent souvent des problèmes avec NOT IN. Il est préférable d’utiliser EXISTS ou NOT EXISTS pour ne pas avoir à se soucier des valeurs NULL.

Clause ANY

expression operateur ANY (sous-requete)

• L’expression de gauche est comparée au résultat de la sous-requête avec l’opérateur donné
• La ligne de gauche est retournée
  • si le résultat d’au moins une comparaison est vraie
• La ligne de gauche n’est pas retournée
  • si aucun résultat de la comparaison n’est vrai
  • si l’expression de gauche vaut NULL
  • si la sous-requête ramène un ensemble vide

La clause ANY, ou son synonyme SOME, permet de comparer l’expression de gauche à chaque ligne du résultat de la sous-requête en utilisant l’opérateur indiqué. Ainsi, la requête de l’exemple avec la clause IN aurait pu être écrite avec = ANY de la façon suivante :

SELECT *
  FROM stock
 WHERE contenant_id = ANY (SELECT id
                          FROM contenant
                         WHERE contenance
                       BETWEEN 0.3 AND 1.0) ;

Clause ALL

expression operateur ALL (sous-requete)

• L’expression de gauche est comparée à tous les résultats de la sous-requête avec l’opérateur donné
• La ligne de gauche est retournée
  • si tous les résultats des comparaisons sont vrais
  • si la sous-requête retourne un ensemble vide
• La ligne de gauche n’est pas retournée
  • si au moins une comparaison est fausse
  • si au moins une comparaison est NULL

La clause ALL permet de comparer l’expression de gauche à chaque ligne du résultat de la sous-requête en utilisant l’opérateur de comparaison indiqué.

La ligne de la table de gauche sera retournée si toutes les comparaisons sont vraies ou si la sous-requête retourne un ensemble vide. En revanche, la ligne de la table de gauche sera exclue si au moins une comparaison est fausse ou si au moins une comparaison est NULL.

La requête d’exemple de la clause NOT IN aurait pu être écrite avec <> ALL de la façon suivante :

SELECT *
  FROM stock
 WHERE contenant_id <> ALL (SELECT id
                              FROM contenant
                             WHERE contenance < 2.0) ;

Utiliser un ensemble

• La sous-requête retourne
  • plusieurs lignes
  • sur plusieurs colonnes
• Positionnée au niveau de la clause FROM
• Nommée avec un alias de table

La sous-requête peut être utilisée dans la clause FROM afin d’être utilisée comme une table dans la requête principale. La sous-requête devra obligatoirement être nommée avec un alias de table. Lorsqu’elles sont issues d’un calcul, les colonnes résultantes doivent également être nommées avec un alias de colonne afin d’éviter toute confusion ou comportement incohérent.

La requête suivante permet de déterminer le nombre moyen de bouteilles par année :

SELECT avg (nombre_total_annee) AS moyenne
  FROM (SELECT annee, sum(nombre) AS nombre_total_annee
          FROM stock
         GROUP BY annee) stock_total_par_annee ;

Clause EXISTS

EXISTS (sous-requête)

• Intéressant avec une corrélation
• La clause EXISTS vérifie la présence ou l’absence de résultats
  • vrai si l’ensemble est non vide
  • faux si l’ensemble est vide

EXISTS présente peu d’intérêt sans corrélation entre la sous-requête et la requête principale.

Le prédicat EXISTS est en général plus performant que IN. Lorsqu’une requête utilisant IN ne peut pas être réécrite sous la forme d’une jointure, il est recommandé d’utiliser EXISTS en lieu et place de IN. Et à l’inverse, une clause NOT IN sera réécrite avec NOT EXISTS.

La requête suivante permet d’identifier les vins pour lesquels il y a au moins une bouteille en stock :

SELECT *
  FROM vin
 WHERE EXISTS (SELECT *
                 FROM stock
                WHERE vin_id = vin.id) ;

Jointures

• Conditions de jointure dans JOIN ou dans WHERE ?
• Produit cartésien
• Jointure interne
• Jointures externes
• Jointure ou sous-requête ?

Les jointures permettent d’écrire des requêtes qui impliquent plusieurs tables. Elles permettent de combiner les colonnes de plusieurs tables selon des critères particuliers, appelés conditions de jointures.

Les jointures permettent de tirer parti du modèle de données dans lequel les tables sont associées à l’aide de clés étrangères.

Conditions de jointure : dans JOIN ou dans WHERE ?

• Jointure dans clause JOIN
  • séparation nette jointure et filtrage
  • plus lisible et maintenable
  • jointures externes propres
  • facilite le travail de l’optimiseur
• Jointure dans clause WHERE
  • historique

Bien qu’il soit possible de décrire une jointure interne sous la forme d’une requête SELECT portant sur deux tables dont la condition de jointure est décrite dans la clause WHERE, cette forme d’écriture n’est pas recommandée. Elle est essentiellement historique et se retrouve surtout dans des projets migrés sans modification.

En effet, les conditions de jointures se trouvent mélangées avec les clauses de filtrage, rendant ainsi la compréhension et la maintenance difficiles. Il arrive aussi que, noyé dans les autres conditions de filtrage, l’utilisateur oublie la configuration de jointure, ce qui aboutit à un produit cartésien, n’ayant rien à voir avec le résultat attendu, sans même parler de la lenteur de la requête.

Il est recommandé d’utiliser la syntaxe SQL:92 et d’exprimer les jointures à l’aide de la clause JOIN. D’ailleurs, cette syntaxe est la seule qui soit utilisable pour exprimer simplement et efficacement une jointure externe. Cette syntaxe facilite la compréhension de la requête mais facilite également le travail de l’optimiseur SQL qui peut déduire beaucoup plus rapidement les jointures qu’en analysant la clause WHERE pour déterminer les conditions de jointure et les tables auxquelles elles s’appliquent le cas échéant.

Comparer ces deux exemples d’une requête typique d’ERP pourtant simplifiée :

SELECT
    clients.numero,
    sum(lignes_commandes.chiffre_affaire)
FROM
    lignes_commandes
    INNER JOIN commandes ON (lignes_commandes.commande_id = commandes.id)
    INNER JOIN clients   ON (commandes.client_id = clients.id)
    INNER JOIN addresses ON (clients.adresse_id = addresses.id)
    INNER JOIN pays      ON (adresses.pays_id = pays.id)
WHERE
    pays.code = 'FR'
    AND addresses.ville = 'Strasbourg'
    AND commandes.statut = 'LIVRÉ'
    AND clients.type = 'PARTICULIER'
    AND clients.actif IS TRUE
GROUP BY clients.numero ;

et :

SELECT
    clients.numero,
    sum(lignes_commandes.chiffre_affaire)
FROM
    lignes_commandes,
    commandes,
    clients,
    addresses,
    pays
WHERE
    pays.code = 'FR'
    AND lignes_commandes.commande_id = commandes.id
    AND commandes.client_id = clients.id
    AND commandes.statut = 'LIVRÉ'
    AND clients.type = 'PARTICULIER'
    AND clients.actif IS TRUE
    AND clients.adresse_id = addresses.id
    AND adresses.pays_id = pays.id
    AND addresses.ville = 'Strasbourg'
GROUP BY clients.numero ;

Produit cartésien

• Clause CROSS JOIN
• Réalise toutes les combinaisons entre les lignes d’une table et les lignes d’une autre
• À éviter dans la mesure du possible
  • peu de cas d’utilisation
  • peu performant

Le produit cartésien peut être exprimé avec la clause de jointure CROSS JOIN :

-- préparation du jeu de données
CREATE TABLE t1 (i1 integer, v1 integer) ;
CREATE TABLE t2 (i2 integer, v2 integer) ;

INSERT INTO t1 (i1, v1) VALUES (0, 0), (1, 1) ;
INSERT INTO t2 (i2, v2) VALUES (2, 2), (3, 3) ;

-- requête CROSS JOIN
SELECT * FROM t1 CROSS JOIN t2 ;

 i1 | v1 | i2 | v2
----+----+----+----
  0 |  0 |  2 |  2
  0 |  0 |  3 |  3
  1 |  1 |  2 |  2
  1 |  1 |  3 |  3

Ou plus simplement, en listant les deux tables dans la clause FROM sans indiquer de condition de jointure :

SELECT * FROM t1, t2 ;

 i1 | v1 | i2 | v2
----+----+----+----
  0 |  0 |  2 |  2
  0 |  0 |  3 |  3
  1 |  1 |  2 |  2
  1 |  1 |  3 |  3
(4 rows)

Voici un autre exemple utilisant aussi un NOT EXISTS :

CREATE TABLE sondes (id_sonde int, nom_sonde text);
CREATE TABLE releves_horaires (
    id_sonde int,
    heure_releve timestamptz check
        (date_trunc('hour',heure_releve)=heure_releve),
    valeur numeric);

INSERT INTO sondes VALUES (1,'sonde 1'),
                          (2, 'sonde 2'),
                          (3, 'sonde 3') ;

INSERT INTO releves_horaires VALUES
  (1,'2013-01-01 12:00:00',10),
  (1,'2013-01-01 13:00:00',11),
  (1,'2013-01-01 14:00:00',12),
  (2,'2013-01-01 12:00:00',10),
  (2,'2013-01-01 13:00:00',12),
  (2,'2013-01-01 14:00:00',12),
  (3,'2013-01-01 12:00:00',10),
  (3,'2013-01-01 14:00:00',10) ;

-- quels sont les relevés manquants entre 12h et 14h ?

SELECT id_sonde,
       heures_releves
FROM sondes
CROSS JOIN generate_series('2013-01-01 12:00:00','2013-01-01 14:00:00',
    interval '1 hour') series(heures_releves)
WHERE NOT EXISTS
    (SELECT 1
     FROM releves_horaires
     WHERE releves_horaires.id_sonde=sondes.id_sonde
       AND releves_horaires.heure_releve=series.heures_releves) ;

 id_sonde |     heures_releves
----------+------------------------
        3 | 2013-01-01 13:00:00+01

Jointure interne

• Clause INNER JOIN
  • meilleure lisibilité
  • facilite le travail de l’optimiseur
• Joint deux tables entre elles
  • Selon une condition de jointure

Une jointure interne est considérée comme un produit cartésien accompagné d’une clause de jointure pour ne conserver que les lignes qui répondent à la condition de jointure. Les SGBD réalisent néanmoins l’opération plus simplement.

La condition de jointure est généralement une égalité, ce qui permet d’associer entre elles les lignes de la table à gauche et de la table à droite dont les colonnes de condition de jointure sont égales.

La jointure interne est exprimée à travers la clause INNER JOIN ou plus simplement JOIN. En effet, si le type de jointure n’est pas spécifié, l’optimiseur considère la jointure comme étant une jointure interne.

Syntaxe d’une jointure interne

• Condition de jointure par prédicats :

table1 [INNER] JOIN table2 ON prédicat […]

- Condition de jointure implicite par liste des colonnes impliquées :

```sql
table1 [INNER] JOIN table2 USING (colonne [, …])

• Liste des colonnes de même nom (dangereux) :

table1 NATURAL [INNER] JOIN table2

La clause `ON` permet d’écrire les conditions de jointures sous la forme de prédicats tels qu’on les retrouve dans une clause `WHERE`.

La clause `USING` permet de spécifier les colonnes sur lesquelles porte la jointure. Les tables jointes devront posséder toutes les colonnes sur lesquelles portent la jointure. La jointure sera réalisée en vérifiant l’égalité entre chaque colonne portant le même nom.

La clause `NATURAL` permet de réaliser la jointure entre deux tables en utilisant les colonnes qui portent le même nom sur les deux tables comme condition de jointure. `NATURAL JOIN` est fortement déconseillée car elle peut facilement entraîner des comportements inattendus.

La requête suivante permet de joindre la table appellation avec la table `region` pour déterminer l’origine d’une appellation :


  
  

  

  
SELECT apl.libelle AS appellation, reg.libelle AS region
  FROM appellation apl
  JOIN region reg
    ON (apl.region_id = reg.id) ;
### Jointure externe à gauche
- Jointure externe à gauche
- ramène le résultat de la jointure interne
- ramène l’ensemble de la table de gauche qui ne peut être joint avec la table de droite
- les attributs de la table de droite sont alors `NULL`
Il existe deux types de jointures externes : la jointure à gauche et la jointure à droite. Cela ne concerne que l’ordre de la jointure, le traitement en lui-même est identique.
### Jointure externe à droite
- Jointure externe à droite
- ramène le résultat de la jointure interne
- ramène l’ensemble de la table de droite qui ne peut être joint avec la table de gauche
- les attributs de la table de gauche sont alors `NULL`
### Jointure externe complète
- Ramène le résultat de la jointure interne
- Ramène l’ensemble de la table de gauche qui ne peut être joint avec la table de droite
- les attributs de la table de droite sont alors `NULL`
- Ramène l’ensemble de la table de droite qui ne peut être joint avec la table de gauche
- les attributs de la table de gauche sont alors `NULL`
### Syntaxe d’une jointure externe à gauche
- Condition de jointure par prédicats :
```sql
table1 LEFT [OUTER] JOIN table2 ON prédicat […]

• Condition de jointure implicite par liste des colonnes impliquées :

table1 LEFT [OUTER] JOIN table2 USING (colonne [, …])

- Liste des colonnes implicites :

```sql
table1 NATURAL LEFT [OUTER] JOIN table2

Il existe trois écritures différentes d’une jointure externe à gauche. La clause NATURAL permet de réaliser la jointure entre deux tables en utilisant les colonnes qui portent le même nom sur les deux tables comme condition de jointure.

Les voici en exemple :

• par prédicat :

SELECT article.art_titre, auteur.aut_nom FROM article LEFT JOIN auteur ON (article.aut_id=auteur.aut_id) ;

- par liste de colonnes :

```sql
SELECT article.art_titre, auteur.aut_nom FROM article LEFT JOIN auteur USING (aut_id) ;

Syntaxe d’une jointure externe à droite

• Condition de jointure par prédicats :

table1 RIGHT [OUTER] JOIN table2 ON prédicat […]

- Condition de jointure implicite par liste des colonnes impliquées :

```sql
table1 RIGHT [OUTER] JOIN table2 USING (colonne [, …])

• Liste des colonnes implicites :

table1 NATURAL RIGHT [OUTER] JOIN table2

Les jointures à droite sont moins fréquentes mais elles restent utilisées.

### Syntaxe d’une jointure externe complète

- Condition de jointure par prédicats :

```sql
table1 FULL OUTER JOIN table2 ON prédicat […]

• Condition de jointure implicite par liste des colonnes impliquées :

table1 FULL OUTER JOIN table2 USING (colonne [, …])

- Liste des colonnes implicites :

```sql
table1 NATURAL FULL OUTER JOIN table2

Jointure ou sous-requête ?

• Jointures
  • algorithmes très efficaces
  • ne gèrent pas tous les cas
• Sous-requêtes
  • parfois peu performantes
  • répondent à des besoins non couverts par les jointures

Les sous-requêtes sont fréquemment utilisées mais elles sont moins performantes que les jointures. Ces dernières permettent d’utiliser des optimisations très efficaces.

Expressions CASE

• Équivalent à l’instruction switch en C ou Java
• Emprunté au langage Ada
• Retourne une valeur en fonction du résultat de tests

CASE permet de tester différents cas. Il s’utilise de la façon suivante :

SELECT
  CASE WHEN col1=10 THEN 'dix'
       WHEN col1>10 THEN 'supérieur à 10'
       ELSE 'inférieur à 10'
  END AS test
FROM t1;

CASE simple

CASE expression
     WHEN valeur THEN expression
     WHEN valeur THEN expression
     (…)
     ELSE expression
 END

Il est possible de tester le résultat d’une expression avec CASE. Dans ce cas, chaque clause WHEN reprendra la valeur à laquelle on souhaite associer une expression particulière :

CASE nom_region
  WHEN 'Afrique'  THEN 1
  WHEN 'Amérique' THEN 2
  WHEN 'Asie'     THEN 3
  WHEN 'Europe'   THEN 4
  ELSE 0
END

CASE sur expressions

CASE WHEN expression THEN expression
     WHEN expression THEN expression
     (…)
     ELSE expression
 END

Une expression peut être évaluée pour chaque clause WHEN. Dans ce cas, l’expression CASE retourne la première expression qui est vraie. Si une autre peut satisfaire la suivante, elle ne sera pas évaluée.

Par exemple :

CASE WHEN salaire * prime < 1300 THEN salaire * prime
     WHEN salaire * prime < 3000 THEN salaire
     WHEN salaire * prime > 5000 THEN salaire * prime
END

Spécificités de CASE

• Comportement procédural
  • les expressions sont évaluées dans l’ordre d’apparition
• Transtypage
  • le type du retour de l’expression dépend du type de rang le plus élevé de toute l’expression
• Imbrication
  • des expressions CASE à l’intérieur d’autres expressions CASE
• Clause ELSE
  • recommandé

Il est possible de placer plusieurs clauses WHEN. Elles sont évaluées dans leur ordre d’apparition.

CASE nom_region
  WHEN 'Afrique'  THEN 1
  WHEN 'Amérique' THEN 2
  /* l'expression suivante ne sera jamais évaluée */
  WHEN 'Afrique'  THEN 5
  WHEN 'Asie'     THEN 1
  WHEN 'Europe'   THEN 3
  ELSE 0
END

Le type de données renvoyé par l’instruction CASE correspond au type indiqué par l’expression au niveau des THEN et du ELSE. Ce doit être le même type. Si les types de données ne correspondent pas, alors PostgreSQL retournera une erreur :

SELECT *,
  CASE nom_region
    WHEN 'Afrique'  THEN 1
    WHEN 'Amérique' THEN 2
    WHEN 'Asie'     THEN 1
    WHEN 'Europe'   THEN 3
    ELSE 'inconnu'
  END
 FROM regions ;

ERROR:  invalid input syntax for integer: "inconnu"
LIGNE 7 :     ELSE 'inconnu'

La clause ELSE n’est pas obligatoire mais fortement recommandé. En effet, si une expression CASE ne comporte pas de clause ELSE, alors la base de données ajoutera une clause ELSE NULL à l’expression.

Ainsi l’expression suivante :

CASE
  WHEN salaire < 1000 THEN 'bas'
  WHEN salaire > 3000 THEN 'haut'
END

Sera implicitement transformée de la façon suivante :

CASE
  WHEN salaire < 1000 THEN 'bas'
  WHEN salaire > 3000 THEN 'haut'
  ELSE NULL
END

Opérateurs ensemblistes

• UNION
• INTERSECT
• EXCEPT

Regroupement de deux ensembles

• Regroupement avec dédoublonnage :

requete_select1 UNION requete_select2

• Regroupement sans dédoublonnage :

requete_select1 UNION ALL requete_select2

L’opérateur ensembliste UNION permet de regrouper deux ensembles dans un même résultat.

Le dédoublonnage peut être particulièrement coûteux car il implique un tri des données.

Exemples

La requête suivante assemble les résultats de deux requêtes pour produire le résultat :

SELECT *
  FROM appellation
 WHERE region_id = 1
UNION ALL
SELECT *
  FROM appellation
 WHERE region_id = 3 ;

Intersection de deux ensembles

• Intersection de deux ensembles avec dédoublonnage :

requete_select1 INTERSECT requete_select2

• Intersection de deux ensembles sans dédoublonnage :

requete_select1 INTERSECT ALL requete_select2

L’opérateur ensembliste INTERSECT permet d’obtenir l’intersection du résultat de deux requêtes.

Le dédoublonnage peut être particulièrement coûteux car il implique un tri des données.

Exemples

L’exemple suivant n’a pas d’autre intérêt que de montrer le résultat de l’opérateur INTERSECT sur deux ensembles simples :

SELECT *
  FROM region
INTERSECT
SELECT *
  FROM region
  WHERE id = 3 ;

 id | libelle
----+---------
  3 | Alsace

Différence entre deux ensembles

• Différence entre deux ensembles avec dédoublonnage :

requete_select1 EXCEPT requete_select2

• Différence entre deux ensembles sans dédoublonnage :

requete_select1 EXCEPT ALL requete_select2

L’opérateur ensembliste EXCEPT est l’équivalent de l’opérateur MINUS d’Oracle. Il permet d’obtenir la différence entre deux ensembles : toutes les lignes présentes dans les deux ensembles sont exclues du résultat.

Le dédoublonnage peut être particulièrement coûteux car il implique un tri des données.

Exemples :

L’exemple suivant n’a pas d’autre intérêt que de montrer le résultat de l’opérateur EXCEPT sur deux ensembles simples. La première requête retourne l’ensemble des lignes de la table region alors que la seconde requête retourne la ligne qui correspond au prédicat id = 3. Cette ligne est ensuite retirée du résultat car elle est présente dans les deux ensembles de gauche et de droite :

SELECT *
  FROM region
EXCEPT
SELECT *
  FROM region
  WHERE id = 3 ;

 id |          libelle
----+----------------------------
 11 | Cotes du Rhone
 12 | Provence produit a Cassis.
 10 | Beaujolais
 19 | Savoie
  7 | Languedoc-Roussillon
  4 | Loire
  6 | Provence
 16 | Est
  8 | Bordeaux
 14 | Lyonnais
 15 | Auvergne
  2 | Bourgogne
 17 | Forez
  9 | Vignoble du Sud-Ouest
 18 | Charente
 13 | Champagne
  5 | Jura
  1 | Provence et Corse

Fonctions de base

• Transtypage
• Manipulation de chaînes
• Manipulation de types numériques
• Manipulation de dates
• Génération de jeu de données

PostgreSQL propose un nombre conséquent de fonctions permettant de manipuler les différents types de données disponibles. Les étudier de façon exhaustive n’est pas l’objet de ce module. Néanmoins, le manuel de PostgreSQL établit une liste complète des fonctions disponibles dans le SGBD.

Transtypage

• Conversion d’un type de données vers un autre type de données
• CAST (expression AS type)
• expression::type

Les opérateurs de transtypages permettent de convertir une donnée d’un type particulier vers un autre type. La conversion échoue si les types de données sont incompatibles.

Exemples

Transtypage incorrect d’une chaîne de caractères vers un entier :

SELECT 3 + '3.5'::integer ;

ERROR:  invalid input syntax for type integer: "3.5"
LIGNE 1 : select 3 + '3.5'::integer ;
                     ^

Astuce

PostgreSQL est volontairement peu flexible pour éviter certaines erreurs subtiles liées à une conversion trop hâtive.

Opérations simples sur les chaînes

• Concaténation : chaîne1 || chaîne2
• Longueur de la chaîne : char_length(chaîne)
• Conversion en minuscules : lower(chaîne)
• Conversion en majuscules : upper(chaîne)

L’opérateur de concaténation permet de concaténer deux chaînes de caractères :

SELECT 'Bonjour' || ', Monde!' ;

-----------------
 Bonjour, Monde!

Il permet aussi de concaténer une chaîne de caractères avec d’autres types de données :

SELECT 'Texte ' || 1::integer ;

---------
 Texte 1

La fonction char_length() permet de connaître la longueur d’une chaîne de caractères :

SELECT char_length('Texte' || 1::integer) ;

-------------
           6

Les fonctions lower et upper permettent de convertir une chaîne respectivement en minuscule et en majuscule :

SELECT lower('Bonjour, Monde!') ;

-----------------
 bonjour, monde!

SELECT upper('Bonjour, Monde!') ;

-----------------
 BONJOUR, MONDE!

Manipulations de chaînes

• Extrait une chaîne à partir d’une autre :
  • substring(chaîne [from int] [for int])
• Emplacement d’une sous-chaîne :
  • position(sous-chaîne in chaîne)

La fonction substring permet d’extraire une chaîne de caractères à partir d’une chaîne en entrée. Il faut lui indiquer, en plus de la chaîne source, la position de départ, et la longeur de la sous-chaîne. Par exemple :

SELECT substring('Bonjour, Monde' from 5 for 4) ;

 substring
-----------
 our,

Notez que vous pouvez aussi utiliser un appel de fonction plus standard :

SELECT substring('Bonjour, Monde', 5, 4) ;

 substring
-----------
 our,

La fonction position indique la position d’une chaîne de caractères dans la chaîne indiquée. Par exemple :

SELECT position (',' in 'Bonjour, Monde') ;

 position
----------
        8

La combinaison des deux est intéressante :

SELECT version() ;

                            version
-------------------------------------------------------------------
 PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg20.04+1) on x86_64-pc-linux-…

SELECT substring(version() from 1 for position(' on' in version()));

    substring
-----------------
 PostgreSQL 14.2

Manipulation de types numériques

• Opérations arithmétiques
• Manipulation de types numériques
• Génération de données

Opérations arithmétiques

• Addition : +
• Soustraction : -
• Multiplication : *
• Division : /
  • entière si implique des entiers !
• Reste (modulo) : %

Ces opérateurs sont classiques. L’ensemble des opérations arithmétiques disponibles sont documentées dans le manuel.

La principale surprise vient de / qui est par défaut une division entière si des entiers sont seuls impliqués :

# SELECT 100  / 101     AS div_entiere,
         100 * 1.0 / 101 AS div_non_entiere ;

 div_entiere |    div_non_entiere
-------------+------------------------
           0 | 0.99009900990099009901

Fonctions numériques courantes

• Arrondi : round (numeric)

• Troncature : trunc (numeric [, precision])

• Entier le plus petit : floor (numeric)

• Entier le plus grand : ceil (numeric)

Ces fonctions sont décrites dans la documentation.

Génération de données

-- Suite d'entiers, 1 par ligne
SELECT i   FROM   generate_series(0, 100, 1) i ;

-- Nombre entre 0 et 1.0
SELECT random() ;
-- Entre 1 et 100
SELECT (random()*100)::int ;

-- Entre 2 bornes (v17+)
SELECT random (20.0, 50.0) ;  -- numeric
SELECT random (-100, 100) ;   -- entier

La fonction generate_series est spécifique à PostgreSQL et permet de générer une suite d’entiers compris entre une borne de départ et une borne de fin :

SELECT generate_series(1, 4) ;

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

Avec un incrément pour chaque itération :

SELECT generate_series(1, 10, 4) ;

 generate_series
-----------------
               1
               5
               9
(3 rows)

Cette fonction est aussi utilisée pour générer des lignes à volonté :

SELECT i FROM generate_series(1,10,3) i ;

 i
----
  1
  4
  7
 10
(4 lignes)

La fonction random() génère un nombre aléatoire, de type double precision (synonyme de float et float8), compris entre 0 et 1.

SELECT random() ;

      random
-------------------
 0.381810061167926

Pour générer un entier compris entre 0 et 100, il suffit de réaliser la requête suivante :

SELECT round(100*random())::integer ;

 round
-------
    74

Depuis PostgreSQL 17, il existe une version plus pratique avec deux bornes, et qui peut renvoyer, selon les paramètres, un integer, un bigint, ou un numeric :

SELECT random(  1.5, 1.9 ) ,     -- numeric
       random( -100, 100),       -- int
       random( 1e10, 1e10+100) ; -- bigint

 random | random |   random
--------+--------+-------------
    1.8 |    -22 | 10000000084

Il est possible de contrôler la graine du générateur de nombres aléatoires en positionnant le paramètre de session seed :

SET seed = 0.123 ;

ou à l’aide de la fonction setseed() :

SELECT setseed(0.123) ;

La graine est un flottant compris entre -1 et 1.

Ces fonctions sont décrites dans le manuel de PostgreSQL.

Manipulation de dates

• Obtenir la date et l’heure courante
• Manipuler des dates
• Opérations arithmétiques
• Formatage de données

Date et heure courante

• Retourne la date courante : current_date
• Retourne l’heure courante : current_time
• Retourne la date et l’heure courante : current_timestamp / now()

Les fonctions current_date et current_time permettent d’obtenir respectivement la date courante et l’heure courante. La première fonction retourne le résultat sous la forme d’un type date et la seconde sous la forme d’un type time with time zone.

Préférer current_timestamp et son synonyme now() pour obtenir la date et l’heure courante, le résultat étant de type timestamp with time zone.

Exceptionnellement, les fonctions current_date, current_time et current_timestamp n’ont pas besoin d’être invoquée avec les parenthèses ouvrantes et fermantes typiques de l’appel d’une fonction. En revanche, l’appel de la fonction now() requiert ces parenthèses.

SELECT current_date ;

 current_date
--------------
 2017-10-04

SELECT current_time ;

    current_time
--------------------
 16:32:47.386689+02

SELECT current_timestamp ;

       current_timestamp
-------------------------------
 2017-10-04 16:32:50.314897+02

SELECT now();
              now
-------------------------------
 2017-10-04 16:32:53.684813+02

Il est possible d’utiliser ces variables comme valeur par défaut d’une colonne :

CREATE TABLE test (
   id int       GENERATED ALWAYS AS IDENTITY,
   dateheure    timestamp with time zone DEFAULT current_timestamp,
   valeur varchar
) ;

INSERT INTO test (valeur) VALUES ('Bonjour, monde!');

SELECT * FROM test ;

 id |           dateheure           |     valeur
----+-------------------------------+-----------------
  1 | 2020-01-30 18:34:34.067738+01 | Bonjour, monde!

Manipulation des données

• Âge
  • Par rapport à la date courante : age (timestamp)
  • Par rapport à une date de référence : age (timestamp, timestamp)

La fonction age(timestamp) permet de déterminer l’âge de la date donnée en paramètre par rapport à la date courante. L’âge sera donné sous la forme d’un type interval.

La forme age(timestamp, timestamp) permet d’obtenir l’âge d’une date par rapport à une autre date, par exemple pour connaître l’âge de Gaston Lagaffe au 5 janvier 1997 :

SELECT age(date '1997-01-05', date '1957-02-28') ;

           age
-------------------------
 39 years 10 mons 5 days

Tronquer et extraire

• Troncature d’une date : date_trunc(text, timestamp)
  • ex : date_trunc('month' from date_naissance)
• Composante de la date : extract(text, timestamp)
  • ex : extract('year' from date_naissance)

La fonction date_trunc(text, timestamp) permet de tronquer la date à une précision donnée. La précision est exprimée en anglais, et autorise les valeurs suivantes :

• microseconds
• milliseconds
• second
• minute
• hour
• day
• week
• month
• quarter
• year
• decade
• century
• millennium

La fonction date_trunc() peut agir sur une donnée de type timestamp, date ou interval. Par exemple, pour arrondir l’âge de Gaston Lagaffe de manière à ne représenter que le nombre d’années :

SELECT date_trunc('year',
    age(date '1997-01-05', date '1957-02-28')) AS age_lagaffe ;

 age_lagaffe
-------------
 39 years

La fonction extract(text from timestamp) permet d’extraire uniquement une composante donnée d’une date, par exemple l’année. Elle retourne un type de données flottant double precision.

SELECT extract('year' from
    age(date '1997-01-05', date '1957-02-28')) AS age_lagaffe ;

 age_lagaffe
-------------
          39

Arithmétique sur les dates

• Opérations arithmétiques sur timestamp, time ou date
  • date/time - date/time = interval
  • date/time + time = date/time
  • date/time + interval = date/time
• Opérations arithmétiques sur interval
  • interval * numeric = interval
  • interval / numeric = interval
  • interval + interval = interval

La soustraction de deux types de données représentant des dates permet d’obtenir un intervalle qui représente le délai écoulé entre ces deux dates :

SELECT timestamp '2012-01-01 10:23:10' - date '0001-01-01' AS soustraction ;

     soustraction
----------------------
 734502 days 10:23:10

L’addition entre deux types de données est plus restreinte. En effet, l’expression de gauche est obligatoirement de type timestamp ou date et l’expression de droite doit être obligatoirement de type time. Le résultat de l’addition permet d’obtenir une donnée de type timestamp, avec ou sans information sur le fuseau horaire selon que cette information soit présente ou non sur l’expression de gauche.

SELECT timestamp '2001-01-01 10:34:12' + time '23:56:13' AS addition ;

      addition
---------------------
 2001-01-02 10:30:25

SELECT date '2001-01-01' + time '23:56:13' AS addition ;

      addition
---------------------
 2001-01-01 23:56:13

L’addition d’une donnée datée avec une donnée de type interval permet d’obtenir un résultat du même type que l’expression de gauche :

SELECT timestamp with time zone '2001-01-01 10:34:12' +
    interval '1 day 1 hour' AS addition ;

        addition
------------------------
 2001-01-02 11:34:12+01

SELECT date '2001-01-01'  + interval '1 day 1 hour' AS addition ;

      addition
---------------------
 2001-01-02 01:00:00

SELECT time '10:34:24' + interval '1 day 1 hour' AS addition ;

 addition
----------
 11:34:24

Une donnée de type interval peut subir des opérations arithmétiques. Le résultat sera de type interval :

SELECT interval '1 day 1 hour' * 2 AS multiplication ;

 multiplication
-----------------
 2 days 02:00:00

SELECT interval '1 day 1 hour' / 2 AS division ;

 division
----------
 12:30:00

SELECT interval '1 day 1 hour' + interval '2 hour' AS addition ;

    addition
----------------
 1 day 03:00:00

SELECT interval '1 day 1 hour' - interval '2 hour' AS soustraction ;

  soustraction
-----------------
 1 day -01:00:00

Date vers chaîne

• Conversion d’une date en chaîne de caractères
  • to_char(timestamp, text)
  • ex : to_char(current_timestamp, 'DD/MM/YYYY HH24:MI:SS')

La fonction to_char() permet de restituer une date selon un format donné :

SELECT current_timestamp ;