Avant de pouvoir utiliser PostgreSQL™, vous devez l'installer. Il est possible que PostgreSQL™ soit déjà installé dans votre environnement, soit parce qu'il est inclus dans votre distribution, soit parce que votre administrateur système s'en est chargé. Dans ce cas, vous devriez obtenir les informations nécessaires pour accéder à PostgreSQL™ dans la documentation de votre distribution ou de la part de votre administrateur.

Si vous n'êtes pas sûr que PostgreSQL™ soit déjà disponible ou que vous puissiez l'utiliser pour vos tests, vous avez la possibilité de l'installer vous-même. Le faire n'est pas difficile et peut être un bon exercice. PostgreSQL™ peut être installé par n'importe quel utilisateur sans droit particulier. Aucun accès administrateur (root) n'est requis.

Si vous installez PostgreSQL™ vous-même, référez-vous au Chapitre 15, Procédure d'installation de PostgreSQL™ du code source, pour les instructions sur l'installation, puis revenez à ce guide quand l'installation est terminée. Nous vous conseillons de suivre attentivement la section sur la configuration des variables d'environnement appropriées.

Si votre administrateur n'a pas fait une installation par défaut, vous pouvez avoir à effectuer un paramétrage supplémentaire. Par exemple, si le serveur de bases de données est une machine distante, vous aurez besoin de configurer la variable d'environnement PGHOST avec le nom du serveur de bases de données. Il sera aussi peut-être nécessaire de configurer la variable d'environnement PGPORT. La démarche est la suivante : si vous essayez de démarrer un programme et qu'il se plaint de ne pas pouvoir se connecter à la base de données, vous devez consulter votre administrateur ou, si c'est vous, la documentation pour être sûr que votre environnement est correctement paramétré. Si vous n'avez pas compris le paragraphe précédent, lisez donc la prochaine section.

Avant de continuer, vous devez connaître les bases de l'architecture système de PostgreSQL™. Comprendre comment les parties de PostgreSQL™ interagissent entre elles rendra ce chapitre un peu plus clair.

Dans le jargon des bases de données, PostgreSQL™ utilise un modèle client/serveur. Une session PostgreSQL™ est le résultat de la coopération des processus (programmes) suivants :

• Un processus serveur, qui gère les fichiers de la base de données, accepte les connexions à la base de la part des applications clientes et effectue sur la base les actions des clients. Le programme serveur est appelé postgres.
• L'application cliente (l'application de l'utilisateur), qui veut effectuer des opérations sur la base de données. Les applications clientes peuvent être de nature très différentes : un client peut être un outil texte, une application graphique, un serveur web qui accède à la base de données pour afficher des pages web ou un outil spécialisé dans la maintenance de bases de données. Certaines applications clientes sont fournies avec PostgreSQL™ ; la plupart sont développées par les utilisateurs.

Comme souvent avec les applications client/serveur, le client et le serveur peuvent être sur des hôtes différents. Dans ce cas, ils communiquent à travers une connexion réseau TCP/IP. Vous devez garder cela à l'esprit car les fichiers qui sont accessibles sur la machine cliente peuvent ne pas l'être (ou l'être seulement en utilisant des noms de fichiers différents) sur la machine exécutant le serveur de bases de données.

Le serveur PostgreSQL™ peut traiter de multiples connexions simultanées depuis les clients. Dans ce but, il démarre un nouveau processus pour chaque connexion. À ce moment, le client et le nouveau processus serveur communiquent sans intervention de la part du processus postgres original. Ainsi, le processus serveur maître s'exécute toujours, attendant de nouvelles connexions clientes, tandis que le client et les processus serveurs associés vont et viennent (bien sûr, tout ceci est invisible pour l'utilisateur ; nous le mentionnons ici seulement par exhaustivité).

Le premier test pour voir si vous pouvez accéder au serveur de bases de données consiste à essayer de créer une base. Un serveur PostgreSQL™ peut gérer plusieurs bases de données. Généralement, une base de données distincte est utilisée pour chaque projet ou pour chaque utilisateur.

Il est possible que votre administrateur ait déjà créé une base pour vous. Il devrait vous avoir dit son nom. Dans ce cas, vous pouvez omettre cette étape et aller directement à la prochaine section.

Pour créer une nouvelle base, nommée ma_base dans cet exemple, utilisez la commande suivante :

$ createdb ma_base

Si cette commande ne fournit aucune réponse, cette étape est réussie et vous pouvez sauter le reste de cette section.

Si vous voyez un message similaire à :

createdb: command not found

alors PostgreSQL™ n'a pas été installé correctement. Soit il n'a pas été installé du tout, soit le chemin système n'a pas été configuré pour l'inclure. Essayez d'appeler la commande avec le chemin absolu :

$ /usr/local/pgsql/bin/createdb ma_base

Le chemin sur votre serveur peut être différent. Contactez votre administrateur ou vérifiez dans les instructions d'installation pour corriger la commande.

Voici une autre réponse possible :

Cela signifie que le serveur n'était pas démarré, ou qu'il n'était pas démarré là où createdb l'attendait. Une fois encore, vérifiez les instructions d'installation ou consultez votre administrateur.

Voici encore une autre réponse possible :

createdb: could not connect to database postgres: FATAL:  role "joe" does not exist

