Documentation officielle PostgreSQL 9.4.6

PostgreSQL FR

Cet ouvrage représente l'adaptation française de la documentation officielle de PostgreSQL™. Celle-ci a été rédigée par les développeurs de PostgreSQL™ et quelques volontaires en parallèle du développement du logiciel. Elle décrit toutes les fonctionnalités officiellement supportées par la dernière version de PostgreSQL™.

Afin de faciliter l'accès aux informations qu'il contient, cet ouvrage est organisé en plusieurs parties. Chaque partie est destinée à une classe précise d'utilisateurs ou à des utilisateurs de niveaux d'expertise différents :

- la Partie I, « Tutoriel » est une introduction informelle destinée aux nouveaux utilisateurs ;

- la Partie II, « Langage SQL » présente l'environnement du langage de requêtes SQL, notamment les types de données, les fonctions et les optimisations utilisateurs. Tout utilisateur de PostgreSQL™ devrait la lire ;

- la Partie III, « Administration du serveur », destinée aux administrateurs PostgreSQL™, décrit l'installation et l'administration du serveur ;

- la Partie IV, « Interfaces client » décrit les interfaces de programmation ;

- la Partie V, « Programmation serveur », destinée aux utilisateurs expérimentés, présente les éléments d'extension du serveur, notamment les types de données et les fonctions utilisateurs ;

- la Partie VI, « Référence » contient la documentation de référence de SQL et des programmes client et serveur. Cette partie est utilisée comme référence par les autres parties ;

- la Partie VII, « Internes » contient diverses informations utiles aux développeurs de PostgreSQL™.

- Titre : Documentation officielle PostgreSQL 9.4.6

- Auteur : PostgreSQL FR

- Parution : 2 mai 2016

- Public visé :

- Licence : Le contenu de cet article est rédigé par PostgreSQL FR et est mis à disposition selon les termes de la Licence Creative Commons Attribution - Pas d'Utilisation Commerciale 3.0 non transposé. Les logos Developpez.com, en-tête, pied de page, css, et look & feel de l'article sont Copyright ® 2013 Developpez.com.

Sommaire

- I. Introduction

- I-A. Définition de PostgreSQL™

- I-B. Bref historique de PostgreSQL™

- I-B-1. Le projet POSTGRES à Berkeley

- I-B-2. Postgres95

- I-B-3. PostgreSQL

- I-C. Conventions

- I-D. Pour plus d'informations

- I-E. Lignes de conduite pour les rapports de bogues

- I-E-1. Identifier les bogues

- I-E-2. Que rapporter ?

- I-E-3. Où rapporter des bogues ?

- II. Tutoriel

- II-A. Démarrage

- II-A-1. Installation

- II-A-2. Concepts architecturaux

- II-A-3. Création d'une base de données

- II-A-4. Accéder à une base

- II-B. Le langage SQL

- II-B-1. Introduction

- II-B-2. Concepts

- II-B-3. Créer une nouvelle table

- II-B-4. Remplir une table avec des lignes

- II-B-5. Interroger une table

- II-B-6. Jointures entre les tables

- II-B-7. Fonctions d'agrégat

- II-B-8. Mises à jour

- II-B-9. Suppressions

- II-C. Fonctionnalités avancées

- II-C-1. Introduction

- II-C-2. Vues

- II-C-3. Clés étrangères

- II-C-4. Transactions

- II-C-5. Fonctions de fenêtrage

- II-C-6. Héritage

- II-C-7. Conclusion

- III. Langage SQL

- III-A. Syntaxe SQL

- III-A-1. Structure lexicale

- III-A-2. Expressions de valeurs

- III-A-3. Fonctions appelantes

- III-B. Définition des données

- III-B-1. Notions fondamentales sur les tables

- III-B-2. Valeurs par défaut

- III-B-3. Contraintes

- III-B-4. Colonnes système

- III-B-5. Modification des tables

- III-B-6. Droits

- III-B-7. Schémas

- III-B-8. L'héritage

- III-B-9. Partitionnement

- III-B-10. Données distantes

- III-B-11. Autres objets de la base de données

- III-B-12. Gestion des dépendances

- III-C. Manipulation de données

- III-C-1. Insérer des données

- III-C-2. Actualiser les données

- III-C-3. Supprimer des données

- III-D. Requêtes

- III-E. Types de données

- III-E-1. Types numériques

- III-E-2. Types monétaires

- III-E-3. Types caractère

- III-E-4. Types de données binaires

- III-E-5. Types date/heure

- III-E-6. Type booléen

- III-E-7. Types énumération

- III-E-8. Types géométriques

- III-E-9. Types adresses réseau

- III-E-10. Type chaîne de bits

- III-E-11. Types de recherche plein texte

- III-E-12. Type UUID

- III-E-13. Type XML

- III-E-14. Types JSON

- III-E-15. Tableaux

- III-E-16. Types composites

- III-E-17. Types intervalle de valeurs

- III-E-18. Types identifiant d'objet

- III-E-19. pg_lsn Type

- III-E-20. Pseudo-Types

- III-F. Fonctions et opérateurs

- III-F-1. Opérateurs logiques

- III-F-2. Opérateurs de comparaison

- III-F-3. Fonctions et opérateurs mathématiques

- III-F-4. Fonctions et opérateurs de chaînes

- III-F-5. Fonctions et opérateurs de chaînes binaires

- III-F-6. Fonctions et opérateurs sur les chaînes de bits

- III-F-7. Correspondance de motif

- III-F-8. Fonctions de formatage des types de données

- III-F-9. Fonctions et opérateurs sur date/heure

- III-F-10. 9.10. Fonctions de support enum

- III-F-11. 9.11. Fonctions et opérateurs géométriques

- III-F-12. Fonctions et opérateurs sur les adresses réseau

- III-F-13. Fonctions et opérateurs de la recherche plein texte

- III-F-14. Fonctions XML

- III-F-15. Fonctions et opérateurs JSON

- III-F-16. Fonctions de manipulation de séquences

- III-F-17. Expressions conditionnelles

- III-F-18. Fonctions et opérateurs de tableaux

- III-F-19. Fonctions et opérateurs sur les données de type range

- III-F-20. Fonctions d'agrégat

- III-F-21. Fonctions Window

- III-F-22. Expressions de sous-requêtes

- III-F-23. Comparaisons de lignes et de tableaux

- III-F-24. Fonctions retournant des ensembles

- III-F-25. Fonctions d'informations système

- III-F-26. Fonctions d'administration système

- III-F-27. Fonctions trigger

- III-F-28. Fonctions des triggers sur les événements

- III-G. Conversion de types

- III-H. Index

- III-I. Recherche plein texte

- III-J. Contrôle d'accès simultané

- III-K. Conseils sur les performances

- IV. Administration du serveur

- IV-A. Procédure d'installation de PostgreSQL™ du code source

- IV-B. Installation à partir du code source sur Windows™

- IV-C. Configuration du serveur et mise en place

- IV-D. Configuration du serveur

- IV-E. Authentification du client

- IV-F. Rôles de la base de données

- IV-G. Administration des bases de données

- IV-H. Localisation

- IV-I. Planifier les tâches de maintenance

- IV-J. Sauvegardes et restaurations

- IV-K. Haute disponibilité, répartition de charge et réplication

- IV-L. Configuration de la récupération

- IV-M. Surveiller l'activité de la base de données

- IV-N. Surveiller l'utilisation des disques

- IV-O. Fiabilité et journaux de transaction

- IV-P. Tests de régression

- V. Interfaces client

- V-A. libpq - Bibliothèque C

- V-A-1. Fonctions de contrôle de connexion à la base de données

- V-A-2. Fonctions de statut de connexion

- V-A-3. Fonctions de commandes d'exécution

- V-A-4. Traitement des commandes asynchrones

- V-A-5. Récupérer le résultats des requêtes ligne par ligne

- V-A-6. Annuler des requêtes en cours d'exécution

- V-A-7. Interface à chemin rapide

- V-A-8. Notification asynchrone

- V-A-9. Fonctions associées avec la commande COPY

- V-A-10. Fonctions de contrôle

- V-A-11. Fonctions diverses

- V-A-12. Traitement des messages

- V-A-13. Système d'événements

- V-A-14. Variables d'environnement

- V-A-15. Fichier de mots de passe

- V-A-16. Fichier des connexions de service

- V-A-17. Recherches LDAP des paramètres de connexion

- V-A-18. Support de SSL

- V-A-19. Comportement des programmes threadés

- V-A-20. Construire des applications avec libpq

- V-A-21. Exemples de programmes

- V-B. Objets larges

- V-C. ECPG SQL embarqué en C

- V-D. Schéma d'information

- VI. Programmation serveur

- VI-A. Étendre SQL

- VI-B. Déclencheurs (triggers)

- VI-C. Déclencheurs (triggers) sur événement

- VI-D. Système de règles

- VI-E. Langages de procédures

- VI-F. PL/pgSQL - Langage de procédures SQL

- VI-G. PL/Tcl - Langage de procédures Tcl

- VI-H. PL/Perl - Langage de procédures Perl

- VI-I. PL/Python - Langage de procédures Python

- VI-J. Interface de programmation serveur

- VI-K. Processus en tâche de fond (background worker)

- VI-L. Décodage logique (Logical Decoding)

- VII. Référence

- VII-A. Commandes SQL

- VII-B. Applications client de PostgreSQL

- VII-C. Applications relatives au serveur PostgreSQL

- VIII. Internes

- VIII-A. Présentation des mécanismes internes de PostgreSQL

- VIII-B. Catalogues système

- VIII-C. Protocole client/serveur

- VIII-D. Conventions de codage pour PostgreSQL

- VIII-E. Support natif des langues

- VIII-F. Écrire un gestionnaire de langage procédural

- VIII-G. Écrire un wrapper de données distantes

- VIII-H. Optimiseur génétique de requêtes (Genetic Query Optimizer)

- VIII-I. Définition de l'interface des méthodes d'accès aux index

- VIII-J. Index GiST

- VIII-K. Index SP-GiST

- VIII-L. Index GIN

- VIII-M. Stockage physique de la base de données

- VIII-N. Interface du moteur, BKI

- VIII-O. Comment le planificateur utilise les statistiques

- IX. Annexes

- IX-A. Codes d'erreurs de PostgreSQL™

- IX-B. Support de date/heure

- IX-C. Mots-clé SQL

- IX-D. Conformité SQL

- IX-E. Notes de version

- IX-F. Modules supplémentaires fournis

- IX-G. Programmes supplémentaires fournis

- IX-H. Projets externes

- IX-I. Dépôt du code source

- IX-J. Documentation

- IX-K. Acronymes

- IX-L. Traduction française

- IX-M. Bibliographie

- IX-M-1. Livres de référence sur SQL

- IX-M-2. Documentation spécifique sur PostgreSQL

- IX-M-3. Procédures et articles

I - Introduction

I-A - Définition de PostgreSQL™

PostgreSQL™ est un système de gestion de bases de données relationnelles objet (ORDBMS) fondé sur POSTGRES, Version 4.2™. Ce dernier a été développé à l'université de Californie au département des sciences informatiques de Berkeley. POSTGRES est à l'origine de nombreux concepts qui ne seront rendus disponibles au sein de systèmes de gestion de bases de données commerciaux que bien plus tard.

PostgreSQL™ est un descendant libre du code original de Berkeley. Il supporte une grande partie du standard SQL tout en offrant de nombreuses fonctionnalités modernes :

- requêtes complexes ;

- clés étrangères ;

- triggers ;

- vues modifiables ;

- intégrité transactionnelle ;

- contrôle des versions concurrentes (MVCC, acronyme de « MultiVersion Concurrency Control »).

De plus, PostgreSQL™ peut être étendu par l'utilisateur de multiples façons, en ajoutant, par exemple :

- de nouveaux types de données ;

- de nouvelles fonctions ;

- de nouveaux opérateurs ;

- de nouvelles fonctions d'agrégat ;

- de nouvelles méthodes d'indexage ;

- de nouveaux langages de procédure.

Et grâce à sa licence libérale, PostgreSQL™ peut être utilisé, modifié et distribué librement, quel que soit le but visé, qu'il soit privé, commercial ou académique.

I-B - Bref historique de PostgreSQL™

Le système de bases de données relationnel objet PostgreSQL™ est issu de POSTGRES™, programme écrit à l'université de Californie à Berkeley. Après plus d'une vingtaine d'années de développement, PostgreSQL™ annonce être devenu la base de données libre de référence.

I-B-1 - Le projet POSTGRES à Berkeley

Le projet POSTGRES™, mené par le professeur Michael Stonebraker, était sponsorisé par le DARPA (acronyme de Defense Advanced Research Projects Agency), l'ARO (acronyme de Army Research Office), la NSF (acronyme de National Science Foundation) et ESL, Inc. Le développement de POSTGRES™ a débuté en 1986. Les concepts initiaux du système ont été présentés dans Stonebraker and Rowe, 1986 et la définition du modèle de données initial apparut dans Rowe and Stonebraker, 1987. Le système de règles fût décrit dans Stonebraker, Hanson, Hong, 1987, l'architecture du gestionnaire de stockage dans Stonebraker, 1987.

