PostgreSQL : bases et tables

Création et modification de la structure. Les exemples construisent la base boutique, réutilisée dans les pages suivantes.

Bases de données

CREATE DATABASE boutique ENCODING 'UTF8' TEMPLATE template0;
\l
\c boutique
SELECT current_database();
-- DROP DATABASE boutique;       -- supprime la base et tout son contenu

Ne pas se connecter à la base que l’on supprime : se connecter à postgres avant un DROP DATABASE.

Schémas

Un schéma regroupe des objets au sein d’une base (par défaut public).

CREATE SCHEMA ventes;
\dn
SET search_path TO ventes, public;     -- ordre de recherche des noms de tables
SHOW search_path;
RESET search_path;                     -- retour à la valeur par défaut (la suite utilise le schéma public)
-- DROP SCHEMA ventes CASCADE;         -- supprime le schéma et son contenu

Types de données courants

Type	Usage
`integer`, `bigint`	Entiers
`numeric(10,2)`	Nombres exacts (montants)
`text`, `varchar(n)`	Texte (`text` sans limite, recommandé par défaut)
`boolean`	Vrai ou faux
`date`, `timestamptz`	Date, date et heure avec fuseau horaire (recommandé pour les horodatages)
`uuid`	Identifiant universel
`jsonb`	Document JSON indexable
`integer[]`, `text[]`	Tableaux
`ENUM`	Une valeur parmi une liste (type à définir)

Créer des tables

CREATE TYPE statut_commande AS ENUM ('nouvelle', 'payee', 'expediee', 'annulee');

CREATE TABLE clients (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nom      text NOT NULL,
  email    text NOT NULL UNIQUE,
  ville    text,
  cree_le  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE commandes (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  client_id  integer NOT NULL REFERENCES clients (id) ON DELETE CASCADE,
  montant    numeric(10,2) NOT NULL CHECK (montant >= 0),
  statut     statut_commande NOT NULL DEFAULT 'nouvelle',
  passee_le  timestamptz NOT NULL DEFAULT now()
);

GENERATED ALWAYS AS IDENTITY : numérotation automatique (forme moderne de serial).
REFERENCES … ON DELETE CASCADE : clé étrangère ; les commandes d’un client supprimé sont supprimées.
CHECK : contrainte vérifiée à chaque insertion et modification.
timestamptz : stocke un instant universel, affiché dans le fuseau de la session.

Inspecter le schéma

\dt
\d clients
\d+ commandes
\di
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'clients'
ORDER BY ordinal_position;
SELECT pg_size_pretty(pg_total_relation_size('commandes'));

Modifier une table

ALTER TABLE clients ADD COLUMN telephone text;
ALTER TABLE clients ALTER COLUMN telephone TYPE varchar(30);
ALTER TABLE clients RENAME COLUMN telephone TO tel;
ALTER TABLE clients ALTER COLUMN ville SET DEFAULT 'inconnue';
ALTER TABLE clients ALTER COLUMN ville DROP DEFAULT;
ALTER TABLE clients DROP COLUMN tel;
ALTER TABLE commandes ADD CONSTRAINT ck_montant_max CHECK (montant < 100000);
ALTER TABLE commandes DROP CONSTRAINT ck_montant_max;
ALTER TABLE clients RENAME TO clients_archive;
ALTER TABLE clients_archive RENAME TO clients;
COMMENT ON TABLE clients IS 'Clients de la boutique';

Vider et supprimer

TRUNCATE TABLE commandes;                 -- vide la table
TRUNCATE TABLE clients RESTART IDENTITY CASCADE;   -- vide aussi les tables liées et remet la numérotation à 1
DROP TABLE IF EXISTS essai;

Bonnes pratiques

Une clé primaire sur chaque table.
numeric pour les montants, jamais real ni double precision.
timestamptz plutôt que timestamp pour les dates et heures.
text plutôt que varchar(n), sauf contrainte métier de longueur.
Des noms en minuscules sans espaces ni accents (PostgreSQL met en minuscules les noms non entre guillemets).

Pour aller plus loin

Manuel : Data Definition
Suite : Requêtes SQL