mais avec votre propre nom de connexion mentionné à la place de joe. Ceci survient si l'administrateur n'a pas créé de compte utilisateur PostgreSQL™ pour vous (les comptes utilisateurs PostgreSQL™ sont distincts de ceux du système d'exploitation). Si vous êtes l'administrateur, la lecture du Chapitre 20, Rôles de la base de données vous expliquera comment créer de tels comptes. Vous aurez besoin de prendre l'identité de l'utilisateur du système d'exploitation sous lequel PostgreSQL™ a été installé (généralement postgres) pour créer le compte du premier utilisateur. Cela pourrait aussi signifier que vous avez un nom d'utilisateur PostgreSQL™ qui est différent de celui de votre compte utilisateur du système d'exploitation. Dans ce cas, vous avez besoin d'utiliser l'option -U ou de configurer la variable d'environnement PGUSER pour spécifier votre nom d'utilisateur PostgreSQL™.

Si vous n'avez pas les droits requis pour créer une base, vous verrez le message suivant :

createdb: database creation failed: ERROR:  permission denied to create database

Tous les utilisateurs n'ont pas l'autorisation de créer de nouvelles bases de données. Si PostgreSQL™ refuse de créer des bases pour vous, alors il faut que l'administrateur vous accorde ce droit. Consultez votre administrateur si cela arrive. Si vous avez installé vous-même PostgreSQL™, alors vous devez ouvrir une session sous le compte utilisateur que vous avez utilisé pour démarrer le serveur. [1idp543488]

Vous pouvez aussi créer des bases de données avec d'autres noms. PostgreSQL™ vous permet de créer un nombre quelconque de bases sur un site donné. Le nom des bases doit avoir comme premier caractère un caractère alphabétique et est limité à 63 octets de longueur. Un choix pratique est de créer une base avec le même nom que votre nom d'utilisateur courant. Beaucoup d'outils utilisent ce nom comme nom par défaut pour la base : cela permet de gagner du temps en saisie. Pour créer cette base, tapez simplement :

$ createdb

Si vous ne voulez plus utiliser votre base, vous pouvez la supprimer. Par exemple, si vous êtes le propriétaire (créateur) de la base ma_base, vous pouvez la détruire en utilisant la commande suivante :

$ dropdb ma_base

(Pour cette commande, le nom de la base n'est pas par défaut le nom du compte utilisateur. Vous devez toujours en spécifier un.) Cette action supprime physiquement tous les fichiers associés avec la base de données et elle ne peut pas être annulée, donc cela doit se faire avec beaucoup de prévoyance.

createdb(1) et dropdb(1) apportent beaucoup plus d'informations sur createdb et dropdb.

Une fois que vous avez créé la base, vous pouvez y accéder :

• Démarrez le programme en ligne de commande de PostgreSQL™, appelé psql, qui vous permet de saisir, d'éditer et d'exécuter de manière interactive des commandes SQL.
• Utilisez un outil existant avec une interface graphique comme pgAdmin ou une suite bureautique avec un support ODBC ou JDBC pour créer et manipuler une base. Ces possibilités ne sont pas couvertes dans ce tutoriel.
• Écrivez une application personnalisée en utilisant un des nombreux langages disponibles. Ces possibilités sont davantage examinées dans la Partie IV, « Interfaces client ».

Vous aurez probablement besoin de lancer psql pour essayer les exemples de ce tutoriel. Pour cela, saisissez la commande suivante :

$ psql ma_base

Si vous n'indiquez pas le nom de la base, alors psql utilisera par défaut le nom de votre compte utilisateur. Vous avez déjà découvert ce principe dans la section précédente en utilisant createdb.

Dans psql, vous serez accueilli avec le message suivant :

psql (9.4.6)
Type "help" for help.
ma_base=>

La dernière ligne peut aussi être :

ma_base=#

Cela veut dire que vous êtes le super-utilisateur de la base de données, ce qui est souvent le cas si vous avez installé PostgreSQL™ vous-même. Être super-utilisateur ou administrateur signifie que vous n'êtes pas sujet aux contrôles d'accès. Concernant ce tutoriel, cela n'a pas d'importance.

Si vous rencontrez des problèmes en exécutant psql, alors retournez à la section précédente. Les diagnostiques de psql et de createdb sont semblables. Si le dernier fonctionnait, alors le premier devrait fonctionner également.

La dernière ligne affichée par psql est l'invite. Cela indique que psql est à l'écoute et que vous pouvez saisir des requêtes SQL dans l'espace de travail maintenu par psql. Essayez ces commandes :

ma_base=> SELECT version();
                               version
 -----------------------------------------------------------------------
 PostgreSQL 9.4.6 on i586-pc-linux-gnu, compiled by GCC 2.96, 32-bit
(1 row)

ma_base=> SELECT current_date;
    date
------------
 2002-08-31
(1 row)

ma_base=> SELECT 2 + 2;
 ?column?
----------
        4
(1 row)

Le programme psql dispose d'un certain nombre de commandes internes qui ne sont pas des commandes SQL. Elles commencent avec le caractère antislash (une barre oblique inverse, « \ »). Par exemple, vous pouvez obtenir de l'aide sur la syntaxe de nombreuses commandes SQL de PostgreSQL™ en exécutant :

ma_base=> \h

Pour sortir de psql, saisissez :

ma_base=> \q

et psql se terminera et vous ramènera à votre shell. Pour plus de commandes internes, saisissez \? à l'invite de psql. Les possibilités complètes de psql sont documentées dans psql(1). Dans ce tutoriel, nous ne verrons pas ces caractéristiques explicitement mais vous pouvez les utiliser vous-même quand cela vous est utile.

Ce chapitre fournit un panorama sur la façon d'utiliser SQL pour exécuter des opérations simples. Ce tutoriel est seulement prévu pour vous donner une introduction et n'est, en aucun cas, un tutoriel complet sur SQL. De nombreux livres ont été écrits sur SQL, incluant [melt93] et [date97]. Certaines caractéristiques du langage de PostgreSQL™ sont des extensions de la norme.

Dans les exemples qui suivent, nous supposons que vous avez créé une base de données appelée ma_base, comme cela a été décrit dans le chapitre précédent et que vous avez été capable de lancer psql.

Les exemples dans ce manuel peuvent aussi être trouvés dans le répertoire src/tutorial/ de la distribution source de PostgreSQL™. (Les distributions binaires de PostgreSQL™ pourraient ne pas proposer ces fichiers.) Pour utiliser ces fichiers, commencez par changer de répertoire et lancez make :

$ cd ..../src/tutorial
$ make

Ceci crée les scripts et compile les fichiers C contenant des fonctions et types définis par l'utilisateur. Puis, pour lancer le tutoriel, faites ce qui suit :

$ cd ..../tutorial
$ psql -s ma_base
...

ma_base=> \i basics.sql

La commande \i de psql lit les commandes depuis le fichier spécifié. L'option -s vous place dans un mode pas à pas qui fait une pause avant d'envoyer chaque instruction au serveur. Les commandes utilisées dans cette section sont dans le fichier basics.sql.

PostgreSQL™ est un système de gestion de bases de données relationnelles (SGBDR). Cela signifie que c'est un système pour gérer des données stockées dans des relations. Relation est essentiellement un terme mathématique pour table. La notion de stockage de données dans des tables est si commune aujourd'hui que cela peut sembler en soi évident mais il y a de nombreuses autres manières d'organiser des bases de données. Les fichiers et répertoires dans les systèmes d'exploitation de type Unix forment un exemple de base de données hiérarchique. Un développement plus moderne est une base de données orientée objets.

Chaque table est un ensemble de lignes. Chaque ligne d'une table donnée a le même ensemble de colonnes et chaque colonne est d'un type de données particulier. Tandis que les colonnes ont un ordre fixé dans chaque ligne, il est important de se rappeler que SQL ne garantit, d'aucune façon, l'ordre des lignes à l'intérieur de la table (bien qu'elles puissent être explicitement triées pour l'affichage).

Les tables sont groupées dans des bases de données et un ensemble de bases gérées par une instance unique du serveur PostgreSQL™ constitue une instance de bases (cluster en anglais).

Vous pouvez créer une nouvelle table en spécifiant le nom de la table, suivi du nom de toutes les colonnes et de leur type :

CREATE TABLE temps (
    ville           varchar(80),
    t_basse         int,           -- température basse
    t_haute         int,           -- température haute
    prcp            real,          -- précipitation
    date            date
);

Vous pouvez saisir cela dans psql avec les sauts de lignes. psql reconnaîtra que la commande n'est pas terminée jusqu'à arriver à un point-virgule.

Les espaces blancs (c'est-à-dire les espaces, les tabulations et les retours à la ligne) peuvent être librement utilisés dans les commandes SQL. Cela signifie que vous pouvez saisir la commande ci-dessus alignée différemment ou même sur une seule ligne. Deux tirets (« -- ») introduisent des commentaires. Ce qui les suit est ignoré jusqu'à la fin de la ligne. SQL est insensible à la casse pour les mots-clés et les identifiants excepté quand les identifiants sont entre double guillemets pour préserver leur casse (non fait ci-dessus).

varchar(80) spécifie un type de données pouvant contenir une chaîne de caractères arbitraires de 80 caractères au maximum. int est le type entier normal. real est un type pour les nombres décimaux en simple précision. date devrait s'expliquer de lui-même (oui, la colonne de type date est aussi nommée date ; cela peut être commode ou porter à confusion, à vous de choisir).

PostgreSQL™ prend en charge les types SQL standards int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp et interval ainsi que d'autres types d'utilité générale et un riche ensemble de types géométriques. PostgreSQL™ peut être personnalisé avec un nombre arbitraire de types de données définis par l'utilisateur. En conséquence, les noms des types ne sont pas des mots-clé dans la syntaxe sauf lorsqu'il est requis de supporter des cas particuliers dans la norme SQL.

Le second exemple stockera des villes et leur emplacement géographique associé :

CREATE TABLE villes (
    nom            varchar(80),
    emplacement    point
);

Le type point est un exemple d'un type de données spécifique à PostgreSQL™.

Pour finir, vous devez savoir que si vous n'avez plus besoin d'une table ou que vous voulez la recréer différemment, vous pouvez la supprimer en utilisant la commande suivante :

DROP TABLE nom_table;

L'instruction INSERT est utilisée pour remplir une table avec des lignes :

INSERT INTO temps VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

Notez que tous les types utilisent des formats d'entrées plutôt évident. Les constantes qui ne sont pas des valeurs numériques simples doivent être habituellement entourées par des guillemets simples (') comme dans l'exemple. Le type date est en réalité tout à fait flexible dans ce qu'il accepte mais, pour ce tutoriel, nous collerons au format non ambigu montré ici.

Le type point demande une paire de coordonnées en entrée comme cela est montré ici :

INSERT INTO villes VALUES ('San Francisco', '(-194.0, 53.0)');

La syntaxe utilisée jusqu'à maintenant nécessite de se rappeler l'ordre des colonnes. Une syntaxe alternative vous autorise à lister les colonnes explicitement :

INSERT INTO temps (ville, t_basse, t_haute, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

Vous pouvez lister les colonnes dans un ordre différent si vous le souhaitez ou même omettre certaines colonnes ; par exemple, si la précipitation est inconnue :

INSERT INTO temps (date, ville, t_haute, t_basse)
    VALUES ('1994-11-29', 'Hayward', 54, 37);

De nombreux développeurs considèrent que le listage explicite des colonnes est un meilleur style que de compter sur l'ordre implicite.

Merci d'exécuter toutes les commandes vues ci-dessus de façon à avoir des données sur lesquelles travailler dans les prochaines sections.

Vous auriez pu aussi utiliser COPY pour charger de grandes quantités de données depuis des fichiers texte. C'est habituellement plus rapide car la commande COPY est optimisée pour cet emploi mais elle est moins flexible que INSERT. Par exemple :

COPY temps FROM '/home/utilisateur/temps.txt';

où le nom du fichier source doit être disponible sur la machine qui exécute le processus serveur car le processus serveur lit le fichier directement. Vous avez plus d'informations sur la commande COPY dans COPY(7).

Pour retrouver les données d'une table, elle est interrogée. Une instruction SQL SELECT est utilisée pour faire cela. L'instruction est divisée en liste de sélection (la partie qui liste les colonnes à retourner), une liste de tables (la partie qui liste les tables à partir desquelles les données seront retrouvées) et une qualification optionnelle (la partie qui spécifie les restrictions). Par exemple, pour retrouver toutes les lignes de la table temps, saisissez :

SELECT * FROM temps;

Ici, * est un raccourci pour « toutes les colonnes ». [2idp662720] Donc, le même résultat pourrait être obtenu avec :

SELECT ville, t_basse, t_haute, prcp, date FROM temps;

Le résultat devrait être ceci :

ville     | t_basse | t_haute | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |   50    | 0.25 | 1994-11-27
 San Francisco |      43 |   57    |    0 | 1994-11-29
 Hayward       |      37 |   54    |      | 1994-11-29
(3 rows)

Vous pouvez écrire des expressions, pas seulement des références à de simples colonnes, dans la liste de sélection. Par exemple, vous pouvez faire :

SELECT ville, (t_haute+t_basse)/2 AS temp_moy, date FROM temps;

Cela devrait donner :

    ville     | temp_moy |    date
---------------+----------+------------
 San Francisco |       48 | 1994-11-27
 San Francisco |       50 | 1994-11-29
 Hayward       |       45 | 1994-11-29
(3 rows)

Notez comment la clause AS est utilisée pour renommer la sortie d'une colonne (cette clause AS est optionnelle).

Une requête peut être « qualifiée » en ajoutant une clause WHERE qui spécifie les lignes souhaitées. La clause WHERE contient une expression booléenne et seules les lignes pour lesquelles l'expression booléenne est vraie sont renvoyées. Les opérateurs booléens habituels (AND, OR et NOT) sont autorisés dans la qualification. Par exemple, ce qui suit recherche le temps à San Francisco les jours pluvieux :

SELECT * FROM temps
    WHERE ville = 'San Francisco' AND prcp > 0.0

Résultat :

     ville     | t_basse | t_haute | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)

Vous pouvez demander à ce que les résultats d'une requête soient renvoyés dans un ordre trié :

SELECT * FROM temps
ORDER BY ville;

 ville        | t_basse | t_haute | prcp |    date
--------------+---------+---------+------+------------
Hayward       |      37 |      54 |      | 1994-11-29
San Francisco |      43 |      57 |    0 | 1994-11-29
San Francisco |      46 |      50 | 0.25 | 1994-11-27

Dans cet exemple, l'ordre de tri n'est pas spécifié complètement, donc vous pouvez obtenir les lignes San Francisco dans n'importe quel ordre. Mais, vous auriez toujours obtenu les résultats affichés ci-dessus si vous aviez fait :

SELECT * FROM temps
ORDER BY ville, t_basse;

Vous pouvez demander que les lignes dupliquées soient supprimées du résultat d'une requête :

SELECT DISTINCT ville
    FROM temps;

     ville
---------------
 Hayward
 San Francisco
(2 rows)

De nouveau, l'ordre des lignes résultats pourrait varier. Vous pouvez vous assurer des résultats cohérents en utilisant DISTINCT et ORDER BY ensemble : [3idp688896]

SELECT DISTINCT ville
FROM temps
ORDER BY ville;

Jusqu'ici, nos requêtes avaient seulement consulté une table à la fois. Les requêtes peuvent accéder à plusieurs tables en même temps ou accéder à la même table de façon à ce que plusieurs lignes de la table soient traitées en même temps. Une requête qui consulte plusieurs lignes de la même ou de différentes tables en même temps est appelée requête de jointure. Comme exemple, supposez que vous souhaitez comparer la colonne ville de chaque ligne de la table temps avec la colonne nom de toutes les lignes de la table villes et que vous choisissez les paires de lignes où ces valeurs correspondent.

Ceci sera accompli avec la requête suivante :

SELECT *
    FROM temps, villes
    WHERE ville = nom;

     ville     | t_basse | t_haute | prcp |    date    |     nom       | emplacement
---------------+---------+---------+------+------------+---------------+-------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)

Deux remarques à propos du résultat :

• Il n'y a pas de lignes pour la ville de Hayward dans le résultat. C'est parce qu'il n'y a aucune entrée correspondante dans la table villes pour Hayward, donc la jointure ignore les lignes n'ayant pas de correspondance avec la table temps. Nous verrons rapidement comment cela peut être résolu.
• Il y a deux colonnes contenant le nom des villes. C'est correct car les listes des colonnes des tables temps et villes sont concaténées. En pratique, ceci est indésirable, vous voudrez probablement lister les colonnes explicitement plutôt que d'utiliser * :

SELECT ville, t_basse, t_haute, prcp, date, emplacement
    FROM temps, villes
    WHERE ville = nom;

Exercice :  Essayez de déterminer la sémantique de cette requête quand la clause WHERE est omise.

Puisque toutes les colonnes ont un nom différent, l'analyseur a automatiquement trouvé à quelle table elles appartiennent. Si des noms de colonnes sont communs entre les deux tables, vous aurez besoin de qualifier les noms des colonnes pour préciser celles dont vous parlez. Par exemple :

SELECT temps.ville, temps.t_basse, temps.t_haute,
       temps.prcp, temps.date, villes.emplacement
    FROM temps, villes
    WHERE villes.nom = temps.ville;

La qualification des noms de colonnes dans une requête de jointure est fréquemment considérée comme une bonne pratique. Cela évite l'échec de la requête si un nom de colonne dupliqué est ajouté plus tard dans une des tables.

Les requêtes de jointure vues jusqu'ici peuvent aussi être écrites sous une autre forme :

SELECT *
    FROM temps INNER JOIN villes ON (temps.ville = villes.nom);

Cette syntaxe n'est pas aussi couramment utilisée que les précédentes mais nous la montrons ici pour vous aider à comprendre les sujets suivants.

Maintenant, nous allons essayer de comprendre comment nous pouvons avoir les entrées de Hayward. Nous voulons que la requête parcourt la table temps et que, pour chaque ligne, elle trouve la (ou les) ligne(s) de villes correspondante(s). Si aucune ligne correspondante n'est trouvée, nous voulons que les valeurs des colonnes de la table villes soient remplacées par des « valeurs vides ». Ce genre de requêtes est appelé jointure externe (outer join). (Les jointures que nous avons vus jusqu'ici sont des jointures internes -- inner joins). La commande ressemble à cela :