Depuis, plusieurs versions majeures de POSTGRES™ ont vu le jour. La première « démo » devint opérationnelle en 1987 et fut présentée en 1988 lors de la conférence ACM-SIGMOD. La version 1, décrite dans Stonebraker, Rowe, Hirohama, 1990, fut livrée à quelques utilisateurs externes en juin 1989. Suite à la critique du premier mécanisme de règles (Stonebraker et al, 1989), celui-ci fut réécrit (Stonebraker et al, ACM, 1990) pour la version 2, présentée en juin 1990. La version 3 apparut en 1991. Elle apporta le support de plusieurs gestionnaires de stockage, un exécuteur de requêtes amélioré et une réécriture du gestionnaire de règles. La plupart des versions qui suivirent, jusqu'à Postgres95™ (voir plus loin), portèrent sur la portabilité et la fiabilité.

POSTGRES™ fût utilisé dans plusieurs applications, en recherche et en production. On peut citer, par exemple : un système d'analyse de données financières, un programme de suivi des performances d'un moteur à réaction, une base de données de suivi d'astéroïdes, une base de données médicale et plusieurs systèmes d'informations géographiques. POSTGRES™ a aussi été utilisé comme support de formation dans plusieurs universités. Illustra Information Technologies (devenu Informix™, maintenant détenu par IBM) a repris le code et l'a commercialisé. Fin 1992, POSTGRES™ est devenu le gestionnaire de données principal du projet de calcul scientifique Sequoia 2000.

La taille de la communauté d'utilisateurs doubla quasiment au cours de l'année 1993. De manière évidente, la maintenance du prototype et le support prenaient un temps considérable, temps qui aurait dû être employé à la recherche en bases de données. Dans un souci de réduction du travail de support, le projet POSTGRES™ de Berkeley se termina officiellement avec la version 4.2.

I-B-2 - Postgres95

En 1994, Andrew Yu et Jolly Chen ajoutèrent un interpréteur de langage SQL à POSTGRES™. Sous le nouveau nom de Postgres95™, le projet fut publié sur le Web comme descendant libre (OpenSource) du code source initial de POSTGRES™, version Berkeley.

Le code de Postgres95™ était écrit en pur C ANSI et réduit de 25%. De nombreux changements internes améliorèrent les performances et la maintenabilité. Les versions 1.0.x de Postgres95™ passèrent le Wisconsin Benchmark avec des performances meilleures de 30 à 50% par rapport à POSTGRES™, version 4.2. À part les correctifs de bogues, les principales améliorations furent les suivantes :

- le langage PostQUEL est remplacé par SQL (implanté sur le serveur). (La bibliothèque d'interface libpq a été nommée à partir du langage PostQUEL.) Les requêtes imbriquées n'ont pas été supportées avant PostgreSQL™ (voir plus loin) mais elles pouvaient être imitées dans Postgres95™ à l'aide de fonctions SQL utilisateur ; les agrégats furent reprogrammés, la clause GROUP BY ajoutée ;

- un nouveau programme, psql, qui utilise GNU Readline, permet l'exécution interactive de requêtes SQL ; c'est la fin du programme monitor ;

- une nouvelle bibliothèque cliente, libpgtcl, supporte les programmes écrits en Tcl ; un shell exemple, pgtclsh, fournit de nouvelles commandes Tcl pour interfacer des programmes Tcl avec Postgres95™ ;

- l'interface de gestion des « Large Objects » est réécrite ; jusque-là, le seul mécanisme de stockage de ces objets passait par le système de fichiers Inversion («  Inversion file system  ») ; ce système est abandonné ;

- le système de règles d'instance est supprimé ; les règles sont toujours disponibles en tant que règles de réécriture ;

- un bref tutoriel présentant les possibilités du SQL ainsi que celles spécifiques à Postgres95™ est distribué avec les sources ;

- la version GNU de make est utilisée pour la construction à la place de la version BSD ; Postgres95™ peut également être compilé avec un GCC™ sans correctif (l'alignement des doubles est corrigé).

I-B-3 - PostgreSQL

En 1996, le nom « Postgres95 » commence à mal vieillir. Le nom choisi, PostgreSQL™, souligne le lien entre POSTGRES™ et les versions suivantes qui intégrent le SQL. En parallèle, la version est numérotée 6.0 pour reprendre la numérotation du projet POSTGRES™ de Berkeley.

Beaucoup de personnes font référence à PostgreSQL™ par « Postgres » (il est rare que le nom soit écrit en capitales) par tradition ou parce que c'est plus simple à prononcer. Cet usage est accepté comme alias ou pseudo.

Lors du développement de Postgres95™, l'effort était axé sur l'identification et la compréhension des problèmes dans le code. Avec PostgreSQL™, l'accent est mis sur les nouvelles fonctionnalités, sans pour autant abandonner les autres domaines.

L'historique de PostgreSQL™ à partir de ce moment est disponible dans l'Annexe E, Notes de version.

I-C - Conventions

Les conventions suivantes sont utilisées dans le synopsis d'une commande : les crochets ([ et ]) indiquent des parties optionnelles. (Dans le synopsis d'une commande Tcl, des points d'interrogation (?) sont utilisés, comme c'est habituellement le cas en Tcl.) Les accolades ({ et }) et les barres verticales (|) indiquent un choix entre plusieurs options. Les points de suspension (...) signifient que l'élément précédent peut être répété.

Lorsque cela améliore la clarté, les commandes SQL sont précédées d'une invite =>, tandis que les commandes shell le sont par $. Dans le cadre général, les invites ne sont pas indiquées.

Un administrateur est généralement une personne en charge de l'installation et de la bonne marche du serveur. Un utilisateur est une personne qui utilise ou veut utiliser une partie quelconque du système PostgreSQL™. Ces termes ne doivent pas être pris trop à la lettre ; cet ouvrage n'a pas d'avis figé sur les procédures d'administration système.

I-D - Pour plus d'informations

En dehors de la documentation, il existe d'autres ressources concernant PostgreSQL™ :

Wiki

Le wiki de PostgreSQL™ contient la FAQ (liste des questions fréquemment posées), la liste TODO et des informations détaillées sur de nombreux autres thèmes.

Site web

Le site web de PostgreSQL™ contient des détails sur la dernière version, et bien d'autres informations pour rendre un travail ou un investissement personnel avec PostgreSQL™ plus productif.

Listes de discussion

Les listes de discussion constituent un bon endroit pour trouver des réponses à ses questions, pour partager ses expériences avec celles d'autres utilisateurs et pour contacter les développeurs. La consultation du site web de PostgreSQL™ fournit tous les détails.

Soi-même !

PostgreSQL™ est un projet OpenSource. En tant que tel, le support dépend de la communauté des utilisateurs. Lorsque l'on débute avec PostgreSQL™, on est tributaire de l'aide des autres, soit au travers de la documentation soit par les listes de discussion. Il est important de faire partager à son tour ses connaissances par la lecture des listes de discussion et les réponses aux questions. Lorsque quelque chose est découvert qui ne figurait pas dans la documentation, pourquoi ne pas en faire profiter les autres ? De même lors d'ajout de fonctionnalités au code.

I-E - Lignes de conduite pour les rapports de bogues

Lorsque vous trouvez un bogue dans PostgreSQL™, nous voulons en entendre parler. Vos rapports de bogues jouent un rôle important pour rendre PostgreSQL™ plus fiable car même avec la plus grande attention, nous ne pouvons pas garantir que chaque partie de PostgreSQL™ fonctionnera sur toutes les plates-formes et dans toutes les circonstances.

Les suggestions suivantes ont pour but de vous former à la saisie d'un rapport de bogue qui pourra ensuite être gérée de façon efficace. Il n'est pas requis de les suivre mais ce serait à l'avantage de tous.

Nous ne pouvons pas promettre de corriger tous les bogues immédiatement. Si le bogue est évident, critique ou affecte un grand nombre d'utilisateurs, il y a de grandes chances pour que quelqu'un s'en charge. Il se peut que nous vous demandions d'utiliser une version plus récente pour vérifier si le bogue est toujours présent. Ou nous pourrions décider que le bogue ne peut être corrigé avant qu'une réécriture massive, que nous avions planifiée, ne soit faite. Ou peut-être est-ce trop difficile et que des choses plus importantes nous attendent. Si vous avez besoin d'aide immédiatement, envisagez l'obtention d'un contrat de support commercial.

I-E-1 - Identifier les bogues

Avant de rapporter un bogue, merci de lire et re-lire la documentation pour vérifier que vous pouvez réellement faire ce que vous essayez de faire. Si ce n'est pas clair, rapportez-le aussi ; c'est un bogue dans la documentation. S'il s'avère que le programme fait différemment de ce qu'indique la documentation, c'est un bogue. Ceci peut inclure les circonstances suivantes, sans s'y limiter :

- Un programme se terminant avec un signal fatal ou un message d'erreur du système d'exploitation qui indiquerait un problème avec le programme. (Un contre-exemple pourrait être le message « disk full », disque plein, car vous devez le régler vous-même.)

- Un programme produit une mauvaise sortie pour une entrée donnée.

- Un programme refuse d'accepter une entrée valide (c'est-à-dire telle que définie dans la documentation).

- Un programme accepte une entrée invalide sans information ou message d'erreur. Mais gardez en tête que votre idée d'entrée invalide pourrait être notre idée d'une extension ou d'une compatibilité avec les pratiques traditionnelles.

- PostgreSQL™ échoue à la compilation, à la construction ou à l'installation suivant les instructions des plateformes supportées.

Ici, « programme » fait référence à un exécutable, pas au moteur du serveur.

Une lenteur ou une absorption des ressources n'est pas nécessairement un bogue. Lisez la documentation ou demandez sur une des listes de discussion pour de l'aide concernant l'optimisation de vos applications. Ne pas se conformer au standard SQL n'est pas nécessairement un bogue sauf si une telle conformité est indiquée explicitement.

Avant de continuer, vérifiez sur la liste des choses à faire ainsi que dans la FAQ pour voir si votre bogue n'est pas déjà connu. Si vous n'arrivez pas à décoder les informations sur la liste des choses à faire, écrivez un rapport. Le minimum que nous puissions faire est de rendre cette liste plus claire.

I-E-2 - Que rapporter ?

Le point le plus important à se rappeler avec les rapports de bogues est de donner tous les faits et seulement les faits. Ne spéculez pas sur ce que vous pensez qui ne va pas, sur ce qu'« il semble faire » ou sur quelle partie le programme a une erreur. Si vous n'êtes pas familier avec l'implémentation, vous vous tromperez probablement et vous ne nous aiderez pas. Et même si vous avez raison, des explications complètes sont un bon supplément mais elles ne doivent pas se substituer aux faits. Si nous pensons corriger le bogue, nous devons toujours le reproduire nous-même. Rapporter les faits stricts est relativement simple (vous pouvez probablement copier/coller à partir de l'écran) mais, trop souvent, des détails importants sont oubliés parce que quelqu'un a pensé qu'ils n'avaient pas d'importance ou que le rapport serait compris.

Les éléments suivants devraient être fournis avec chaque rapport de bogue :

- La séquence exacte des étapes nécessaires pour reproduire le problème à partir du lancement du programme. Ceci devrait se suffire ; il n'est pas suffisant d'envoyer une simple instruction SELECT sans les commandes CREATE TABLE et INSERT qui ont précédé, si la sortie devrait dépendre des données contenues dans les tables. Nous n'avons pas le temps de comprendre le schéma de votre base de données. Si nous sommes supposés créer nos propres données, nous allons probablement ne pas voir le problème.
Le meilleur format pour un test suite à un problème relatif à SQL est un fichier qui peut être lancé via l'interface psql et qui montrera le problème. (Assurez-vous de ne rien avoir dans votre fichier de lancement ~/.psqlrc.) Un moyen facile pour créer ce fichier est d'utiliser pg_dump pour récupérer les déclarations des tables ainsi que les données nécessaires pour mettre en place la scène. Il ne reste plus qu'à ajouter la requête posant problème. Vous êtes encouragé à minimiser la taille de votre exemple mais ce n'est pas une obligation. Si le bogue est reproductible, nous le trouverons de toute façon.
Si votre application utilise une autre interface client, telle que PHP, alors essayez d'isoler le problème aux requêtes erronées. Nous n'allons certainement pas mettre en place un serveur web pour reproduire votre problème. Dans tous les cas, rappelez-vous d'apporter les fichiers d'entrée exacts ; n'essayez pas de deviner que le problème se pose pour les « gros fichiers » ou pour les « bases de données de moyenne taille », etc. car cette information est trop inexacte, subjective pour être utile.

