Commandes psql
Les méta-commandes s'exécutent dans psql (le client terminal de PostgreSQL), pas sur le serveur. Elles commencent par une barre oblique inverse et ne nécessitent pas de point-virgule.
| Ce qu'elle fait | Commande |
|---|
| Lister les bases de données | \l |
| Se connecter à une autre base | \c shop |
| Lister les tables | \dt |
| Lister les tables de tous les schémas | \dt *.* |
| Décrire une table (colonnes, index, clés) | \d users |
| Décrire avec le stockage et les commentaires | \d+ users |
| Lister les schémas | \dn |
| Lister les vues | \dv |
| Lister les index | \di |
| Lister les fonctions | \df |
| Lister les rôles (utilisateurs) | \du |
| Afficher la connexion actuelle | \conninfo |
| Exécuter du SQL depuis un fichier | \i setup.sql |
| Activer ou désactiver le chronométrage des requêtes | \timing |
| Affichage étendu (une colonne par ligne) | \x |
| Modifier la dernière requête dans votre éditeur | \e |
| Aide sur la syntaxe SQL | \h CREATE TABLE |
| Lister toutes les méta-commandes | \? |
| Quitter psql | \q |
Se connecter depuis le terminal
| Ce qu'elle fait | Commande |
|---|
| Se connecter à une base avec un utilisateur | psql -U postgres -d shop |
| Se connecter à un hôte et un port distants | psql -h db.example.com -p 5432 -U app -d shop |
| Se connecter avec une URL de connexion | psql postgresql://app:secret@localhost:5432/shop |
| Exécuter une requête et quitter | psql -d shop -c "SELECT count(*) FROM users;" |
| Exécuter un fichier SQL | psql -d shop -f setup.sql |
| Créer une base depuis le shell | createdb shop |
| Supprimer une base depuis le shell | dropdb shop |
Bases de données et schémas
| Ce qu'elle fait | SQL |
|---|
| Créer une base de données | CREATE DATABASE shop; |
| Supprimer une base de données | DROP DATABASE shop; |
| Afficher la base de données actuelle | SELECT current_database(); |
| Créer un schéma | CREATE SCHEMA sales; |
| Afficher l'ordre de recherche des schémas | SHOW search_path; |
| Chercher d'abord dans un schéma | SET search_path TO sales, public; |
| Afficher la version du serveur | SELECT version(); |
Tables et types de données
| Ce qu'elle fait | SQL |
|---|
| Créer une table avec une clé d'identité | CREATE TABLE products (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, price numeric(10,2) CHECK (price >= 0), created_at timestamptz DEFAULT now()); |
| Ajouter une colonne | ALTER TABLE users ADD COLUMN phone text; |
| Renommer une colonne | ALTER TABLE users RENAME COLUMN country TO country_code; |
| Changer le type d'une colonne | ALTER TABLE users ALTER COLUMN age TYPE smallint; |
| Définir une valeur par défaut | ALTER TABLE users ALTER COLUMN active SET DEFAULT false; |
| Supprimer une colonne | ALTER TABLE users DROP COLUMN tags; |
| Vider une table et réinitialiser son identité | TRUNCATE orders, users RESTART IDENTITY; |
| Supprimer une table si elle existe | DROP TABLE IF EXISTS products; |
| Décimal exact pour les montants | numeric(10,2) |
| Date et heure avec fuseau horaire | timestamptz |
| UUID aléatoire | SELECT gen_random_uuid(); |
| Type énuméré | CREATE TYPE mood AS ENUM ('happy', 'sad'); |
Contraintes et clés
| Ce qu'elle fait | SQL |
|---|
| Ajouter une clé primaire | ALTER TABLE logs ADD PRIMARY KEY (id); |
| Clé étrangère qui supprime les lignes enfants | CREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE); |
| Ajouter une contrainte CHECK | ALTER TABLE orders ADD CONSTRAINT amount_positive CHECK (amount > 0); |
| Unicité où les NULL comptent comme égaux (15+) | CREATE TABLE emails (address text UNIQUE NULLS NOT DISTINCT); |
| Rendre une colonne obligatoire | ALTER TABLE users ALTER COLUMN country SET NOT NULL; |
| Supprimer une contrainte | ALTER TABLE users DROP CONSTRAINT users_email_key; |
Requêtes (spécificités PostgreSQL)
SELECT, WHERE et JOIN standard sont dans l'aide-mémoire SQL ; voici les formes propres à PostgreSQL.
| Ce qu'elle fait | SQL |
|---|
| Correspondance insensible à la casse | SELECT * FROM users WHERE name ILIKE 'a%'; |
| Correspondance par expression régulière | SELECT * FROM users WHERE email ~ '@example\.com$'; |
| Première ligne de chaque groupe | SELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC; |
| Limite et décalage | SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; |
| Limite de lignes standard | SELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY; |
| Conversion avec :: | SELECT '42'::integer + 1; |
| Valeur de repli pour NULL | SELECT name, COALESCE(email, 'none') FROM users; |
| Comparer en traitant les NULL comme égaux | SELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com'; |
| Générer une série de nombres | SELECT * FROM generate_series(1, 5); |
| Agréger en une chaîne | SELECT string_agg(name, ', ' ORDER BY name) FROM users; |
| Agrégat conditionnel | SELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders; |
Insérer, mettre à jour, supprimer et upsert
| Ce qu'elle fait | SQL |
|---|
| Insérer et récupérer le nouvel id | INSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id; |
| Insérer plusieurs lignes | INSERT INTO users (name) VALUES ('Eli'), ('Femi'); |
| Upsert (insérer ou mettre à jour) | INSERT INTO users (name, email) VALUES ('Asha K', 'asha@example.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; |
| Insérer en ignorant les doublons | INSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING; |
| Mettre à jour et voir les lignes modifiées | UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary; |
| Mettre à jour depuis une autre table | UPDATE orders SET status = 'vip' FROM users WHERE orders.user_id = users.id AND users.country = 'IN'; |
| Supprimer et voir ce qui a été supprimé | DELETE FROM orders WHERE status = 'refunded' RETURNING id; |
| Supprimer à l'aide d'une autre table | DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false; |
| Copier des lignes dans une nouvelle table | CREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid'; |
JSONB
| Ce qu'elle fait | SQL |
|---|
| Obtenir un champ en JSON | SELECT profile -> 'plan' FROM users; |
| Obtenir un champ en texte | SELECT profile ->> 'plan' FROM users; |
| Obtenir un champ imbriqué en texte | SELECT profile #>> '{address,city}' FROM users; |
| Lignes dont le JSON contient une valeur | SELECT * FROM users WHERE profile @> '{"plan": "pro"}'; |
| Lignes qui ont une clé | SELECT * FROM users WHERE profile ? 'city'; |
| Définir une clé | UPDATE users SET profile = jsonb_set(profile, '{plan}', '"team"') WHERE id = 2; |
| Fusionner des objets | UPDATE users SET profile = profile || '{"beta": true}'; |
| Supprimer une clé | UPDATE users SET profile = profile - 'city'; |
| Construire du JSON à partir de colonnes | SELECT jsonb_build_object('id', id, 'name', name) FROM users; |
| Lignes sous forme de tableau JSON | SELECT jsonb_agg(users) FROM users; |
| Index pour les recherches @> et ? | CREATE INDEX users_profile_idx ON users USING gin (profile); |
Tableaux
| Ce qu'elle fait | SQL |
|---|
| Littéral de tableau | SELECT ARRAY[1, 2, 3]; |
| Lignes où le tableau contient une valeur | SELECT * FROM users WHERE 'sql' = ANY(tags); |
| Lignes où le tableau contient toutes ces valeurs | SELECT * FROM users WHERE tags @> ARRAY['sql', 'python']; |
| Une ligne par élément | SELECT name, unnest(tags) FROM users; |
| Ajouter un élément à la fin | UPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2; |
| Longueur du tableau | SELECT name, cardinality(tags) FROM users; |
Dates et heures
| Ce qu'elle fait | SQL |
|---|
| Maintenant, avec fuseau horaire | SELECT now(); |
| Aujourd'hui | SELECT current_date; |
| Ajouter un intervalle | SELECT current_date + INTERVAL '7 days'; |
| Lignes des 30 derniers jours | SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'; |
| Tronquer au mois | SELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1; |
| Extraire une partie | SELECT EXTRACT(YEAR FROM hired_on) FROM employees; |
| Différence entre deux dates (jours) | SELECT DATE '2026-12-25' - DATE '2026-11-17'; |
| Âge entre deux dates | SELECT name, age(current_date, hired_on) FROM employees; |
| Formater une date en texte | SELECT to_char(hired_on, 'DD Mon YYYY') FROM employees; |
| Convertir un texte en date | SELECT to_date('17/11/2026', 'DD/MM/YYYY'); |
| Convertir vers un fuseau horaire | SELECT now() AT TIME ZONE 'Asia/Kolkata'; |
Fonctions de texte
| Ce qu'elle fait | SQL |
|---|
| Concaténer (un NULL donne NULL) | SELECT name || ' <' || email || '>' FROM users; |
| Concaténer en ignorant les NULL | SELECT concat_ws(' ', name, email) FROM users; |
| Majuscules et minuscules | SELECT upper(name), lower(name) FROM users; |
| Longueur | SELECT name, length(name) FROM users; |
| Partie d'une chaîne | SELECT substring(name FROM 1 FOR 2) FROM users; |
| Remplacer | SELECT replace(email, '@example.com', '') FROM users; |
| Découper et prendre une partie | SELECT split_part(email, '@', 2) FROM users; |
| Remplacement par expression régulière | SELECT regexp_replace('a1b22c', '[0-9]+', '', 'g'); |
| Supprimer les espaces | SELECT trim(' hi '); |
CTE et fonctions de fenêtrage
| Ce qu'elle fait | SQL |
|---|
| Nommer une sous-requête avec WITH | WITH paid AS (SELECT user_id, sum(amount) AS total FROM orders WHERE status = 'paid' GROUP BY user_id) SELECT * FROM paid WHERE total > 50; |
| CTE récursive (compter jusqu'à 5) | WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n; |
| Numéroter les lignes par groupe | SELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; |
| Classement avec ex aequo | SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees; |
| Total cumulé | SELECT id, amount, sum(amount) OVER (ORDER BY id) FROM orders; |
| Valeur de la ligne précédente | SELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees; |
| Joindre chaque ligne à sa propre sous-requête | SELECT u.name, o.amount FROM users u CROSS JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) o; |
Index
| Ce qu'elle fait | SQL |
|---|
| Index B-tree (par défaut) | CREATE INDEX orders_user_idx ON orders (user_id); |
| Index unique | CREATE UNIQUE INDEX employees_name_idx ON employees (name); |
| Index multicolonne | CREATE INDEX orders_user_created_idx ON orders (user_id, created_at); |
| Index partiel | CREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid'; |
| Index sur expression | CREATE INDEX users_lower_email_idx ON users (lower(email)); |
| Index couvrant (parcours d'index seul) | CREATE INDEX orders_user_cover_idx ON orders (user_id) INCLUDE (amount); |
| Créer sans bloquer les écritures | CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status); |
| Supprimer un index | DROP INDEX IF EXISTS orders_user_idx; |
| Voir le plan | EXPLAIN SELECT * FROM orders WHERE user_id = 1; |
| Voir le plan avec les temps réels | EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1; |
Transactions et verrous
| Ce qu'elle fait | SQL |
|---|
| Démarrer, puis valider ou annuler | BEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK; |
| Annuler une partie d'une transaction | BEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT; |
| Isolation plus stricte | BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT; |
| Verrouiller les lignes lues | BEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT; |
| File de tâches : ignorer les lignes verrouillées | BEGIN; SELECT id FROM orders WHERE status = 'paid' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED; COMMIT; |
Utilisateurs, rôles et privilèges
| Ce qu'elle fait | SQL |
|---|
| Créer un rôle de connexion | CREATE ROLE app LOGIN PASSWORD 'change-me'; |
| Accès en lecture seule à une table | GRANT SELECT ON users TO app; |
| Accès en lecture à toutes les tables d'un schéma | GRANT SELECT ON ALL TABLES IN SCHEMA public TO app; |
| Retirer un privilège | REVOKE SELECT ON users FROM app; |
| Changer un mot de passe | ALTER ROLE app PASSWORD 'new-secret'; |
| Afficher l'utilisateur actuel | SELECT current_user; |
Maintenance et supervision
| Ce qu'elle fait | SQL |
|---|
| Requêtes en cours | SELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle'; |
| Annuler une requête | SELECT pg_cancel_backend(12345); |
| Mettre fin à une connexion | SELECT pg_terminate_backend(12345); |
| Taille d'une table avec ses index | SELECT pg_size_pretty(pg_total_relation_size('orders')); |
| Taille de la base de données | SELECT pg_size_pretty(pg_database_size(current_database())); |
| Mettre à jour les statistiques du planificateur | ANALYZE orders; |
| Récupérer les lignes mortes | VACUUM orders; |
| Réécrire une table à sa taille minimale (la verrouille) | VACUUM FULL orders; |
| Afficher un paramètre | SHOW work_mem; |
| Modifier un paramètre pour cette session | SET work_mem = '64MB'; |
Sauvegarde et restauration
Commandes shell des outils clients de PostgreSQL.
| Ce qu'elle fait | Commande |
|---|
| Exporter une base dans un fichier SQL | pg_dump -U postgres shop > shop.sql |
| Exporter au format personnalisé | pg_dump -Fc -U postgres shop > shop.dump |
| Exporter une seule table | pg_dump -t orders shop > orders.sql |
| Restaurer un export SQL | psql -d shop -f shop.sql |
| Restaurer un export au format personnalisé | pg_restore -d shop shop.dump |
| Exporter toutes les bases et tous les rôles | pg_dumpall > cluster.sql |
| Exporter une requête en CSV (depuis psql) | \copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER |
| Importer un CSV (depuis psql) | \copy orders (user_id, amount, status) FROM 'orders.csv' CSV HEADER |
Chaque commande psql et chaque instruction PostgreSQL dont vous avez besoin, dans des tableaux à copier-coller : d'abord la connexion et les commandes \d, puis les tables, les upserts, JSONB, les dates, les fonctions de fenêtrage, les index, les rôles, les sauvegardes et la maintenance.
Le SQL présenté ici suit la syntaxe de PostgreSQL 17, et l'essentiel fonctionne aussi dans les versions antérieures. Les instructions SQL ont été vérifiées sur PostgreSQL 17 ; pour en essayer une, collez-la dans le playground PostgreSQL, qui contient des tables d'exemple.