SELECT *
    FROM temps LEFT OUTER JOIN villes ON (temps.ville = villes.nom);

    ville     | t_basse | t_haute | prcp |    date    |     nom       | emplacement
---------------+---------+---------+------+------------+---------------+-------------
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)

Cette requête est appelée une jointure externe à gauche (left outer join) parce que la table mentionnée à la gauche de l'opérateur de jointure aura au moins une fois ses lignes dans le résultat tandis que la table sur la droite aura seulement les lignes qui correspondent à des lignes de la table de gauche. Lors de l'affichage d'une ligne de la table de gauche pour laquelle il n'y a pas de correspondance dans la table de droite, des valeurs vides (appelées NULL) sont utilisées pour les colonnes de la table de droite.

Exercice :  Il existe aussi des jointures externes à droite et des jointures externes complètes. Essayez de trouver ce qu'elles font.

Nous pouvons également joindre une table avec elle-même. Ceci est appelé une jointure réflexive. Comme exemple, supposons que nous voulons trouver toutes les entrées de temps qui sont dans un intervalle de température d'autres entrées de temps. Nous avons donc besoin de comparer les colonnes t_basse et t_haute de chaque ligne de temps aux colonnes t_basse et t_haute de toutes les autres lignes de temps. Nous pouvons faire cela avec la requête suivante :