- La sortie que vous obtenez. Merci de ne pas dire que cela « ne fonctionne pas » ou s'est « arrêté brutalement ». S'il existe un message d'erreur, montrez-le même si vous ne le comprenez pas. Si le programme se termine avec une erreur du système d'exploitation, dites-le. Même si le résultat de votre test est un arrêt brutal du programme ou un autre souci évident, il pourrait ne pas survenir sur notre plateforme. Le plus simple est de copier directement la sortie du terminal, si possible.

Si vous rapportez un message d'erreur, merci d'obtenir la forme la plus verbeuse de ce message. Avec psql, exécutez \set VERBOSITY verbose avant tout. Si vous récupérez le message des traces du serveur, initialisez la variable d'exécution log_error_verbosity avec verbose pour que tous les détails soient tracés.

Dans le cas d'erreurs fatales, le message d'erreur rapporté par le client pourrait ne pas contenir toutes les informations disponibles. Jetez aussi un œil aux traces du serveur de la base de données. Si vous ne conservez pas les traces de votre serveur, c'est le bon moment pour commencer à le faire.

- Il est très important de préciser ce que vous attendez en sortie. Si vous écrivez uniquement « Cette commande m'a donné cette réponse. » ou « Ce n'est pas ce que j'attendais. », nous pourrions le lancer nous-même, analyser la sortie et penser que tout est correct car cela correspond exactement à ce que nous attendions. Nous ne devrions pas avoir à passer du temps pour décoder la sémantique exacte de vos commandes. Tout spécialement, ne vous contentez pas de dire que « Ce n'est pas ce que SQL spécifie/Oracle fait. » Rechercher le comportement correct à partir de SQL n'est pas amusant et nous ne connaissons pas le comportement de tous les autres serveurs de base de données relationnels. (Si votre problème est un arrêt brutal du serveur, vous pouvez évidemment omettre cet élément.)

- Toutes les options en ligne de commande ainsi que les autres options de lancement incluant les variables d'environnement ou les fichiers de configuration que vous avez modifié. Encore une fois, soyez exact. Si vous utilisez une distribution pré-packagée qui lance le serveur au démarrage, vous devriez essayer de retrouver ce que cette distribution fait.

- Tout ce que vous avez fait de différent à partir des instructions d'installation.

- La version de PostgreSQL™. Vous pouvez lancer la commande SELECT version(); pour trouver la version du serveur sur lequel vous êtes connecté. La plupart des exécutables disposent aussi d'une option --version ; postgres --version et psql --version devraient au moins fonctionner. Si la fonction ou les options n'existent pas, alors votre version est bien trop ancienne et vous devez mettre à jour. Si vous avez lancé une version préparée sous forme de paquets, tel que les RPM, dites-le en incluant la sous-version que le paquet pourrait avoir. Si vous êtes sur une version Git, mentionnez-le en indiquant le hachage du commit.
Si votre version est antérieure à la 9.4.6, nous allons certainement vous demander de mettre à jour. Beaucoup de corrections de bogues et d'améliorations sont apportées dans chaque nouvelle version, donc il est bien possible qu'un bogue rencontré dans une ancienne version de PostgreSQL™ soit déjà corrigé. Nous ne fournissons qu'un support limité pour les sites utilisant d'anciennes versions de PostgreSQL™ ; si vous avez besoin de plus de support que ce que nous fournissons, considérez l'acquisition d'un contrat de support commercial.

- Informations sur la plate-forme. Ceci inclut le nom du noyau et sa version, bibliothèque C, processeur, mémoires et ainsi de suite. Dans la plupart des cas, il est suffisant de préciser le vendeur et la version mais ne supposez pas que tout le monde sait ce que « Debian » contient ou que tout le monde utilise des i386. Si vous avez des problèmes à l'installation, des informations sur l'ensemble des outils de votre machine (compilateurs, make, etc.) sont aussi nécessaires.

N'ayez pas peur si votre rapport de bogue devient assez long. C'est un fait. Il est préférable de rapporter tous les faits la première fois plutôt que nous ayons à vous tirer les vers du nez. D'un autre côté, si vos fichiers d'entrée sont trop gros, il est préférable de demander si quelqu'un souhaite s'y plonger. Voici un article qui relève quelques autres conseils sur les rapports de bogues.

Ne passez pas tout votre temps à vous demander quelles modifications apporter pour que le problème s'en aille. Ceci ne nous aidera probablement pas à le résoudre. S'il arrive que le bogue ne peut pas être corrigé immédiatement, vous aurez toujours l'opportunité de chercher ceci et de partager vos trouvailles. De même, encore une fois, ne perdez pas votre temps à deviner pourquoi le bogue existe. Nous le trouverons assez rapidement.

Lors de la rédaction d'un rapport de bogue, merci de choisir une terminologie qui ne laisse pas place aux confusions. Le paquet logiciel en totalité est appelé « PostgreSQL », quelquefois « Postgres » en court. Si vous parlez spécifiquement du serveur, mentionnez-le mais ne dites pas seulement « PostgreSQL a planté ». Un arrêt brutal d'un seul processus serveur est assez différent de l'arrêt brutal du « postgres » père ; merci de ne pas dire que « le serveur a planté » lorsque vous voulez dire qu'un seul processus s'est arrêté, ni vice versa. De plus, les programmes clients tels que l'interface interactive « psql » sont complètement séparés du moteur. Essayez d'être précis sur la provenance du problème : client ou serveur.

I-E-3 - Où rapporter des bogues ?

En général, envoyez vos rapports de bogue à la liste de discussion des rapports de bogue (<pgsql-bogues@postgresql.org>>). Nous vous demandons d'utiliser un sujet descriptif pour votre courrier électronique, par exemple une partie du message d'erreur.

Une autre méthode consiste à remplir le formulaire web disponible sur le site web du projet. Saisir un rapport de bogue de cette façon fait que celui-ci est envoyé à la liste de discussion <pgsql-bogues@postgresql.org>>.

Si votre rapport de bogue a des implications sur la sécurité et que vous préfèreriez qu'il ne soit pas immédiatement visible dans les archives publiques, ne l'envoyez pas sur pgsql-bugs. Les problèmes de sécurité peuvent être rapportés de façon privé sur <security@postgresql.org>>.

N'envoyez pas de rapports de bogue aux listes de discussion des utilisateurs, comme <pgsql-sql@postgresql.org><> ou <pgsql-general@postgresql.org>>. Ces listes de discussion servent à répondre aux questions des utilisateurs et les abonnés ne souhaitent pas recevoir de rapports de bogues. Plus important, ils ont peu de chance de les corriger.

De même, n'envoyez pas vos rapports de bogue à la liste de discussion des développeurs <pgsql-hackers@postgresql.org>pgsql-hackers>. Cette liste sert aux discussions concernant le développement de PostgreSQL™ et il serait bon de conserver les rapports de bogue séparément. Nous pourrions choisir de discuter de votre rapport de bogue sur pgsql-hackers si le problème nécessite que plus de personnes s'en occupent.

Si vous avez un problème avec la documentation, le meilleur endroit pour le rapporter est la liste de discussion pour la documentation <pgsql-docs@postgresql.org>>. Soyez précis sur la partie de la documentation qui vous déplaît.

Si votre bogue concerne un problème de portabilité sur une plate-forme non supportée, envoyez un courrier électronique à <pgsql-hackers@postgresql.org>>, pour que nous puissions travailler sur le portage de PostgreSQL™ sur votre plate-forme.

Dû, malheureusement, au grand nombre de pourriels (spam), toutes les adresses de courrier électronique ci-dessus appartiennent à des listes de discussion fermées. Autrement dit, vous devez être abonné pour être autorisé à y envoyer un courrier. Néanmoins, vous n'avez pas besoin de vous abonner pour utiliser le formulaire web de rapports de bogue. Si vous souhaitez envoyer des courriers mais ne pas recevoir le trafic de la liste, vous pouvez vous abonner et configurer l'option nomail. Pour plus d'informations, envoyez un courrier à <majordomo@postgresql.org>help> avec le seul mot help dans le corps du message.

II - Tutoriel

Bienvenue dans le tutoriel de PostgreSQL™. Les chapitres suivants présentent une courte introduction à PostgreSQL™, aux concepts des bases de données relationnelles et au langage SQL à ceux qui débutent dans l'un de ces domaines. Seules sont nécessaires des connaissances générales sur l'utilisation des ordinateurs. Aucune expérience particulière d'Unix ou de programmation n'est requise. Ce tutoriel a surtout pour but de faire acquérir une expérience pratique des aspects importants du système PostgreSQL™. Il n'est ni exhaustif ni complet, mais introductif.

À la suite de ce tutoriel, la lecture de la Partie II, « Langage SQL » permettra d'acquérir une connaissance plus complète du langage SQL, celle de la Partie IV, « Interfaces client » des informations sur le développement d'applications. La configuration et la gestion sont détaillées dans la Partie III, « Administration du serveur ».

II-A - Démarrage

II-A-1 - Installation

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.

II-A-2 - Concepts architecturaux

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

II-A-3 - Création d'une base de données

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 :

createdb: could not connect to database postgres: could not connect to server: No such file or directory

Is the server running locally and accepting

connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

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. [1]

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.

[1] Quelques explications : les noms d'utilisateurs de PostgreSQL™ sont différents des comptes utilisateurs du système d'exploitation. Quand vous vous connectez à une base de données, vous pouvez choisir le nom d'utilisateur PostgreSQL™ que vous utilisez. Si vous ne spécifiez rien, cela sera par défaut le même nom que votre compte système courant. En fait, il existe toujours un compte utilisateur PostgreSQL™ qui a le même nom que l'utilisateur du système d'exploitation qui a démarré le serveur, et cet utilisateur a toujours le droit de créer des bases. Au lieu de vous connecter au système en tant que cet utilisateur, vous pouvez spécifier partout l'option -U pour sélectionner un nom d'utilisateur PostgreSQL™ sous lequel vous connecter.

II-A-4 - Accéder à une base

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 :

1. $ 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 :

1. psql (9.4.6)

2. Type "help" for help.

3. 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 :

1. ma_base=> SELECT version();

2. version

3. -----------------------------------------------------------------------

4. PostgreSQL 9.4.6 on i586-pc-linux-gnu, compiled by GCC 2.96, 32-bit

5. (1 row)

7. ma_base=> SELECT current_date;

8. date

9. ------------

10. 2002-08-31

11. (1 row)

13. ma_base=> SELECT 2 + 2;

14. ?column?

15. ----------

16. 4

17. (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 :

1. ma_base=> \h

Pour sortir de psql, saisissez :

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

II-B - Le langage SQL

II-B-1 - Introduction

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.

II-B-2 - Concepts

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

II-B-3 - Créer une nouvelle table

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 :

1. CREATE TABLE temps (

2. ville           varchar(80),

3. t_basse         int,           -- température basse

4. t_haute         int,           -- température haute

5. prcp            real,          -- précipitation

6. date            date

7. );

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é :

1. CREATE TABLE villes (

2. nom            varchar(80),

3. emplacement    point

4. );

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 :

1. DROP TABLE nom_table;

II-B-4 - Remplir une table avec des lignes

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

1. 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 :

1. 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 :

1. INSERT INTO temps (ville, t_basse, t_haute, prcp, date)

2. 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 :

1. INSERT INTO temps (date, ville, t_haute, t_basse)

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

II-B-5 - Interroger une table

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 :

1. SELECT * FROM temps;

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

1. 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 :

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

Cela devrait donner :

1. ville     | temp_moy |    date

2. ---------------+----------+------------

3. San Francisco |       48 | 1994-11-27

4. San Francisco |       50 | 1994-11-29

5. Hayward       |       45 | 1994-11-29

6. (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 :

1. SELECT * FROM temps

2. WHERE ville = 'San Francisco' AND prcp > 0.0

Résultat :

1. ville     | t_basse | t_haute | prcp |    date

2. ---------------+---------+---------+------+------------

3. San Francisco |      46 |      50 | 0.25 | 1994-11-27

4. (1 row)

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

1. SELECT * FROM temps

2. ORDER BY ville;

1. ville        | t_basse | t_haute | prcp |    date

2. --------------+---------+---------+------+------------

3. Hayward       |      37 |      54 |      | 1994-11-29

4. San Francisco |      43 |      57 |    0 | 1994-11-29

5. 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 :

1. SELECT * FROM temps

2. ORDER BY ville, t_basse;

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

1. SELECT DISTINCT ville

2. FROM temps;

1. ville

2. ---------------

3. Hayward

4. San Francisco

5. (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 : [3]

1. SELECT DISTINCT ville

2. FROM temps

3. ORDER BY ville;

[2] Alors que SELECT * est utile pour des requêtes rapides, c'est généralement considéré comme un mauvais style dans un code en production car l'ajout d'une colonne dans la table changerait les résultats.

[3] Dans certains systèmes de bases de données, ceci incluant les anciennes versions de PostgreSQL™, l'implémentation de DISTINCT ordonne automatiquement les lignes. Du coup, ORDER BY n'est pas nécessaire. Mais, ceci n'est pas requis par le standard SQL et PostgreSQL™ ne vous garantit pas actuellement que DISTINCT ordonne les lignes.

II-B-6 - Jointures entre les tables

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 est uniquement un modèle conceptuel. La jointure est habituellement exécutée d'une manière plus efficace que la comparaison de chaque paire de lignes mais c'est invisible pour l'utilisateur.

Ceci sera accompli avec la requête suivante :

1. SELECT *

2. FROM temps, villes

3. WHERE ville = nom;

1. ville     | t_basse | t_haute | prcp |    date    |     nom       | emplacement

2. ---------------+---------+---------+------+------------+---------------+-------------

3. San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)

4. San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)

5. (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 * :

1. SELECT ville, t_basse, t_haute, prcp, date, emplacement

2. FROM temps, villes

3. 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 :

1. SELECT temps.ville, temps.t_basse, temps.t_haute,

2. temps.prcp, temps.date, villes.emplacement

3. FROM temps, villes

4. 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 :

1. SELECT *

2. 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 :

1. SELECT *

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

1. ville     | t_basse | t_haute | prcp |    date    |     nom       | emplacement

2. ---------------+---------+---------+------+------------+---------------+-------------

3. Hayward       |      37 |      54 |      | 1994-11-29 |               |

4. San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)

5. San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)