SELECT T1.ville, T1.t_basse AS bas, T1.t_haute AS haut,
    T2.ville, T2.t_basse AS bas, T2.t_haute AS haus
    FROM temps T1, temps T2
    WHERE T1.t_basse < T2.t_basse
    AND T1.t_haute > T2.t_haute;

     ville      | bas | haut |     ville     | bas | haut
----------------+-----+------+---------------+-----+------
  San Francisco |  43 |   57 | San Francisco |  46 |   50
  Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

Dans cet exemple, nous avons renommé la table temps en T1 et en T2 pour être capable de distinguer respectivement le côté gauche et droit de la jointure. Vous pouvez aussi utiliser ce genre d'alias dans d'autres requêtes pour économiser de la frappe, c'est-à-dire :

SELECT *
    FROM temps t, villes v
    WHERE t.ville = v.nom;

Vous rencontrerez ce genre d'abréviation assez fréquemment.

Comme la plupart des autres produits de bases de données relationnelles, PostgreSQL™ supporte les fonctions d'agrégat. Une fonction d'agrégat calcule un seul résultat à partir de plusieurs lignes en entrée. Par exemple, il y a des agrégats pour calculer le nombre (count), la somme (sum), la moyenne (avg), le maximum (max) et le minimum (min) d'un ensemble de lignes.

Comme exemple, nous pouvons trouver la température la plus haute parmi les températures basses avec :

SELECT max(t_basse) FROM temps;

 max
-----
  46
(1 row)

Si nous voulons connaître dans quelle ville (ou villes) ces lectures se sont produites, nous pouvons essayer :

SELECT ville FROM temps WHERE t_basse = max(t_basse);
FAUX

mais cela ne marchera pas puisque l'agrégat max ne peut pas être utilisé dans une clause WHERE (cette restriction existe parce que la clause WHERE détermine les lignes qui seront traitées par l'agrégat ; donc les lignes doivent être évaluées avant que les fonctions d'agrégat ne calculent leur résultat). Cependant, comme cela est souvent le cas, la requête peut être répétée pour arriver au résultat attendu, ici en utilisant une sous-requête :

SELECT ville FROM temps
    WHERE t_basse = (SELECT max(t_basse) FROM temps);

     ville
---------------
 San Francisco
(1 row)

Ceci est correct car la sous-requête est un calcul indépendant qui traite son propre agrégat séparément à partir de ce qui se passe dans la requête externe.

Les agrégats sont également très utiles s'ils sont combinés avec les clauses GROUP BY. Par exemple, nous pouvons obtenir la température la plus haute parmi les températures basses observées dans chaque ville avec :

SELECT ville, max(t_basse)
    FROM temps
    GROUP BY ville;

     ville     | max
---------------+-----
 Hayward       |  37
 San Francisco |  46
(2 rows)

ce qui nous donne une ligne par ville dans le résultat. Chaque résultat d'agrégat est calculé avec les lignes de la table correspondant à la ville. Nous pouvons filtrer ces lignes groupées en utilisant HAVING :

SELECT ville, max(t_basse)
    FROM temps
    GROUP BY ville
    HAVING max(t_basse) < 40;

  ville  | max
---------+-----
 Hayward |  37
(1 row)

ce qui nous donne le même résultat uniquement pour les villes qui ont toutes leurs valeurs de t_basse en-dessous de 40. Pour finir, si nous nous préoccupons seulement des villes dont le nom commence par « S », nous pouvons faire :

SELECT ville, max(t_basse)
    FROM temps
    WHERE ville LIKE 'S%'
    GROUP BY ville
    HAVING max(t_basse) < 40;