6. (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 :

1. SELECT T1.ville, T1.t_basse AS bas, T1.t_haute AS haut,

2. T2.ville, T2.t_basse AS bas, T2.t_haute AS haus

3. FROM temps T1, temps T2

4. WHERE T1.t_basse < T2.t_basse

5. AND T1.t_haute > T2.t_haute;

1. ville      | bas | haut |     ville     | bas | haut

2. ----------------+-----+------+---------------+-----+------

3. San Francisco |  43 |   57 | San Francisco |  46 |   50

4. Hayward       |  37 |   54 | San Francisco |  46 |   50

5. (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 :

1. SELECT *

2. FROM temps t, villes v

3. WHERE t.ville = v.nom;

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

II-B-7 - Fonctions d'agrégat

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 :

1. SELECT max(t_basse) FROM temps;

1. max

2. -----

3. 46

4. (1 row)

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

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

2. 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 :

1. SELECT ville FROM temps

2. WHERE t_basse = (SELECT max(t_basse) FROM temps);

1. ville

2. ---------------

3. San Francisco

4. (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 :

1. SELECT ville, max(t_basse)

2. FROM temps

3. GROUP BY ville;

1. ville     | max

2. ---------------+-----

3. Hayward       |  37

4. San Francisco |  46

5. (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 :

1. SELECT ville, max(t_basse)

2. FROM temps

3. GROUP BY ville

4. HAVING max(t_basse) < 40;

1. ville  | max

2. ---------+-----

3. Hayward |  37

4. (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 :

1. SELECT ville, max(t_basse)

2. FROM temps

3. WHERE ville LIKE 'S%'

4. GROUP BY ville

5. HAVING max(t_basse) < 40;

L'opérateur LIKE fait la correspondance avec un motif ; cela est expliqué dans la Section 9.7, « Correspondance de motif ».

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.

II-B-8 - Mises à jour

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 :

1. UPDATE temps

2. SET t_haute = t_haute - 2,  t_basse = t_basse - 2

3. WHERE date > '1994-11-28';

Regardez le nouvel état des données :

1. SELECT * FROM temps;

1. ville      | t_basse | t_haute | prcp |    date

2. ----------------+---------+---------+------+------------

3. San Francisco  |      46 |      50 | 0.25 | 1994-11-27

4. San Francisco  |      41 |      55 |    0 | 1994-11-29

5. Hayward        |      35 |      52 |      | 1994-11-29

6. (3 rows)

II-B-9 - Suppressions

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 :

1. DELETE FROM temps WHERE ville = 'Hayward';

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

1. SELECT * FROM temps;

1. ville     | t_basse | t_haute | prcp |    date

2. ---------------+---------+---------+------+------------

3. San Francisco |      46 |      50 | 0.25 | 1994-11-27

4. San Francisco |      41 |      55 |    0 | 1994-11-29

5. (2 rows)

Faire très attention aux instructions de la forme :

1. 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 !

II-C - Fonctionnalités avancées

II-C-1 - Introduction

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

II-C-2 - Vues

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 :

1. CREATE VIEW ma_vue AS

2. SELECT ville, t_basse, t_haute, prcp, date, emplacement

3. FROM temps, villes

4. WHERE ville = nom;

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

II-C-3 - Clés étrangères

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 :

1. CREATE TABLE villes (

2. ville       varchar(80) primary key,

3. emplacement point

4. );

6. CREATE TABLE temps (

7. ville      varchar(80) references villes,

8. t_haute int,

9. t_basse int,

10. prcp       real,

11. date       date

12. );

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

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

ERROR: insert or update on table "temps" violates foreign key constraint "temps_ville_fkey"

DETAIL : Key (ville)=(a) is not present in table "villes".

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.

II-C-4 - Transactions

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

1. UPDATE comptes SET balance = balance - 100.00

2. WHERE nom = 'Alice';

3. UPDATE branches SET balance = balance - 100.00

4. WHERE nom = (SELECT nom_branche FROM comptes WHERE nom = 'Alice');

5. UPDATE comptes SET balance = balance + 100.00

6. WHERE nom = 'Bob';

7. UPDATE branches SET balance = balance + 100.00

8. 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 :

1. BEGIN;

2. UPDATE comptes SET balance = balance - 100.00

3. WHERE nom = 'Alice';

4. -- etc etc

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

Quelques bibliothèques clientes lancent les commandes BEGIN et COMMIT automatiquement. L'utilisateur bénéficie alors des effets des blocs transactionnels sans les demander. Vérifiez la documentation de l'interface que vous utilisez.

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 :

1. BEGIN;

2. UPDATE comptes SET balance = balance - 100.00

3. WHERE nom = 'Alice';

4. SAVEPOINT mon_pointdesauvegarde;

5. UPDATE comptes SET balance = balance + 100.00

6. WHERE nom = 'Bob';

7. -- oups ... oublions ça et créditons le compte de Wally

8. ROLLBACK TO mon_pointdesauvegarde;

9. UPDATE comptes SET balance = balance + 100.00

10. WHERE nom = 'Wally';

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

II-C-5 - Fonctions de fenêtrage

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 :

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

1. nomdep   | noemp | salaire |          avg

2. -----------+-------+---------+-----------------------

3. develop   |    11 |   5200  | 5020.0000000000000000

4. develop   |     7 |   4200  | 5020.0000000000000000

5. develop   |     9 |   4500  | 5020.0000000000000000

6. develop   |     8 |   6000  | 5020.0000000000000000

7. develop   |    10 |   5200  | 5020.0000000000000000

8. personnel |     5 |   3500  | 3700.0000000000000000

9. personnel |     2 |   3900  | 3700.0000000000000000

10. ventes    |     3 |   4800  | 4866.6666666666666667

11. ventes    |     1 |   5000  | 4866.6666666666666667

12. ventes    |     4 |   4800  | 4866.6666666666666667

13. (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 :

1. SELECT nomdep, noemp, salaire,

2. rank() OVER (PARTITION BY nomdep ORDER BY salaire DESC)

3. FROM salaireemp;

1. nomdep   | noemp | salaire| rank

2. -----------+-------+--------+------

3. develop   |     8 |   6000 |    1

4. develop   |    10 |   5200 |    2

5. develop   |    11 |   5200 |    2

6. develop   |     9 |   4500 |    4

7. develop   |     7 |   4200 |    5

8. personnel |     2 |   3900 |    1

9. personnel |     5 |   3500 |    2

10. ventes    |     1 |   5000 |    1

11. ventes    |     4 |   4800 |    2

12. ventes    |     3 |   4800 |    2

13. (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. [4] Voici un exemple utilisant sum :

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

1. salaire|  sum

2. --------+-------

3. 5200 | 47100

4. 5000 | 47100

5. 3500 | 47100

6. 4800 | 47100

7. 3900 | 47100

8. 4200 | 47100

9. 4500 | 47100

10. 4800 | 47100

11. 6000 | 47100

12. 5200 | 47100

13. (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 :

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

1. salaire|  sum

2. --------+-------

3. 3500 |  3500

4. 3900 |  7400

5. 4200 | 11600

6. 4500 | 16100

7. 4800 | 25700

8. 4800 | 25700

9. 5000 | 30700

10. 5200 | 41100

11. 5200 | 41100

12. 6000 | 47100

13. (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 :

1. SELECT nomdep, noemp, salaire, date_embauche

2. FROM

3. (SELECT nomdep, noemp, salaire, date_embauche,

4. rank() OVER (PARTITION BY nomdep ORDER BY salaire DESC, noemp) AS pos

5. FROM salaireemp

6. ) AS ss

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

1. SELECT sum(salaire) OVER w, avg(salaire) OVER w

2. FROM salaireemp

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

[4] Il existe des options pour définir la window frame autrement, mais ce tutoriel ne les présente pas. Voir la Section 4.2.8, « Appels de fonction de fenêtrage » pour les détails.

II-C-6 - Héritage

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

1. CREATE TABLE capitales (

2. nom        text,

3. population real,

4. altitude   int,    -- (en pied)

5. etat       char(2)

6. );

8. CREATE TABLE non_capitales (

9. nom        text,

10. population real,

11. altitude   int     -- (en pied)

12. );

14. CREATE VIEW villes AS

15. SELECT nom, population, altitude FROM capitales

16. UNION

17. 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 :

1. CREATE TABLE villes (

2. nom        text,

3. population real,

4. altitude   int     -- (en pied)

5. );

7. CREATE TABLE capitales (

8. etat      char(2)

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

1. SELECT nom, altitude

2. FROM villes

3. WHERE altitude > 500;

ce qui renvoie :

1. nom     | altitude

2. -----------+----------

3. Las Vegas |     2174

4. Mariposa  |     1953

5. Madison   |      845

6. (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 :

1. SELECT nom, altitude

2. FROM ONLY villes

3. WHERE altitude > 500;

1. nom     | altitude

2. -----------+----------

3. Las Vegas |     2174

4. Mariposa  |     1953

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

Bien que l'héritage soit fréquemment utile, il n'a pas été intégré avec les contraintes d'unicité et les clés étrangères, ce qui limite son utilité. Voir la Section 5.8, « L'héritage » pour plus de détails.

II-C-7 - Conclusion

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.

III - Langage SQL

Cette partie présente l'utilisation du langage SQL au sein de PostgreSQL™. La syntaxe générale de SQL y est expliquée, ainsi que la création des structures de stockage des données, le peuplement de la base et son interrogation. La partie centrale liste les types de données et les fonctions disponibles ainsi que leur utilisation dans les requêtes SQL. Le reste traite de l'optimisation de la base de données en vue d'obtenir des performances idéales.

L'information dans cette partie est présentée pour qu'un utilisateur novice puisse la suivre du début à la fin et obtenir ainsi une compréhension complète des sujets sans avoir à effectuer de fréquents sauts entre les chapitres. Les chapitres sont indépendants. Un utilisateur plus expérimenté pourra, donc, ne consulter que les chapitres l'intéressant. L'information est présentée dans un style narratif par unité thématique. Les lecteurs qui cherchent une description complète d'une commande particulière peuvent se référer à la Partie VI, « Référence ».

Pour profiter pleinement de cette partie, il est nécessaire de savoir se connecter à une base PostgreSQL™ et d'y exécuter des commandes SQL. Les lecteurs qui ne sont pas familiers avec ces prérequis sont encouragés à lire préalablement la Partie I, « Tutoriel ».

Les commandes SQL sont généralement saisies à partir du terminal interactif de PostgreSQL™, psql. D'autres programmes possédant des fonctionnalités similaires peuvent également être utilisés.

III-A - Syntaxe SQL

III-A-1 - Structure lexicale

s

III-A-2 - Expressions de valeurs

s

III-A-3 - Fonctions appelantes

s

III-B - Définition des données

D

III-B-1 - Notions fondamentales sur les tables

d

III-B-2 - Valeurs par défaut

d

III-B-3 - Contraintes

d

III-B-4 - Colonnes système

d

III-B-5 - Modification des tables

d

III-B-6 - Droits

d

III-B-7 - Schémas

d

III-B-8 - L'héritage

d

III-B-9 - Partitionnement

d

III-B-10 - Données distantes

d

III-B-11 - Autres objets de la base de données

d

III-B-12 - Gestion des dépendances

d

III-C - Manipulation de données

1. D

III-C-1 - Insérer des données

sd

III-C-2 - Actualiser les données

d

III-C-3 - Supprimer des données

d

III-D - Requêtes

D

7.1. Aperçu

7.2. Expressions de table

7.3. Listes de sélection

7.4. Combiner des requêtes

7.5. Tri des lignes

7.6. LIMIT et OFFSET

7.7. Listes VALUES

7.8. Requêtes WITH (Common Table Expressions)

III-E - Types de données

d

III-E-1 - Types numériques

x

III-E-2 - Types monétaires

d

III-E-3 - Types caractère

d

III-E-4 - Types de données binaires

d

III-E-5 - Types date/heure

d

III-E-6 - Type booléen

d

III-E-7 - Types énumération

d

III-E-8 - Types géométriques

d

III-E-9 - Types adresses réseau

d

III-E-10 - Type chaîne de bits

d

III-E-11 - Types de recherche plein texte

d

III-E-12 - Type UUID

d

III-E-13 - Type XML

g

III-E-14 - Types JSON

d

III-E-15 - Tableaux

h

III-E-16 - Types composites

g

III-E-17 - Types intervalle de valeurs

f

III-E-18 - Types identifiant d'objet

g

III-E-19 - pg_lsn Type

g

III-E-20 - Pseudo-Types

h

III-F - Fonctions et opérateurs

D

III-F-1 - Opérateurs logiques

s

III-F-2 - Opérateurs de comparaison

d

III-F-3 - Fonctions et opérateurs mathématiques

d

III-F-4 - Fonctions et opérateurs de chaînes

d

III-F-5 - Fonctions et opérateurs de chaînes binaires

d

III-F-6 - Fonctions et opérateurs sur les chaînes de bits

d

III-F-7 - Correspondance de motif

d

III-F-8 - Fonctions de formatage des types de données

d

III-F-9 - Fonctions et opérateurs sur date/heure

d

III-F-10 - 9.10. Fonctions de support enum

s

III-F-11 - 9.11. Fonctions et opérateurs géométriques

s

III-F-12 - Fonctions et opérateurs sur les adresses réseau

s

III-F-13 - Fonctions et opérateurs de la recherche plein texte

s

III-F-14 - Fonctions XML

s

III-F-15 - Fonctions et opérateurs JSON

s

III-F-16 - Fonctions de manipulation de séquences

s

III-F-17 - Expressions conditionnelles

d

III-F-18 - Fonctions et opérateurs de tableaux

d

III-F-19 - Fonctions et opérateurs sur les données de type range

z

III-F-20 - Fonctions d'agrégat

s

III-F-21 - Fonctions Window

f

III-F-22 - Expressions de sous-requêtes

e

III-F-23 - Comparaisons de lignes et de tableaux

e

III-F-24 - Fonctions retournant des ensembles

e

III-F-25 - Fonctions d'informations système

e

III-F-26 - Fonctions d'administration système

e

III-F-27 - Fonctions trigger

j

III-F-28 - Fonctions des triggers sur les événements

d

III-G - Conversion de types

D

10.1. Aperçu

10.2. Opérateurs

10.3. Fonctions

10.4. Stockage de valeurs

10.5. Constructions UNION, CASE et constructions relatives

III-H - Index

D

11.1. Introduction

11.2. Types d'index

11.3. Index multicolonnes

11.4. Index et ORDER BY

11.5. Combiner des index multiples

11.6. Index d'unicité

11.7. Index d'expressions

11.8. Index partiels

11.9. Classes et familles d'opérateurs

11.10. Index et collationnements

11.11. Examiner l'utilisation des index

III-I - Recherche plein texte

D

12.1. Introduction

12.2. Tables et index

12.3. Contrôler la recherche plein texte

12.4. Fonctionnalités supplémentaires

12.5. Analyseurs

12.6. Dictionnaires

12.7. Exemple de configuration

12.8. Tester et déboguer la recherche plein texte

12.9. Types d'index GiST et GIN

12.10. Support de psql

12.11. Limites

12.12. Migration à partir d'une recherche plein texte antérieure à 8.3

III-J - Contrôle d'accès simultané

D

13.1. Introduction

13.2. Isolation des transactions

13.3. Verrouillage explicite

13.4. Vérification de cohérence des données au niveau de l'application

13.5. Avertissements

13.6. Verrous et index

III-K - Conseils sur les performances

D

14.1. Utiliser EXPLAIN

14.2. Statistiques utilisées par le planificateur

14.3. Contrôler le planificateur avec des clauses JOIN explicites

14.4. Remplir une base de données

14.5. Configuration avec une perte acceptée

IV - Administration du serveur

Cette partie couvre des thèmes de grand intérêt pour un administrateur de bases de données PostgreSQL™, à savoir l'installation du logiciel, la mise en place et la configuration du serveur, la gestion des utilisateurs et des bases de données et la maintenance. Tout administrateur d'un serveur PostgreSQL™, même pour un usage personnel, mais plus particulièrement en production, doit être familier des sujets abordés dans cette partie.

Les informations sont ordonnées de telle sorte qu'un nouvel utilisateur puisse les lire linéairement du début à la fin. Cependant les chapitres sont indépendants et peuvent être lus séparément. L'information est présentée dans un style narratif, regroupée en unités thématiques. Les lecteurs qui recherchent une description complète d'une commande particulière peuvent se référer à la Partie VI, « Référence ».

Les premiers chapitres peuvent être compris sans connaissances préalables. Ainsi, de nouveaux utilisateurs installant leur propre serveur peuvent commencer leur exploration avec cette partie.

Le reste du chapitre concerne l'optimisation (tuning) et la gestion. Le lecteur doit être familier avec l'utilisation générale du système de bases de données PostgreSQL™. Les lecteurs sont encouragés à regarder la Partie I, « Tutoriel » et la Partie II, « Langage SQL » pour obtenir des informations complémentaires.

IV-A - Procédure d'installation de PostgreSQL™ du code source

e

IV-B - Installation à partir du code source sur Windows™

e

IV-C - Configuration du serveur et mise en place

e

IV-D - Configuration du serveur

e

IV-E - Authentification du client

d

IV-F - Rôles de la base de données

e

IV-G - Administration des bases de données

e

IV-H - Localisation

e

IV-I - Planifier les tâches de maintenance

e

IV-J - Sauvegardes et restaurations

d

IV-K - Haute disponibilité, répartition de charge et réplication

d

IV-L - Configuration de la récupération

d

IV-M - Surveiller l'activité de la base de données

d

IV-N - Surveiller l'utilisation des disques

d

IV-O - Fiabilité et journaux de transaction

d

IV-P - Tests de régression

d

V - Interfaces client

Cette partie décrit les interfaces de programmation client distribuées avec PostgreSQL™. Chacun de ces chapitres peut être lu indépendamment. On trouve beaucoup d'autres interfaces de programmation de clients, chacune distribuée séparément avec sa propre documentation. Les lecteurs de cette partie doivent être familiers de l'utilisation des requêtes SQL de manipulation et d'interrogation d'une base (voir la Partie II, « Langage SQL ») et surtout du langage de programmation utilisé par l'interface.

V-A - libpq - Bibliothèque C

d

V-A-1 - Fonctions de contrôle de connexion à la base de données

d

V-A-2 - Fonctions de statut de connexion

d

V-A-3 - Fonctions de commandes d'exécution

d

V-A-4 - Traitement des commandes asynchrones

d

V-A-5 - Récupérer le résultats des requêtes ligne par ligne

d

V-A-6 - Annuler des requêtes en cours d'exécution

d

V-A-7 - Interface à chemin rapide

d

V-A-8 - Notification asynchrone

d

V-A-9 - Fonctions associées avec la commande COPY

d

V-A-10 - Fonctions de contrôle

d

V-A-11 - Fonctions diverses

d

V-A-12 - Traitement des messages

d

V-A-13 - Système d'événements

s

V-A-14 - Variables d'environnement

d

V-A-15 - Fichier de mots de passe

d

V-A-16 - Fichier des connexions de service

d

V-A-17 - Recherches LDAP des paramètres de connexion

s

V-A-18 - Support de SSL

f

V-A-19 - Comportement des programmes threadés

f

V-A-20 - Construire des applications avec libpq

d

V-A-21 - Exemples de programmes

r

V-B - Objets larges

g

32.1. Introduction

32.2. Fonctionnalités d'implémentation

32.3. Interfaces client

32.4. Fonctions du côté serveur

32.5. Programme d'exemple

V-C - ECPG SQL embarqué en C

J

33.1. Le Concept

33.2. Gérer les Connexions à la Base de Données

33.3. Exécuter des Commandes SQL

33.4. Utiliser des Variables Hôtes

33.5. SQL Dynamique

33.6. Librairie pgtypes

33.7. Utiliser les Zones de Descripteur

33.8. Gestion des Erreurs

33.9. Directives de Préprocesseur

33.10. Traiter des Programmes en SQL Embarqué

33.11. Fonctions de la Librairie

33.12. Large Objects

33.13. Applications C++

33.14. Commandes SQL Embarquées

33.15. Mode de Compatibilité Informix™

33.16. Fonctionnement Interne

V-D - Schéma d'information

S

34.1. Le schéma

34.2. Types de données

34.3. information_schema_catalog_name

34.4. administrable_role_authorizations

34.5. applicable_roles

34.6. attributes

34.7. character_sets

34.8. check_constraint_routine_usage

34.9. check_constraints

34.10. collations

34.11. collation_character_set_applicability

34.12. column_domain_usage

34.13. column_options

34.14. column_privileges

34.15. column_udt_usage

34.16. columns

34.17. constraint_column_usage

34.18. constraint_table_usage

34.19. data_type_privileges

34.20. domain_constraints

34.21. domain_udt_usage

34.22. domains

34.23. element_types

34.24. enabled_roles

34.25. foreign_data_wrapper_options

34.26. foreign_data_wrappers

34.27. foreign_server_options

34.28. foreign_servers

34.29. foreign_table_options

34.30. foreign_tables

34.31. key_column_usage

34.32. parameters

34.33. referential_constraints

34.34. role_column_grants

34.35. role_routine_grants

34.36. role_table_grants

34.37. role_udt_grants

34.38. role_usage_grants

34.39. routine_privileges

34.40. routines

34.41. schemata

34.42. sequences

34.43. sql_features

34.44. sql_implementation_info

34.45. sql_languages

34.46. sql_packages

34.47. sql_parts

34.48. sql_sizing

34.49. sql_sizing_profiles

34.50. table_constraints

34.51. table_privileges

34.52. tables

34.53. triggered_update_columns

34.54. triggers

34.55. udt_privileges

34.56. usage_privileges

34.57. user_defined_types

34.58. user_mapping_options

34.59. user_mappings

34.60. view_column_usage

34.61. view_routine_usage

34.62. view_table_usage

34.63. views

VI - Programmation serveur

Cette partie traite des possibilités d'extension des fonctionnalités du serveur par l'ajout de fonctions utilisateur, de types de données, de déclencheurs (triggers), etc. Il est préférable de n'aborder ces sujets, avancés, qu'après avoir compris tous les autres.

Les derniers chapitres décrivent les langages de programmation serveur disponibles avec PostgreSQL™ ainsi que les problèmes de ces langages en général. Il est essentiel de lire au minimum les premières sections du Chapitre 35, Étendre SQL (qui traitent des fonctions) avant de se plonger dans les langages de programmation serveur.

VI-A - Étendre SQL

F

35.1. L'extensibilité

35.2. Le système des types de PostgreSQL™

35.3. Fonctions utilisateur

35.4. Fonctions en langage de requêtes (SQL)

35.5. Surcharge des fonctions

35.6. Catégories de volatilité des fonctions

35.7. Fonctions en langage de procédures

35.8. Fonctions internes

35.9. Fonctions en langage C

35.10. Agrégats utilisateur

35.11. Types utilisateur

35.12. Opérateurs définis par l'utilisateur

35.13. Informations sur l'optimisation d'un opérateur

35.14. Interfacer des extensions d'index

35.15. Empaqueter des objets dans une extension

35.16. Outils de construction d'extension

VI-B - Déclencheurs (triggers)

F

36.1. Aperçu du comportement des déclencheurs

36.2. Visibilité des modifications des données

36.3. Écrire des fonctions déclencheurs en C

36.4. Un exemple complet de trigger

VI-C - Déclencheurs (triggers) sur événement

F

37.1. Aperçu du fonctionnement des triggers sur évènement

37.2. Matrice de déclenchement des triggers sur évènement

37.3. Écrire des fonctions trigger sur événement en C

37.4. Un exemple complet de trigger sur événement

VI-D - Système de règles

F

38.1. Arbre de requêtes

38.2. Vues et système de règles

38.3. Vues matérialisées

38.4. Règles sur insert, update et delete

38.5. Règles et droits

38.6. Règles et statut de commande

38.7. Règles contre déclencheurs

VI-E - Langages de procédures

F

39.1. Installation des langages de procédures

VI-F - PL/pgSQL - Langage de procédures SQL

F

40.1. Aperçu

40.2. Structure de PL/pgSQL

40.3. Déclarations

40.4. Expressions

40.5. Instructions de base

40.6. Structures de contrôle

40.7. Curseurs

40.8. Erreurs et messages

40.9. Procédures trigger

40.10. Les dessous de PL/pgSQL

40.11. Astuces pour développer en PL/pgSQL

40.12. Portage d'Oracle™ PL/SQL

VI-G - PL/Tcl - Langage de procédures Tcl

F

41.1. Aperçu

41.2. Fonctions et arguments PL/Tcl

41.3. Valeurs des données avec PL/Tcl

41.4. Données globales avec PL/Tcl

41.5. Accès à la base de données depuis PL/Tcl

41.6. Procédures pour déclencheurs en PL/Tcl

41.7. Procédures de trigger sur événement en PL/Tcl

41.8. Les modules et la commande unknown

41.9. Noms de procédure Tcl

VI-H - PL/Perl - Langage de procédures Perl

F

42.1. Fonctions et arguments PL/Perl

42.2. Valeurs en PL/Perl

42.3. Fonction incluses

42.4. Valeurs globales dans PL/Perl

42.5. Niveaux de confiance de PL/Perl

42.6. Déclencheurs PL/Perl

42.7. Triggers sur événements avec PL/Perl

42.8. PL/Perl sous le capot

VI-I - PL/Python - Langage de procédures Python

D

43.1. Python 2 et Python 3

43.2. Fonctions PL/Python

43.3. Valeur des données avec PL/Python

43.4. Sharing Data

43.5. Blocs de code anonymes

43.6. Fonctions de déclencheurs

43.7. Accès à la base de données

43.8. Sous-transactions explicites

43.9. Fonctions outils

43.10. Variables d'environnement

VI-J - Interface de programmation serveur

D

44.1. Fonctions d'interface

44.2. Fonctions de support d'interface

44.3. Gestion de la mémoire

44.4. Visibilité des modifications de données

44.5. Exemples

VI-K - Processus en tâche de fond (background worker)

d

VI-L - Décodage logique (Logical Decoding)

d

46.1. Exemples de décodage logique

46.2. Concepts de décodage logique

46.3. Interface du protocole de réplication par flux

46.4. Interface SQL de décodage logique

46.5. Catalogues systèmes liés au décodage logique

46.6. Plugins de sortie de décodage logique

46.7. Écrivains de sortie de décodage logique

46.8. Support de la réplication synchrone pour le décodage logique

VII - Référence

Les points abordés dans ce référentiel ont pour objectif de fournir, de manière concise, un résumé précis, complet, formel et faisant autorité sur leurs sujets respectifs. Des informations complémentaires sur l'utilisation de PostgreSQL™ sont présentées, dans d'autres parties de cet ouvrage, sous la forme de descriptions, de tutoriels ou d'exemples. On pourra se reporter à la liste de références croisées disponible sur chaque page de référence.

Les entrées du référentiel sont également disponibles sous la forme de pages « man » traditionnelles.

VII-A - Commandes SQL

S

ABORT — Interrompre la transaction en cours

ALTER AGGREGATE — Modifier la définition d'une fonction d'agrégat

ALTER COLLATION — modifie la définition d'une collation

ALTER CONVERSION — Modifier la définition d'une conversion

ALTER DATABASE — Modifier une base de données

ALTER DEFAULT PRIVILEGES — définit les droits d'accès par défaut

ALTER DOMAIN — Modifier la définition d'un domaine

ALTER EVENT TRIGGER — modifier la définition d'un trigger sur un événement

ALTER EXTENSION — modifie la définition d'une extension

ALTER FOREIGN DATA WRAPPER — modifier la définition d'un wrapper de données distantes

ALTER FOREIGN TABLE — modifie la définition de la table distante

ALTER FUNCTION — Modifier la définition d'une fonction

ALTER GROUP — Modifier le nom d'un rôle ou la liste de ses membres

ALTER INDEX — Modifier la définition d'un index

ALTER LANGUAGE — Modifier la définition d'un langage procédural

ALTER LARGE OBJECT — Modifier la définition d'un Large Object

ALTER MATERIALIZED VIEW — modifier la définition d'une vue matérialisée

ALTER OPERATOR — Modifier la définition d'un opérateur

ALTER OPERATOR CLASS — Modifier la définition d'une classe d'opérateur

ALTER OPERATOR FAMILY — Modifier la définition d'une famille d'opérateur

ALTER ROLE — Modifier un rôle de base de données

ALTER RULE — modifier la définition d'une règle

ALTER SCHEMA — Modifier la définition d'un schéma

ALTER SEQUENCE — Modifier la définition d'un générateur de séquence

ALTER SERVER — modifier la définition d'un serveur distant

ALTER SYSTEM — Modifier un paramètre de configuration du serveur

ALTER TABLE — Modifier la définition d'une table

ALTER TABLESPACE — Modifier la définition d'un tablespace

ALTER TEXT SEARCH CONFIGURATION — modifier la définition d'une configuration de recherche plein texte

ALTER TEXT SEARCH DICTIONARY — modifier la définition d'un dictionnaire de recherche plein texte

ALTER TEXT SEARCH PARSER — modifier la définition d'un analyseur de recherche plein texte

ALTER TEXT SEARCH TEMPLATE — modifier la définition d'un modèle de recherche plein texte

ALTER TRIGGER — Modifier la définition d'un déclencheur

ALTER TYPE — Modifier la définition d'un type

ALTER USER — Modifier un rôle de la base de données

ALTER USER MAPPING — change la définition d'une correspondance d'utilisateurs (user mapping)

ALTER VIEW — modifier la définition d'une vue

ANALYZE — Collecter les statistiques d'une base de données

BEGIN — Débuter un bloc de transaction

CHECKPOINT — Forcer un point de vérification dans le journal des transactions

CLOSE — Fermer un curseur

CLUSTER — Réorganiser une table en fonction d'un index

COMMENT — Définir ou modifier le commentaire associé à un objet

COMMIT — Valider la transaction en cours

COMMIT PREPARED — Valider une transaction préalablement préparée en vue d'une validation en deux phases

COPY — Copier des données depuis/vers un fichier vers/depuis une table

CREATE AGGREGATE — Définir une nouvelle fonction d'agrégat

CREATE CAST — Définir un transtypage

CREATE COLLATION — définit une nouvelle collation

CREATE CONVERSION — Définir une nouvelle conversion d'encodage

CREATE DATABASE — Créer une nouvelle base de données

CREATE DOMAIN — Définir un nouveau domaine

CREATE EVENT TRIGGER — définir un nouveau trigger sur événement

CREATE EXTENSION — installe une nouvelle extension

CREATE FOREIGN DATA WRAPPER — définit un nouveau wrapper de données distantes

CREATE FOREIGN TABLE — crée une nouvelle table distante

CREATE FUNCTION — Définir une nouvelle fonction

CREATE GROUP — Définir un nouveau rôle de base de données

CREATE INDEX — Définir un nouvel index

CREATE LANGUAGE — Définir un nouveau langage procédural

CREATE MATERIALIZED VIEW — définir une nouvelle vue matérialisée

CREATE OPERATOR — Définir un nouvel opérateur

CREATE OPERATOR CLASS — Définir une nouvelle classe d'opérateur

CREATE OPERATOR FAMILY — définir une nouvelle famille d'opérateur

CREATE ROLE — Définir un nouveau rôle de base de données

CREATE RULE — Définir une nouvelle règle de réécriture

CREATE SCHEMA — Définir un nouveau schéma

CREATE SEQUENCE — Définir un nouveau générateur de séquence

CREATE SERVER — Définir un nouveau serveur distant

CREATE TABLE — Définir une nouvelle table

CREATE TABLE AS — Définir une nouvelle table à partir des résultats d'une requête

CREATE TABLESPACE — Définir un nouvel tablespace

CREATE TEXT SEARCH CONFIGURATION — définir une nouvelle configuration de recherche plein texte

CREATE TEXT SEARCH DICTIONARY — définir un dictionnaire de recherche plein texte

CREATE TEXT SEARCH PARSER — définir un nouvel analyseur de recherche plein texte

CREATE TEXT SEARCH TEMPLATE — définir un nouveau modèle de recherche plein texte

CREATE TRIGGER — Définir un nouveau déclencheur

CREATE TYPE — Définir un nouveau type de données

CREATE USER — Définir un nouveau rôle de base de données

CREATE USER MAPPING — Définir une nouvelle correspondance d'utilisateur (user mapping) pour un serveur distant

CREATE VIEW — Définir une vue

DEALLOCATE — Désaffecter (libérer) une instruction préparée

DECLARE — Définir un curseur

DELETE — Supprimer des lignes d'une table

DISCARD — Annuler l'état de la session

DO — exécute un bloc de code anonyme

DROP AGGREGATE — Supprimer une fonction d'agrégat

DROP CAST — Supprimer un transtypage

DROP COLLATION — supprime une collation

DROP CONVERSION — Supprimer une conversion

DROP DATABASE — Supprimer une base de données

DROP DOMAIN — Supprimer un domaine

DROP EXTENSION — Supprime une extension

DROP EVENT TRIGGER — supprimer un trigger sur événement

DROP FOREIGN DATA WRAPPER — Supprimer un wrapper de données distantes

DROP FOREIGN TABLE — Supprime une table distante

DROP FUNCTION — Supprimer une fonction

DROP GROUP — Supprimer un rôle de base de données

DROP INDEX — Supprimer un index

DROP LANGUAGE — Supprimer un langage procédural

DROP MATERIALIZED VIEW — supprimer une vue matérialisée

DROP OPERATOR — Supprimer un opérateur

DROP OPERATOR CLASS — Supprimer une classe d'opérateur

DROP OPERATOR FAMILY — Supprimer une famille d'opérateur

DROP OWNED — Supprimer les objets de la base possédés par un rôle

DROP ROLE — Supprimer un rôle de base de données

DROP RULE — Supprimer une règle de réécriture

DROP SCHEMA — Supprimer un schéma

DROP SEQUENCE — Supprimer une séquence

DROP SERVER — Supprimer un descripteur de serveur distant

DROP TABLE — Supprimer une table

DROP TABLESPACE — Supprimer un tablespace

DROP TEXT SEARCH CONFIGURATION — Supprimer une configuration de recherche plein texte

DROP TEXT SEARCH DICTIONARY — Supprimer un dictionnaire de recherche plein texte

DROP TEXT SEARCH PARSER — Supprimer un analyseur de recherche plein texte

DROP TEXT SEARCH TEMPLATE — Supprimer un modèle de recherche plein texte

DROP TRIGGER — Supprimer un déclencheur

DROP TYPE — Supprimer un type de données

DROP USER — Supprimer un rôle de base de données

DROP USER MAPPING — Supprimer une correspondance d'utilisateur pour un serveur distant

DROP VIEW — Supprimer une vue

END — Valider la transaction en cours

EXECUTE — Exécuter une instruction préparée

EXPLAIN — Afficher le plan d'exécution d'une instruction

FETCH — Récupérer les lignes d'une requête à l'aide d'un curseur

GRANT — Définir les droits d'accès

INSERT — Insérer de nouvelles lignes dans une table

LISTEN — Attendre une notification

LOAD — Charger une bibliothèque partagée

LOCK — verrouiller une table

MOVE — positionner un curseur

NOTIFY — engendrer une notification

PREPARE — prépare une instruction pour exécution

PREPARE TRANSACTION — prépare la transaction en cours pour une validation en deux phases

REASSIGN OWNED — Modifier le propriétaire de tous les objets de la base appartenant à un rôle spécifique

REFRESH MATERIALIZED VIEW — remplacer le contenu d'une vue matérialisée

REINDEX — reconstruit les index

RELEASE SAVEPOINT — détruit un point de sauvegarde précédemment défini

RESET — reinitialise un paramètre d'exécution à sa valeur par défaut

REVOKE — supprime les droits d'accès

ROLLBACK — annule la transaction en cours

ROLLBACK PREPARED — annule une transaction précédemment préparée en vue d'une validation en deux phases

ROLLBACK TO SAVEPOINT — annule les instructions jusqu'au point de sauvegarde

SAVEPOINT — définit un nouveau point de sauvegarde à l'intérieur de la transaction en cours

SECURITY LABEL — Définir ou modifier une label de sécurité appliquée à un objet

SELECT — récupère des lignes d'une table ou d'une vue

SELECT INTO — définit une nouvelle table à partir des résultats d'une requête

SET — change un paramètre d'exécution

SET CONSTRAINTS — initialise le moment de vérification de contrainte de la transaction en cours

SET ROLE — initialise l'identifiant utilisateur courant de la session en cours

SET SESSION AUTHORIZATION — Initialise l'identifiant de session de l'utilisateur et l'identifiant de l'utilisateur actuel de la session en cours

SET TRANSACTION — initialise les caractéristiques de la transaction actuelle

SHOW — affiche la valeur d'un paramètre d'exécution

START TRANSACTION — débute un bloc de transaction

TRUNCATE — vide une table ou un ensemble de tables

UNLISTEN — arrête l'écoute d'une notification

UPDATE — mettre à jour les lignes d'une table

VACUUM — récupère l'espace inutilisé et, optionnellement, analyse une base

VALUES — calcule un ensemble de lignes

VII-B - Applications client de PostgreSQL

s

clusterdb — Grouper une base de données PostgreSQL™

createdb — Créer une nouvelle base de données PostgreSQL™

createlang — Installer un langage procédural sous PostgreSQL™

createuser — Définir un nouveau compte utilisateur PostgreSQL™

dropdb — Supprimer une base de données PostgreSQL™

droplang — Supprimer un langage procédural

dropuser — Supprimer un compte utilisateur PostgreSQL™

ecpg — Préprocesseur C pour le SQL embarqué

pg_basebackup — réalise une sauvegarde de base d'une instance PostgreSQL™

pg_config — récupèrer des informations sur la version installée de PostgreSQL™

pg_dump — sauvegarder une base de données PostgreSQL™ dans un script ou tout autre fichier d'archive

pg_receivexlog — suit le flux des journaux de transactions d'un serveur PostgreSQL™

pg_recvlogical — contrôle les flux de décodage logique de PostgreSQL™

pg_dumpall — extraire une grappe de bases de données PostgreSQL™ dans un fichier de script

pg_isready — vérifier le statut de connexion d'un serveur PostgreSQL™

pg_restore — restaure une base de données PostgreSQL™ à partir d'un fichier d'archive créé par pg_dump

psql — terminal interactif PostgreSQL™

reindexdb — reindexe une base de données PostgreSQL™

vacuumdb — récupère l'espace inutilisé et, optionnellement, analyse une base de données PostgreSQL™

VII-C - Applications relatives au serveur PostgreSQL

D

initdb — Créer un nouveau « cluster »

pg_controldata — afficher les informations de contrôle d'un groupe de bases de données PostgreSQL™

pg_ctl — initialiser, démarrer, arrêter ou contrôler le serveur PostgreSQL™

pg_resetxlog — réinitialiser les WAL et les autres informations de contrôle d'une grappe de bases de données PostgreSQL™

postgres — Serveur de bases de données PostgreSQL™

postmaster — Serveur de bases de données PostgreSQL™

VIII - Internes

S

VIII-A - Présentation des mécanismes internes de PostgreSQL

x

VIII-B - Catalogues système

d

VIII-C - Protocole client/serveur

d

VIII-D - Conventions de codage pour PostgreSQL

d

VIII-E - Support natif des langues

d

VIII-F - Écrire un gestionnaire de langage procédural

d

VIII-G - Écrire un wrapper de données distantes

d

VIII-H - Optimiseur génétique de requêtes (Genetic Query Optimizer)

d

VIII-I - Définition de l'interface des méthodes d'accès aux index

d

VIII-J - Index GiST

d

VIII-K - Index SP-GiST

d

VIII-L - Index GIN

d

VIII-M - Stockage physique de la base de données

d

VIII-N - Interface du moteur, BKI

d

VIII-O - Comment le planificateur utilise les statistiques

d

IX - Annexes

IX-A - Codes d'erreurs de PostgreSQL™

s

IX-B - Support de date/heure

s

IX-C - Mots-clé SQL

s

IX-D - Conformité SQL

s

IX-E - Notes de version

s

IX-F - Modules supplémentaires fournis

s

IX-G - Programmes supplémentaires fournis

s

IX-H - Projets externes

s

IX-I - Dépôt du code source

s

IX-J - Documentation

s

IX-K - Acronymes

s

IX-L - Traduction française

Bibliographie

d

IX-M - Bibliographie

Références sélectionnées et lectures autour du SQL et de PostgreSQL™.

Quelques livres blancs et rapports techniques réalisés par l'équipe d'origine de développement de POSTGRES™ sont disponibles sur le site web du département des sciences informatiques de l'université de Californie.

IX-M-1 - Livres de référence sur SQL

Textes de référence sur les fonctionnalités de SQL.

[bowman01] The Practical SQL Handbook. Bowman et al, 2001. Using SQL Variants. Fourth Edition. Judith Bowman, Sandra Emerson, et Marcy Darnovsky. 0-201-70309-2. 2001. Addison-Wesley Professional. Copyright © 2001 Addison-Wesley Longman, Inc..

[date97] A Guide to the SQL Standard. Date and Darwen, 1997. A user's guide to the standard database language SQL. Fourth Edition. C. J. Date et Hugh Darwen. 0-201-96426-0. 1997. Addison-Wesley. Copyright © 1997 Addison-Wesley Longman, Inc..

[date04] An Introduction to Database Systems. Date, 2004. Eighth Edition. C. J. Date. 0-321-19784-4. 2003. Addison-Wesley. Copyright © 2004 Pearson Education, Inc..

[elma04] Fundamentals of Database Systems. Fourth Edition. Ramez Elmasri et Shamkant Navathe. 0-321-12226-7. 2003. Addison-Wesley. Copyright © 2004.

[melt93] Understanding the New SQL. Melton and Simon, 1993. A complete guide. Jim Melton et Alan R. Simon. 1-55860-245-3. 1993. Morgan Kaufmann. Copyright © 1993 Morgan Kaufmann Publishers, Inc..

[ull88] Principles of Database and Knowledge. Base Systems. Ullman, 1988. Jeffrey D. Ullman. Volume 1. Computer Science Press. 1988.

IX-M-2 - Documentation spécifique sur PostgreSQL

Cette section concerne la documentation relative à PostgreSQL.

[sim98] Enhancement of the ANSI SQL Implementation of PostgreSQL. Simkovics, 1998. Stefan Simkovics. 29 novembre 1998. Département des systèmes d'informations, université de technologie de Vienne. Vienne, Autriche.

[yu95] The Postgres95. User Manual. Yu and Chen, 1995. A. Yu et J. Chen. . 5 septembre 1995. Université de Californie. Berkeley, Californie.

[fong] The design and implementation of the POSTGRES™ query optimizer. Zelaine Fong. Université de Californie, Berkeley, département des sciences informatiques.

IX-M-3 - Procédures et articles

Cette section recense les articles et brèves.

[olson93] Partial indexing in POSTGRES: research project. Olson, 1993. Nels Olson. 1993. UCB Engin T7.49.1993 O676. Université de Californie. Berkeley, Californie.

« A Unified Framework for Version Modeling Using Production Rules in a Database System ». Ong and Goh, 1990. L. Ong et J. Goh. ERL Technical Memorandum M90/33. Avril 1990. Université de Californie. Berkely, Californie.

« The POSTGRES™ data model ». Rowe and Stonebraker, 1987. L. Rowe et M. Stonebraker. VLDB Conference. Septembre 1987. Brighton, Angleterre. .

« Generalized Partial Indexes (version en cache)  ». Seshardri, 1995. P. Seshadri et A. Swami. Eleventh International Conference on Data Engineering. 6-10 mars 1995. Taipeh, Taiwan. . 1995. Cat. No.95CH35724. IEEE Computer Society Press. Los Alamitos, Californie. 420-7.

« The design of POSTGRES™ ». Stonebraker and Rowe, 1986. M. Stonebraker et L. Rowe. ACM-SIGMOD Conference on Management of Data. Mai 1986. Washington, DC. .

« The design of the POSTGRES™ rules system ». Stonebraker, Hanson, Hong, 1987. M. Stonebraker, E. Hanson, et C. H. Hong. IEEE Conference on Data Engineering. Février 1987. Los Angeles, Californie. .

« The design of the POSTGRES™ storage system ». Stonebraker, 1987. M. Stonebraker. VLDB Conference. Septembre 1987. Brighton, Angleterre. .

« A commentary on the POSTGRES™ rules system ». Stonebraker et al, 1989. M. Stonebraker, M. Hearst, et S. Potamianos. SIGMOD Record 18(3). Septembre 1989.

« The case for partial indexes ». Stonebraker, M, 1989b. M. Stonebraker. SIGMOD Record 18(4). 4-11. Décembre 1989.

« The implementation of POSTGRES™ ». Stonebraker, Rowe, Hirohama, 1990. M. Stonebraker, L. A. Rowe, et M. Hirohama. Transactions on Knowledge and Data Engineering 2(1). IEEE. Mars 1990.

« On Rules, Procedures, Caching and Views in Database Systems ». Stonebraker et al, ACM, 1990. M. Stonebraker, A. Jhingran, J. Goh, et S. Potamianos. ACM-SIGMOD Conference on Management of Data. Juin 1990. .

Liste des illustrations

9.1. Feuille de style XSLT pour convertir du SQL/XML en HTML

54.1. Diagramme structuré d'un algorithme génétique

Liste des tableaux

4.1. Séquences d'échappements avec antislash

4.2. Précédence des opérateurs (en ordre décroissant)

8.1. Types de données

8.2. Types numériques

8.3. Types monétaires

8.4. Types caractère

8.5. Types caractères spéciaux

8.6. Types de données binaires

8.7. Octets littéraux bytea à échapper

8.8. Octets échappés en sortie pour bytea

8.9. Types date et heure

8.10. Saisie de date

8.11. Saisie d'heure

8.12. Saisie des fuseaux horaires

8.13. Saisie de dates/heures spéciales

8.14. Styles d'affichage de date/heure

8.15. Convention de présentation des dates

8.16. Abréviations d'unités d'intervalle ISO 8601

8.17. Saisie d'intervalle

8.18. Exemples de styles d'affichage d'intervalles

8.19. Type de données booléen

8.20. Types géométriques

8.21. Types d'adresses réseau

8.22. Exemples de saisie de types cidr

8.23. Types primitifs JSON et types PostgreSQL™ correspondant

8.24. Types identifiant d'objet

8.25. Pseudo-Types

9.1. Opérateurs de comparaison

9.2. Opérateurs mathématiques

9.3. Fonctions mathématiques

9.4. Fonctions de génération de nombres aléatoires

9.5. Fonctions trigonométriques

9.6. Fonctions et opérateurs SQL pour le type chaîne

9.7. Autres fonctions de chaîne

9.8. Conversions intégrées

9.9. Fonctions et opérateurs SQL pour chaînes binaires

9.10. Autres fonctions sur les chaînes binaires

9.11. Opérateurs sur les chaînes de bits

9.12. Opérateurs de correspondance des expressions rationnelles

9.13. Atomes d'expressions rationnelles

9.14. quantificateur d'expressions rationnelles

9.15. Contraintes des expressions rationnelles

9.16. Échappements de caractère dans les expressions rationnelles

9.17. Échappement de raccourcis de classes dans les expressions rationnelles

9.18. Échappements de contrainte dans les expressions rationnelles

9.19. Rétro-références dans les expressions rationnelles

9.20. Lettres d'option intégrées à une ERA

9.21. Fonctions de formatage

9.22. Modèles pour le formatage de champs de type date/heure

9.23. Modificateurs de motifs pour le formatage des dates/heures

9.24. Motifs de modèle pour le formatage de valeurs numériques

9.25. Modifications de motifs pour le formatage numérique

9.26. Exemples avec to_char

9.27. Opérateurs date/heure

9.28. Fonctions date/heure

9.29. Variantes AT TIME ZONE

9.30. Fonctions de support enum

9.31. Opérateurs géométriques

9.32. Fonctions géométriques

9.33. Fonctions de conversion de types géométriques

9.34. Opérateurs cidr et inet

9.35. Fonctions cidr et inet

9.36. Fonctions macaddr

9.37. Opérateurs de recherche plein texte

9.38. Fonctions de la recherche plein texte

9.39. Fonctions de débogage de la recherche plein texte

9.40. Opérateurs json et jsonb

9.41. Opérateurs jsonb supplémentaires

9.42. Fonctions de création de données JSON

9.43. Fonctions de traitement du JSON

9.44. Fonctions séquence

9.45. Opérateurs pour les tableaux

9.46. Fonctions pour les tableaux

9.47. Opérateurs pour les types range

9.48. Fonctions range

9.49. Fonctions d'agrégat générales

9.50. Fonctions d'agrégats pour les statistiques

9.51. Fonctions d'agrégat par ensemble trié

9.52. Fonctions d'agrégat par ensemble hypothétique

9.53. Fonctions Window généralistes

9.54. Fonctions de génération de séries

9.55. Fonctions de génération d'indices

9.56. Fonctions d'information de session

9.57. Fonctions de consultation des privilèges d'accès

9.58. Fonctions d'interrogation de visibilité dans les schémas

9.59. Fonctions d'information du catalogue système

9.60. Fonctions d'informations sur les commentaires

9.61. ID de transaction et instantanés

9.62. Composants de l'instantané

9.63. Fonctions agissant sur les paramètres de configuration

9.64. Fonctions d'envoi de signal au serveur

9.65. Fonctions de contrôle de la sauvegarde

9.66. Fonctions d'information sur la restauration

9.67. Fonctions de contrôle de la restauration

9.68. Fonction de synchronisation de snapshot

9.69. Fonctions SQL pour la réplication

9.70. Fonctions de calcul de la taille des objets de la base de données

9.71. Fonctions de récupération de l'emplacement des objets de la base de données

9.72. Fonctions d'accès générique aux fichiers

9.73. Fonctions de verrous consultatifs

12.1. Types de jeton de l'analyseur par défaut

13.1. Niveaux d'isolation standards des transactions SQL

13.2. Modes de verrou conflictuels

13.3. Verrous en conflit au niveau ligne

17.1. Paramètres system v ipc

17.2. Utilisation des fichiers serveur SSL

18.1. Niveaux de sévérité des messages

18.2. Clé d'option courte

22.1. Jeux de caractères de PostgreSQL™

22.2. Conversion de jeux de caractères client/serveur

25.1. Matrice de fonctionnalités : haute disponibilité, répartition de charge et réplication

27.1. Vues statistiques standards

27.2. Vue pg_stat_activity

27.3. Vue pg_stat_archiver

27.4. Vue pg_stat_bgwriter

27.5. Vue pg_stat_database

27.6. Vue pg_stat_all_tables

27.7. Vue pg_stat_all_indexes

27.8. Vue pg_statio_all_tables

27.9. Vue pg_statio_all_indexes

27.10. Vue pg_statio_all_sequences

27.11. Vue pg_stat_user_functions

27.12. Vue pg_stat_replication

27.13. Vue pg_stat_database_conflicts

27.14. Fonctions supplémentaires de statistiques

27.15. Fonctions statistiques par processus serveur

27.16. Sondes disponibles pour DTrace

27.17. Types définis utilisés comme paramètres de sonde

31.1. Description des modes SSL

31.2. Utilisation des fichiers SSL libpq/client

32.1. Fonctions SQL pour les Large Objects

33.1. Correspondance Entre les Types PostgreSQL et les Types de Variables C

33.2. Formats d'Entrée Valides pour PGTYPESdate_from_asc

33.3. Formats d'Entrée Valides pour PGTYPESdate_fmt_asc

33.4. Formats d'Entrée Valides pour rdefmtdate

33.5. Formats d'Entrée Valide pour PGTYPEStimestamp_from_asc

34.1. Colonnes de information_schema_catalog_name

34.2. Colonnes de administrable_role_authorizations

34.3. Colonnes de applicable_roles

34.4. Colonnes de attributes

34.5. Colonnes de character_sets

34.6. Colonnes de check_constraint_routine_usage

34.7. Colonnes de check_constraints

34.8. Colonnes de collations

34.9. Colonnes de collation_character_set_applicability

34.10. Colonnes de column_domain_usage

34.11. Colonnes de column_options

34.12. Colonnes de column_privileges

34.13. Colonnes de column_udt_usage

34.14. Colonnes de columns

34.15. Colonnes de constraint_column_usage

34.16. Colonnes de constraint_table_usage

34.17. Colonnes de data_type_privileges

34.18. Colonnes de domain_constraints

34.19. Colonnes de domain_udt_usage

34.20. Colonnes de domains

34.21. Colonnes de element_types

34.22. Colonnes de enabled_roles

34.23. Colonnes de foreign_data_wrapper_options

34.24. Colonnes de foreign_data_wrappers

34.25. Colonnes de foreign_server_options

34.26. Colonnes de foreign_servers

34.27. Colonnes de foreign_table_options

34.28. Colonnes de foreign_tables

34.29. Colonnes de key_column_usage

34.30. Colonnes de parameters

34.31. Colonnes de referential_constraints

34.32. Colonnes de role_column_grants

34.33. Colonnes de role_routine_grants

34.34. Colonnes de role_table_grants

34.35. Colonnes de role_udt_grants

34.36. Colonnes de role_usage_grants

34.37. Colonnes de routine_privileges

34.38. Colonnes de routines

34.39. Colonnes de schemata

34.40. Colonnes de sequences

34.41. Colonnes de sql_features

34.42. Colonnes de sql_implementation_info

34.43. Colonnes de sql_languages

34.44. Colonnes de sql_packages

34.45. Colonnes de sql_parts

34.46. Colonnes de sql_sizing

34.47. Colonnes de sql_sizing_profiles

34.48. Colonnes de table_constraints

34.49. Colonnes de table_privileges

34.50. Colonnes de tables

34.51. Colonnes de triggered_update_columns

34.52. Colonnes de triggers

34.53. Colonnes de udt_privileges

34.54. Colonnes de usage_privileges

34.55. Colonnes de user_defined_types

34.56. Colonnes de user_mapping_options

34.57. Colonnes de user_mappings

34.58. Colonnes de view_column_usage

34.59. Colonnes de view_routine_usage

34.60. Colonnes de view_table_usage

34.61. Colonnes de views

35.1. Équivalence des types C et des types SQL intégrés

35.2. Stratégies B-tree

35.3. Stratégies de découpage

35.4. Stratégies « R-tree » pour GiST à deux dimensions

35.5. Stratégies point SP-GiST

35.6. Stratégies des tableaux GIN

35.7. Fonctions d'appui de B-tree

35.8. Fonctions d'appui pour découpage

35.9. Fonctions d'appui pour GiST

35.10. Fonctions de support SP-GiST

35.11. Fonctions d'appui GIN

37.1. Support des triggers sur évènement par commande

40.1. Diagnostiques et erreurs

48.1. Catalogues système

48.2. Les colonnes de pg_aggregate

48.3. Colonnes de pg_am

48.4. Colonnes de pg_amop

48.5. Colonnes de pg_amproc

48.6. Colonnes de pg_attrdef

48.7. Colonnes de pg_attribute

48.8. Colonnes de pg_authid

48.9. Colonnes de pg_auth_members

48.10. Colonnes de pg_cast

48.11. Colonnes de pg_class

48.12. Colonnes de pg_event_trigger

48.13. Colonnes de pg_collation

48.14. Colonnes de pg_constraint

48.15. Colonnes de pg_conversion

48.16. Colonnes de pg_database

48.17. Colonnes de pg_db_role_setting

48.18. Colonnes de pg_default_acl

48.19. Colonnes de pg_depend

48.20. Colonnes de pg_description

48.21. Colonnes de pg_enum

48.22. Colonnes de pg_extension

48.23. Colonnes de pg_foreign_data_wrapper

48.24. Colonnes pg_foreign_server

48.25. Colonnes de pg_foreign_table

48.26. Colonnes de pg_index

48.27. Colonnes de pg_inherits

48.28. Colonnes de pg_language

48.29. Colonnes de pg_largeobject

48.30. Colonnes de pg_largeobject_metadata

48.31. Colonnes de pg_namespace

48.32. Colonnes de pg_opclass

48.33. Colonnes de pg_operator

48.34. Colonnes de pg_opfamily

48.35. Colonnes de pg_pltemplate

48.36. Colonnes de pg_proc

48.37. Colonnes de pg_range

48.38. Colonnes de pg_rewrite

48.39. Colonnes de pg_replication_slots

48.40. Colonnes de pg_seclabel

48.41. Colonnes de pg_shdepend

48.42. Colonnes de pg_shdescription

48.43. Colonnes de pg_shseclabel

48.44. Colonnes de pg_statistic

48.45. Colonnes de pg_tablespace

48.46. Colonnes de pg_trigger

48.47. Colonnes de pg_ts_config

48.48. Colonnes de pg_ts_config_map

48.49. Colonnes de pg_ts_dict

48.50. Colonnes de pg_ts_parser

48.51. Colonnes de pg_ts_template

48.52. Colonnes de pg_type

48.53. Codes typcategory

48.54. Colonnes de pg_user_mapping

48.55. Vues système

48.56. Colonnes de pg_available_extensions

48.57. Colonnes de pg_available_extension_versions

48.58. Colonnes de pg_cursors

48.59. Colonnes de pg_group

48.60. Colonnes de pg_indexes

48.61. Colonnes pg_locks

48.62. Colonnes de pg_matviews

48.63. Colonnes de pg_prepared_statements

48.64. Colonnes de pg_prepared_xacts

48.65. Colonnes de pg_roles

48.66. Colonnes de pg_rules

48.67. Colonnes de pg_seclabels

48.68. Colonnes de pg_settings

48.69. Colonnes de pg_shadow

48.70. Colonnes de pg_stats

48.71. Colonnes de pg_tables

48.72. Colonnes de pg_timezone_abbrevs

48.73. Colonnes de pg_timezone_names

48.74. Colonnes de pg_user

48.75. Colonnes de pg_user_mappings

48.76. Colonnes de pg_views

56.1. Classes d'opérateur GiST internes

57.1. Classes d'opérateur SP-GiST internes

58.1. Classes d'opérateur GIN internes

59.1. Contenu de PGDATA

59.2. Disposition d'une page

59.3. Disposition de PageHeaderData

59.4. Disposition de HeapTupleHeaderData

A.1. Codes d'erreur de PostgreSQL™

B.1. Noms de mois

B.2. Noms des jours de la semaine

B.3. Modificateurs de Champs Date/Heure

C.1. Mots-clé SQL

F.1. Représentations externes d'un cube

F.2. Opérateurs GiST du type cube

F.3. Fonctions cube

F.4. Fonctions earthdistance par cubes

F.5. Opérateurs earthdistance par points

F.6. Opérateurshstore

F.7. Fonctions hstore

F.8. Fonctions intarray

F.9. Opérateurs d'intarray

F.10. Types de données isn

F.11. Fonctions de isn

F.12. Opérateurs ltree

F.13. Fonctions ltree

F.14. Colonnes de pg_buffercache

F.15. Algorithmes supportés par crypt()

F.16. Nombre d'itération pour crypt()

F.17. Vitesse de l'algorithm de hachage

F.18. Résumé de fonctionnalités avec et sans OpenSSL

F.19. Colonnes de pgrowlocks

F.20. Colonnes de pg_stat_statements

F.21. Colonnes de pgstattuple

F.22. Fonctions de pg_trgm

F.23. Opérateurs de pg_trgm

F.24. Représentations externes de seg

F.25. Exemples d'entrées valides de type seg

F.26. Opérateurs GiST du type Seg

F.27. Fonctions Sepgsql

F.28. Fonctions tablefunc

F.29. Paramètres connectby

F.30. Fonctions pour la génération d'UUID

F.31. Fonctions renvoyant des constantes UUID

F.32. Fonctions

F.33. Paramètres de xpath_table

G.1. Variables automatiques

H.1. Interfaces clients maintenus en externe

H.2. Langages procéduraux maintenus en externe

Liste des exemples

8.1. Utilisation des types caractère

8.2. Utilisation du type boolean.

8.3. Utiliser les types de chaînes de bits

10.1. Résolution du type d'opérateur factoriel

10.2. Résolution de types pour les opérateurs de concaténation de chaînes

10.3. Résolution de types pour les opérateurs de valeur absolue et de négation

10.4. Résolution du type d'opérateur avec des inclusions de tableaux

10.5. Opérateur personnalisé sur un domaine

10.6. Résolution de types pour les arguments de la fonction arrondie

10.7. Résolution de types pour les fonctions retournant un segment de chaîne

10.8. Conversion de types pour le stockage de character

10.9. Résolution de types avec des types sous-spécifiés dans une union

10.10. Résolution de types dans une union simple

10.11. Résolution de types dans une union transposée

11.1. Mettre en place un index partiel pour exclure des valeurs courantes

11.2. Mettre en place un index partiel pour exclure les valeurs inintéressantes

11.3. Mettre en place un index d'unicité partiel

19.1. Exemple d'entrées de pg_hba.conf

19.2. Un exemple de fichier pg_ident.conf

31.1. Premier exemple de programme pour libpq

31.2. Deuxième exemple de programme pour libpq

31.3. Troisième exemple de programme pour libpq

32.1. Exemple de programme sur les objets larges avec libpq

33.1. Programme de Démonstration SQLDA

33.2. Programme ECPG Accédant à un Large Object

39.1. Installation manuelle de PL/Perl

40.1. Mettre entre guillemets des valeurs dans des requêtes dynamiques

40.2. Exceptions avec UPDATE/INSERT

40.3. Une procédure trigger PL/pgSQL

40.4. Une procédure d'audit par trigger en PL/pgSQL

40.5. Une fonction trigger en PL/pgSQL surune vue pour un audit

40.6. Une procédure trigger PL/pgSQL pour maintenir une table résumée

40.7. Une procédure PL/pgSQL pour un trigger d'événement

40.8. Portage d'une fonction simple de PL/SQL vers PL/pgSQL

40.9. Portage d'une fonction qui crée une autre fonction de PL/SQL vers PL/pgSQL

40.10. Portage d'une procédure avec manipulation de chaînes et paramètres OUT de PL/SQL vers PL/pgSQL

40.11. Portage d'une procédure de PL/SQL vers PL/pgSQL

F.1. Créer une table distante pour les journaux applicatifs PostgreSQL au format CSV