Il est important de comprendre l'interaction entre les agrégats et les clauses SQL WHERE et HAVING. La différence fondamentale entre WHERE et HAVING est que WHERE sélectionne les lignes en entrée avant que les groupes et les agrégats ne soient traités (donc, cette clause contrôle les lignes qui se retrouvent dans le calcul de l'agrégat) tandis que HAVING sélectionne les lignes groupées après que les groupes et les agrégats aient été traités. Donc, la clause WHERE ne doit pas contenir de fonctions d'agrégat ; cela n'a aucun sens d'essayer d'utiliser un agrégat pour déterminer les lignes en entrée des agrégats. D'un autre côté, la clause HAVING contient toujours des fonctions d'agrégat (pour être précis, vous êtes autorisés à écrire une clause HAVING qui n'utilise pas d'agrégats mais c'est rarement utilisé. La même condition pourra être utilisée plus efficacement par un WHERE).

Dans l'exemple précédent, nous pouvons appliquer la restriction sur le nom de la ville dans la clause WHERE puisque cela ne nécessite aucun agrégat. C'est plus efficace que d'ajouter la restriction dans HAVING parce que nous évitons le groupement et les calculs d'agrégat pour toutes les lignes qui ont échoué lors du contrôle fait par WHERE.

Vous pouvez mettre à jour une ligne existante en utilisant la commande UPDATE. Supposez que vous découvrez que les températures sont toutes excédentes de deux degrés après le 28 novembre. Vous pouvez corriger les données de la façon suivante :

UPDATE temps
    SET t_haute = t_haute - 2,  t_basse = t_basse - 2
    WHERE date > '1994-11-28';

Regardez le nouvel état des données :

SELECT * FROM temps;

     ville      | t_basse | t_haute | prcp |    date
----------------+---------+---------+------+------------
 San Francisco  |      46 |      50 | 0.25 | 1994-11-27
 San Francisco  |      41 |      55 |    0 | 1994-11-29
 Hayward        |      35 |      52 |      | 1994-11-29
(3 rows)

Les lignes peuvent être supprimées de la table avec la commande DELETE. Supposez que vous n'êtes plus intéressé par le temps de Hayward. Vous pouvez faire ce qui suit pour supprimer ses lignes de la table :

DELETE FROM temps WHERE ville = 'Hayward';

Toutes les entrées de temps pour Hayward sont supprimées.

SELECT * FROM temps;

     ville     | t_basse | t_haute | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      41 |      55 |    0 | 1994-11-29
(2 rows)

Faire très attention aux instructions de la forme :

DELETE FROM nom_table;

Sans une qualification, DELETE supprimera toutes les lignes de la table donnée, la laissant vide. Le système le fera sans demander de confirmation !

Le chapitre précédent couvre les bases de l'utilisation de SQL pour le stockage et l'accès aux données avec PostgreSQL™. Il est temps d'aborder quelques fonctionnalités avancées du SQL qui simplifient la gestion et empêchent la perte ou la corruption des données. Quelques extensions de PostgreSQL™ sont également abordées.

Ce chapitre fait occasionnellement référence aux exemples disponibles dans le Chapitre 2, Le langage SQL pour les modifier ou les améliorer. Il est donc préférable d'avoir lu ce chapitre. Quelques exemples de ce chapitre sont également disponibles dans advanced.sql situé dans le répertoire du tutoriel. De plus, ce fichier contient quelques données à charger pour utiliser l'exemple. Cela n'est pas repris ici (on peut se référer à la Section 2.1, « Introduction » pour savoir comment utiliser ce fichier).

Se référer aux requêtes de la Section 2.6, « Jointures entre les tables ». Si la liste des enregistrements du temps et des villes est d'un intérêt particulier pour l'application considérée mais qu'il devient contraignant de saisir la requête à chaque utilisation, il est possible de créer une vue avec la requête. De ce fait, la requête est nommée et il peut y être fait référence de la même façon qu'il est fait référence à une table :

CREATE VIEW ma_vue AS
    SELECT ville, t_basse, t_haute, prcp, date, emplacement
        FROM temps, villes
        WHERE ville = nom;

SELECT * FROM ma_vue;

L'utilisation des vues est un aspect clé d'une bonne conception des bases de données SQL. Les vues permettent d'encapsuler les détails de la structure des tables. Celle-ci peut alors changer avec l'évolution de l'application, tandis que l'interface reste constante.

Les vues peuvent être utilisées dans quasiment toutes les situations où une vraie table est utilisable. De plus, il n'est pas inhabituel de construire des vues reposant sur d'autres vues.

Soient les tables temps et villes définies dans le Chapitre 2, Le langage SQL. Il s'agit maintenant de s'assurer que personne n'insère de ligne dans la table temps qui ne corresponde à une entrée dans la table villes. On appelle cela maintenir l'intégrité référentielle des données. Dans les systèmes de bases de données simplistes, lorsqu'au moins c'est possible, cela est parfois obtenu par la vérification préalable de l'existence d'un enregistrement correspondant dans la table villes, puis par l'insertion, ou l'interdiction, du nouvel enregistrement dans temps. Puisque cette approche, peu pratique, présente un certain nombre d'inconvénients, PostgreSQL™ peut se charger du maintien de l'intégrité référentielle.

La nouvelle déclaration des tables ressemble alors à ceci :

CREATE TABLE villes (
        ville       varchar(80) primary key,
        emplacement point
);

CREATE TABLE temps (
        ville      varchar(80) references villes,
        t_haute int,
        t_basse int,
        prcp       real,
        date       date
);

Lors d'une tentative d'insertion d'enregistrement non valide :

INSERT INTO temps VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

Le comportement des clés étrangères peut être adapté très finement à une application particulière. Ce tutoriel ne va pas plus loin que cet exemple simple. De plus amples informations sont accessibles dans le Chapitre 5, Définition des données. Une utilisation efficace des clés étrangères améliore la qualité des applications accédant aux bases de données. Il est donc fortement conseillé d'apprendre à les utiliser.

Les transactions sont un concept fondamental de tous les systèmes de bases de données. Une transaction assemble plusieurs étapes en une seule opération tout-ou-rien. Les états intermédiaires entre les étapes ne sont pas visibles par les transactions concurrentes. De plus, si un échec survient qui empêche le succès de la transaction, alors aucune des étapes n'affecte la base de données.

Si l'on considère, par exemple, la base de données d'une banque qui contient le solde de différents comptes clients et le solde total des dépôts par branches et que l'on veuille enregistrer un virement de 100 euros du compte d'Alice vers celui de Bob, les commandes SQL peuvent ressembler à cela (après simplification) :

UPDATE comptes SET balance = balance - 100.00
    WHERE nom = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE nom = (SELECT nom_branche FROM comptes WHERE nom = 'Alice');
UPDATE comptes SET balance = balance + 100.00
    WHERE nom = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE nom = (SELECT nom_branche FROM comptes WHERE nom = 'Bob');

Ce ne sont pas les détails des commandes qui importent ici ; le point important est la nécessité de plusieurs mises à jour séparées pour accomplir cette opération assez simple. Les employés de la banque veulent être assurés que, soit toutes les commandes sont effectuées, soit aucune ne l'est. Il n'est pas envisageable que, suite à une erreur du système, Bob reçoive 100 euros qui n'ont pas été débités du compte d'Alice. De la même façon, Alice ne restera pas longtemps une cliente fidèle si elle est débitée du montant sans que celui-ci ne soit crédité sur le compte de Bob. Il est important de garantir que si quelque chose se passe mal, aucune des étapes déjà exécutées n'est prise en compte. Le regroupement des mises à jour au sein d'une transaction apporte cette garantie. Une transaction est dite atomique : du point de vue des autres transactions, elle passe complètement ou pas du tout.

Il est également nécessaire de garantir qu'une fois la transaction terminée et validée par la base de données, les transactions sont enregistrées définitivement et ne peuvent être perdues, même si une panne survient peu après. Ainsi, si un retrait d'argent est effectué par Bob, il ne faut absolument pas que le débit de son compte disparaisse suite à une panne survenant juste après son départ de la banque. Une base de données transactionnelle garantit que toutes les mises à jour faites lors d'une transaction sont stockées de manière persistante (c'est-à-dire sur disque) avant que la transaction ne soit déclarée validée.

Une autre propriété importante des bases de données transactionnelles est en relation étroite avec la notion de mises à jour atomiques : quand plusieurs transactions sont lancées en parallèle, aucune d'entre elles ne doit être capable de voir les modifications incomplètes effectuées par les autres. Ainsi, si une transaction calcule le total de toutes les branches, inclure le débit de la branche d'Alice sans le crédit de la branche de Bob, ou vice-versa, est une véritable erreur. Les transactions doivent donc être tout-ou-rien, non seulement pour leur effet persistant sur la base de données, mais aussi pour leur visibilité au moment de leur exécution. Les mises à jour faites jusque-là par une transaction ouverte sont invisibles aux autres transactions jusqu'à la fin de celle-ci. À ce moment, toutes les mises à jours deviennent simultanément visibles.

Sous PostgreSQL™, une transaction est déclarée en entourant les commandes SQL de la transaction par les commandes BEGIN et COMMIT. La transaction bancaire ressemble alors à ceci :

BEGIN;
UPDATE comptes SET balance = balance - 100.00
    WHERE nom = 'Alice';
-- etc etc
COMMIT;

Si, au cours de la transaction, il est décidé de ne pas valider (peut-être la banque s'aperçoit-elle que la balance d'Alice passe en négatif), la commande ROLLBACK peut être utilisée à la place de COMMIT. Toutes les mises à jour réalisées jusque-là sont alors annulées.

En fait, PostgreSQL™ traite chaque instruction SQL comme si elle était exécutée dans une transaction. En l'absence de commande BEGIN explicite, chaque instruction individuelle se trouve implicitement entourée d'un BEGIN et (en cas de succès) d'un COMMIT. Un groupe d'instructions entourées par BEGIN et COMMIT est parfois appelé bloc transactionnel.

Il est possible d'augmenter la granularité du contrôle des instructions au sein d'une transaction en utilisant des points de retournement (savepoint). Ceux-ci permettent d'annuler des parties de la transaction tout en validant le reste. Après avoir défini un point de retournement à l'aide de SAVEPOINT, les instructions exécutées depuis ce point peuvent, au besoin, être annulées avec ROLLBACK TO. Toutes les modifications de la base de données effectuées par la transaction entre le moment où le point de retournement a été défini et celui où l'annulation est demandée sont annulées mais les modifications antérieures à ce point sont conservées.

Le retour à un point de retournement ne l'annule pas. Il reste défini et peut donc être utilisé plusieurs fois. À l'inverse, lorsqu'il n'est plus nécessaire de revenir à un point de retournement particulier, il peut être relâché, ce qui permet de libérer des ressources systèmes. Il faut savoir toutefois que relâcher un point de retournement ou y revenir relâche tous les points de retournement qui ont été définis après.

Tout ceci survient à l'intérieur du bloc de transaction, et n'est donc pas visible par les autres sessions en cours sur la base de données. Si le bloc est validé, et à ce moment-là seulement, toutes les actions validées deviennent immédiatement visibles par les autres sessions, tandis que les actions annulées ne le seront jamais.

Reconsidérant la base de données de la banque, on peut supposer vouloir débiter le compte d'Alice de $100.00, somme à créditer sur le compte de Bob, mais considérer plus tard que c'est le compte de Wally qu'il convient de créditer. À l'aide des points de retournement, cela peut se dérouler ainsi :

BEGIN;
UPDATE comptes SET balance = balance - 100.00
    WHERE nom = 'Alice';
SAVEPOINT mon_pointdesauvegarde;
UPDATE comptes SET balance = balance + 100.00
    WHERE nom = 'Bob';
-- oups ... oublions ça et créditons le compte de Wally
ROLLBACK TO mon_pointdesauvegarde;
UPDATE comptes SET balance = balance + 100.00
    WHERE nom = 'Wally';
COMMIT;

Cet exemple est bien sûr très simplifié mais de nombreux contrôles sont réalisables au sein d'un bloc de transaction grâce à l'utilisation des points de retournement. Qui plus est, ROLLBACK TO est le seul moyen de regagner le contrôle d'un bloc de transaction placé dans un état d'annulation par le système du fait d'une erreur. C'est plus rapide que de tout annuler pour tout recommencer.

Une fonction de fenêtrage effectue un calcul sur un jeu d'enregistrements liés d'une certaine façon à l'enregistrement courant. On peut les rapprocher des calculs réalisables par une fonction d'agrégat mais, contrairement à une fonction d'agrégat, l'utilisation d'une fonction de fenêtrage (de fenêtrage) n'entraîne pas le regroupement des enregistrements traités en un seul. Chaque enregistrement garde son identité propre. En coulisse, la fonction de fenêtrage est capable d'accéder à d'autres enregistrements que l'enregistrement courant du résultat de la requête.

Voici un exemple permettant de comparer le salaire d'un employé avec le salaire moyen de sa division :

SELECT nomdep, noemp, salaire, avg(salaire) OVER (PARTITION BY nomdep) FROM salaireemp;

 nomdep   | noemp | salaire |          avg
-----------+-------+---------+-----------------------
 develop   |    11 |   5200  | 5020.0000000000000000
 develop   |     7 |   4200  | 5020.0000000000000000
 develop   |     9 |   4500  | 5020.0000000000000000
 develop   |     8 |   6000  | 5020.0000000000000000
 develop   |    10 |   5200  | 5020.0000000000000000
 personnel |     5 |   3500  | 3700.0000000000000000
 personnel |     2 |   3900  | 3700.0000000000000000
 ventes    |     3 |   4800  | 4866.6666666666666667
 ventes    |     1 |   5000  | 4866.6666666666666667
 ventes    |     4 |   4800  | 4866.6666666666666667
(10 rows)

Les trois premières colonnes viennent directement de la table salaireemp, et il y a une ligne de sortie pour chaque ligne de la table. La quatrième colonne représente une moyenne calculée sur tous les enregistrements de la table qui ont la même valeur de nomdep que la ligne courante. (Il s'agit effectivement de la même fonction que la fonction d'agrégat classique avg, mais la clause OVER entraîne son exécution en tant que fonction de fenêtrage et son calcul sur le jeu approprié d'enregistrements.)

Un appel à une fonction de fenêtrage contient toujours une clause OVER qui suit immédiatement le nom et les arguments de la fonction. C'est ce qui permet de la distinguer syntaxiquement d'une fonction simple ou d'une fonction d'agrégat. La clause OVER détermine précisément comment les lignes de la requête sont éclatées pour être traitées par la fonction de fenêtrage. La liste PARTITION BY contenue dans la clause OVER spécifie la répartition des enregistrements en groupes, ou partitions, qui partagent les mêmes valeurs pour la (les) expression(s) contenue(s) dans la clause PARTITION BY. Pour chaque enregistrement, la fonction de fenêtrage est calculée sur les enregistrements qui se retrouvent dans la même partition que l'enregistrement courant.

Vous pouvez aussi contrôler l'ordre dans lequel les lignes sont traitées par les fonctions de fenêtrage en utilisant la clause ORDER BY à l'intérieur de la clause OVER (la partition traitée par le ORDER BY n'a de plus pas besoin de correspondre à l'ordre dans lequel les lignes seront affichées). Voici un exemple :

SELECT nomdep, noemp, salaire,
       rank() OVER (PARTITION BY nomdep ORDER BY salaire DESC)
FROM salaireemp;

nomdep   | noemp | salaire| rank
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 ventes    |     1 |   5000 |    1
 ventes    |     4 |   4800 |    2
 ventes    |     3 |   4800 |    2
(10 rows)

On remarque que la fonction rank produit un rang numérique dans la partition de l'enregistrement pour chaque valeur différente de l'ORDER BY, dans l'ordre défini par la clause ORDER BY. rank n'a pas besoin de paramêtre explicite, puisque son comportement est entièrement déterminé par la clause OVER.

Les lignes prises en compte par une fonction de fenêtrage sont celles de la table virtuelle produite par la clause FROM de la requête filtrée par ses clauses WHERE, GROUP BY et HAVING, s'il y en a. Par exemple, une ligne rejetée parce qu'elle ne satisfait pas à la condition WHERE n'est vue par aucune fonction de fenêtrage. Une requête peut contenir plusieurs de ces fonctions de fenêtrage qui découpent les données de façons différentes, par le biais de clauses OVER différentes, mais elles travaillent toutes sur le même jeu d'enregistrements, défini par cette table virtuelle.

ORDER BY peut être omis lorsque l'ordre des enregistrements est sans importance. Il est aussi possible d'omettre PARTITION BY, auquel cas il n'y a qu'une seule partition, contenant tous les enregistrements.

Il y a un autre concept important associé aux fonctions de fenêtrage : pour chaque enregistrement, il existe un jeu d'enregistrements dans sa partition appelé son window frame (cadre de fenêtre). Beaucoup de fonctions de fenêtrage, mais pas toutes, travaillent uniquement sur les enregistrements du window frame, plutôt que sur l'ensemble de la partition. Par défaut, si on a précisé une clause ORDER BY, la window frame contient tous les enregistrements du début de la partition jusqu'à l'enregistrement courant, ainsi que tous les enregistrements suivants qui sont égaux à l'enregistrement courant au sens de la clause ORDER BY. Quand ORDER BY est omis, la window frame par défaut contient tous les enregistrements de la partition. [4idp862816] Voici un exemple utilisant sum :

SELECT salaire, sum(salaire) OVER () FROM salaireemp;

 salaire|  sum
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)

Dans l'exemple ci-dessus, puisqu'il n'y a pas d'ORDER BY dans la clause OVER, la window frame est égale à la partition ; en d'autres termes, chaque somme est calculée sur toute la table, ce qui fait qu'on a le même résultat pour chaque ligne du résultat. Mais si on ajoute une clause ORDER BY, on a un résultat très différent :

SELECT salaire, sum(salaire) OVER (ORDER BY salaire) FROM salaireemp;

 salaire|  sum
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

Ici, sum est calculé à partir du premier salaire (c'est-à-dire le plus bas) jusqu'au salaire courant, en incluant tous les doublons du salaire courant (remarquez les valeurs pour les salaires identiques).

Les fonctions window ne sont autorisées que dans la liste SELECT et la clause ORDER BY de la requête. Elles sont interdites ailleurs, comme par exemple dans les clauses GROUP BY,HAVING et WHERE. La raison en est qu'elles sont exécutées après le traitement de ces clauses. Par ailleurs, les fonctions de fenêtrage s'exécutent après les fonctions d'agrégat classiques. Cela signifie qu'il est permis d'inclure une fonction d'agrégat dans les arguments d'une fonction de fenêtrage, mais pas l'inverse.

S'il y a besoin de filtrer ou de grouper les enregistrements après le calcul des fonctions de fenêtrage, une sous-requête peut être utilisée. Par exemple :

SELECT nomdep, noemp, salaire, date_embauche
FROM
  (SELECT nomdep, noemp, salaire, date_embauche,
          rank() OVER (PARTITION BY nomdep ORDER BY salaire DESC, noemp) AS pos
     FROM salaireemp
  ) AS ss
WHERE pos < 3;

La requête ci-dessus n'affiche que les enregistrements de la requête interne ayant un rang inférieur à 3.

Quand une requête met en jeu plusieurs fonctions de fenêtrage, il est possible d'écrire chacune avec une clause OVER différente, mais cela entraîne des duplications de code et augmente les risques d'erreurs si on souhaite le même comportement pour plusieurs fonctions de fenêtrage. À la place, chaque comportement de fenêtrage peut être associé à un nom dans une clause WINDOW et ensuite être référencé dans OVER. Par exemple :

SELECT sum(salaire) OVER w, avg(salaire) OVER w
  FROM salaireemp
  WINDOW w AS (PARTITION BY nomdep ORDER BY salaire DESC);

Plus de détails sur les fonctions de fenêtrage sont disponibles dans la Section 4.2.8, « Appels de fonction de fenêtrage », la Section 9.21, « Fonctions Window », la Section 7.2.4, « Traitement de fonctions Window » et la page de référence SELECT(7).

L'héritage est un concept issu des bases de données orientées objet. Il ouvre de nouvelles possibilités intéressantes en conception de bases de données.

Soient deux tables : une table villes et une table capitales. Les capitales étant également des villes, il est intéressant d'avoir la possibilité d'afficher implicitement les capitales lorsque les villes sont listées. Un utilisateur particulièrement brillant peut écrire ceci 

CREATE TABLE capitales (
  nom        text,
  population real,
  altitude   int,    -- (en pied)
  etat       char(2)
);

CREATE TABLE non_capitales (
  nom        text,
  population real,
  altitude   int     -- (en pied)
);

CREATE VIEW villes AS
  SELECT nom, population, altitude FROM capitales
    UNION
  SELECT nom, population, altitude FROM non_capitales;

Cela fonctionne bien pour les requêtes, mais la mise à jour d'une même donnée sur plusieurs lignes devient vite un horrible casse-tête.

Une meilleure solution peut être :

CREATE TABLE villes (
  nom        text,
  population real,
  altitude   int     -- (en pied)
);

CREATE TABLE capitales (
  etat      char(2)
) INHERITS (villes);

Dans ce cas, une ligne de capitales hérite de toutes les colonnes (nom, population et altitude) de son parent, villes. Le type de la colonne nom est text, un type natif de PostgreSQL™ pour les chaînes de caractères à longueur variable. Les capitales d'état ont une colonne supplémentaire, etat, qui affiche l'état dont elles sont la capitale. Sous PostgreSQL™, une table peut hériter de zéro à plusieurs autres tables.

La requête qui suit fournit un exemple d'extraction des noms de toutes les villes, en incluant les capitales des états, situées à une altitude de plus de 500 pieds :

SELECT nom, altitude
  FROM villes
  WHERE altitude > 500;

ce qui renvoie :

   nom     | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
 Madison   |      845
(3 rows)

À l'inverse, la requête qui suit récupère toutes les villes qui ne sont pas des capitales et qui sont situées à une altitude d'au moins 500 pieds :

SELECT nom, altitude
    FROM ONLY villes
    WHERE altitude > 500;

nom     | altitude
-----------+----------
 Las Vegas |     2174
 Mariposa  |     1953
(2 rows)

Ici, ONLY avant villes indique que la requête ne doit être exécutée que sur la table villes, et non pas sur les tables en dessous de villes dans la hiérarchie des héritages. La plupart des commandes déjà évoquées -- SELECT, UPDATE et DELETE -- supportent cette notation (ONLY).

PostgreSQL™ dispose d'autres fonctionnalités non décrites dans ce tutoriel d'introduction orienté vers les nouveaux utilisateurs de SQL. Ces fonctionnalités sont discutées plus en détails dans le reste de ce livre.

Si une introduction plus approfondie est nécessaire, le lecteur peut visiter le site web de PostgreSQL qui fournit des liens vers d'autres ressources.