Menu
CoddyTech

Aide-mémoire PostgreSQL

Dernière mise à jour

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 faitCommande
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 faitCommande
Se connecter à une base avec un utilisateurpsql -U postgres -d shop
Se connecter à un hôte et un port distantspsql -h db.example.com -p 5432 -U app -d shop
Se connecter avec une URL de connexionpsql postgresql://app:secret@localhost:5432/shop
Exécuter une requête et quitterpsql -d shop -c "SELECT count(*) FROM users;"
Exécuter un fichier SQLpsql -d shop -f setup.sql
Créer une base depuis le shellcreatedb shop
Supprimer une base depuis le shelldropdb shop

Bases de données et schémas

Ce qu'elle faitSQL
Créer une base de donnéesCREATE DATABASE shop;
Supprimer une base de donnéesDROP DATABASE shop;
Afficher la base de données actuelleSELECT current_database();
Créer un schémaCREATE SCHEMA sales;
Afficher l'ordre de recherche des schémasSHOW search_path;
Chercher d'abord dans un schémaSET search_path TO sales, public;
Afficher la version du serveurSELECT version();

Tables et types de données

Ce qu'elle faitSQL
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 colonneALTER TABLE users ADD COLUMN phone text;
Renommer une colonneALTER TABLE users RENAME COLUMN country TO country_code;
Changer le type d'une colonneALTER TABLE users ALTER COLUMN age TYPE smallint;
Définir une valeur par défautALTER TABLE users ALTER COLUMN active SET DEFAULT false;
Supprimer une colonneALTER TABLE users DROP COLUMN tags;
Vider une table et réinitialiser son identitéTRUNCATE orders, users RESTART IDENTITY;
Supprimer une table si elle existeDROP TABLE IF EXISTS products;
Décimal exact pour les montantsnumeric(10,2)
Date et heure avec fuseau horairetimestamptz
UUID aléatoireSELECT gen_random_uuid();
Type énuméréCREATE TYPE mood AS ENUM ('happy', 'sad');

Contraintes et clés

Ce qu'elle faitSQL
Ajouter une clé primaireALTER TABLE logs ADD PRIMARY KEY (id);
Clé étrangère qui supprime les lignes enfantsCREATE TABLE reviews (id integer PRIMARY KEY, user_id integer REFERENCES users(id) ON DELETE CASCADE);
Ajouter une contrainte CHECKALTER 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 obligatoireALTER TABLE users ALTER COLUMN country SET NOT NULL;
Supprimer une contrainteALTER 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 faitSQL
Correspondance insensible à la casseSELECT * FROM users WHERE name ILIKE 'a%';
Correspondance par expression régulièreSELECT * FROM users WHERE email ~ '@example\.com$';
Première ligne de chaque groupeSELECT DISTINCT ON (user_id) user_id, amount FROM orders ORDER BY user_id, amount DESC;
Limite et décalageSELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
Limite de lignes standardSELECT * FROM users ORDER BY id FETCH FIRST 5 ROWS ONLY;
Conversion avec ::SELECT '42'::integer + 1;
Valeur de repli pour NULLSELECT name, COALESCE(email, 'none') FROM users;
Comparer en traitant les NULL comme égauxSELECT * FROM users WHERE email IS DISTINCT FROM 'asha@example.com';
Générer une série de nombresSELECT * FROM generate_series(1, 5);
Agréger en une chaîneSELECT string_agg(name, ', ' ORDER BY name) FROM users;
Agrégat conditionnelSELECT count(*) FILTER (WHERE status = 'paid') AS paid, count(*) AS total FROM orders;

Insérer, mettre à jour, supprimer et upsert

Ce qu'elle faitSQL
Insérer et récupérer le nouvel idINSERT INTO users (name, email) VALUES ('Dana', 'dana@example.com') RETURNING id;
Insérer plusieurs lignesINSERT 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 doublonsINSERT INTO users (name, email) VALUES ('Ben', 'ben@example.com') ON CONFLICT DO NOTHING;
Mettre à jour et voir les lignes modifiéesUPDATE employees SET salary = salary * 1.1 WHERE department = 'IT' RETURNING name, salary;
Mettre à jour depuis une autre tableUPDATE 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 tableDELETE FROM orders USING users WHERE orders.user_id = users.id AND users.active = false;
Copier des lignes dans une nouvelle tableCREATE TABLE paid_orders AS SELECT * FROM orders WHERE status = 'paid';

JSONB

Ce qu'elle faitSQL
Obtenir un champ en JSONSELECT profile -> 'plan' FROM users;
Obtenir un champ en texteSELECT profile ->> 'plan' FROM users;
Obtenir un champ imbriqué en texteSELECT profile #>> '{address,city}' FROM users;
Lignes dont le JSON contient une valeurSELECT * 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 objetsUPDATE users SET profile = profile || '{"beta": true}';
Supprimer une cléUPDATE users SET profile = profile - 'city';
Construire du JSON à partir de colonnesSELECT jsonb_build_object('id', id, 'name', name) FROM users;
Lignes sous forme de tableau JSONSELECT jsonb_agg(users) FROM users;
Index pour les recherches @> et ?CREATE INDEX users_profile_idx ON users USING gin (profile);

Tableaux

Ce qu'elle faitSQL
Littéral de tableauSELECT ARRAY[1, 2, 3];
Lignes où le tableau contient une valeurSELECT * FROM users WHERE 'sql' = ANY(tags);
Lignes où le tableau contient toutes ces valeursSELECT * FROM users WHERE tags @> ARRAY['sql', 'python'];
Une ligne par élémentSELECT name, unnest(tags) FROM users;
Ajouter un élément à la finUPDATE users SET tags = array_append(tags, 'sql') WHERE id = 2;
Longueur du tableauSELECT name, cardinality(tags) FROM users;

Dates et heures

Ce qu'elle faitSQL
Maintenant, avec fuseau horaireSELECT now();
Aujourd'huiSELECT current_date;
Ajouter un intervalleSELECT current_date + INTERVAL '7 days';
Lignes des 30 derniers joursSELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days';
Tronquer au moisSELECT date_trunc('month', created_at) AS month, sum(amount) FROM orders GROUP BY 1 ORDER BY 1;
Extraire une partieSELECT 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 datesSELECT name, age(current_date, hired_on) FROM employees;
Formater une date en texteSELECT to_char(hired_on, 'DD Mon YYYY') FROM employees;
Convertir un texte en dateSELECT to_date('17/11/2026', 'DD/MM/YYYY');
Convertir vers un fuseau horaireSELECT now() AT TIME ZONE 'Asia/Kolkata';

Fonctions de texte

Ce qu'elle faitSQL
Concaténer (un NULL donne NULL)SELECT name || ' <' || email || '>' FROM users;
Concaténer en ignorant les NULLSELECT concat_ws(' ', name, email) FROM users;
Majuscules et minusculesSELECT upper(name), lower(name) FROM users;
LongueurSELECT name, length(name) FROM users;
Partie d'une chaîneSELECT substring(name FROM 1 FOR 2) FROM users;
RemplacerSELECT replace(email, '@example.com', '') FROM users;
Découper et prendre une partieSELECT split_part(email, '@', 2) FROM users;
Remplacement par expression régulièreSELECT regexp_replace('a1b22c', '[0-9]+', '', 'g');
Supprimer les espacesSELECT trim(' hi ');

CTE et fonctions de fenêtrage

Ce qu'elle faitSQL
Nommer une sous-requête avec WITHWITH 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 groupeSELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
Classement avec ex aequoSELECT 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édenteSELECT name, salary, LAG(salary) OVER (ORDER BY hired_on) FROM employees;
Joindre chaque ligne à sa propre sous-requêteSELECT 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 faitSQL
Index B-tree (par défaut)CREATE INDEX orders_user_idx ON orders (user_id);
Index uniqueCREATE UNIQUE INDEX employees_name_idx ON employees (name);
Index multicolonneCREATE INDEX orders_user_created_idx ON orders (user_id, created_at);
Index partielCREATE INDEX orders_paid_idx ON orders (created_at) WHERE status = 'paid';
Index sur expressionCREATE 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 écrituresCREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
Supprimer un indexDROP INDEX IF EXISTS orders_user_idx;
Voir le planEXPLAIN SELECT * FROM orders WHERE user_id = 1;
Voir le plan avec les temps réelsEXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Transactions et verrous

Ce qu'elle faitSQL
Démarrer, puis valider ou annulerBEGIN; UPDATE employees SET salary = 0 WHERE id = 3; ROLLBACK;
Annuler une partie d'une transactionBEGIN; INSERT INTO users (name) VALUES ('Gil'); SAVEPOINT s1; DELETE FROM users WHERE name = 'Gil'; ROLLBACK TO s1; COMMIT;
Isolation plus stricteBEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) FROM orders; COMMIT;
Verrouiller les lignes luesBEGIN; SELECT * FROM orders WHERE id = 1 FOR UPDATE; COMMIT;
File de tâches : ignorer les lignes verrouilléesBEGIN; 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 faitSQL
Créer un rôle de connexionCREATE ROLE app LOGIN PASSWORD 'change-me';
Accès en lecture seule à une tableGRANT SELECT ON users TO app;
Accès en lecture à toutes les tables d'un schémaGRANT SELECT ON ALL TABLES IN SCHEMA public TO app;
Retirer un privilègeREVOKE SELECT ON users FROM app;
Changer un mot de passeALTER ROLE app PASSWORD 'new-secret';
Afficher l'utilisateur actuelSELECT current_user;

Maintenance et supervision

Ce qu'elle faitSQL
Requêtes en coursSELECT pid, state, query FROM pg_stat_activity WHERE state <> 'idle';
Annuler une requêteSELECT pg_cancel_backend(12345);
Mettre fin à une connexionSELECT pg_terminate_backend(12345);
Taille d'une table avec ses indexSELECT pg_size_pretty(pg_total_relation_size('orders'));
Taille de la base de donnéesSELECT pg_size_pretty(pg_database_size(current_database()));
Mettre à jour les statistiques du planificateurANALYZE orders;
Récupérer les lignes mortesVACUUM orders;
Réécrire une table à sa taille minimale (la verrouille)VACUUM FULL orders;
Afficher un paramètreSHOW work_mem;
Modifier un paramètre pour cette sessionSET work_mem = '64MB';

Sauvegarde et restauration

Commandes shell des outils clients de PostgreSQL.

Ce qu'elle faitCommande
Exporter une base dans un fichier SQLpg_dump -U postgres shop > shop.sql
Exporter au format personnalisépg_dump -Fc -U postgres shop > shop.dump
Exporter une seule tablepg_dump -t orders shop > orders.sql
Restaurer un export SQLpsql -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ôlespg_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.

FAQ de l'aide-mémoire PostgreSQL

Que sont les commandes psql ?
psql est le client terminal de PostgreSQL. En plus du SQL, il comprend des méta-commandes qui commencent par une barre oblique inverse : \l liste les bases de données, \c se connecte à l'une d'elles, \dt liste les tables, \d users décrit une table et \q quitte. Elles s'exécutent dans psql lui-même : elles ne nécessitent donc pas de point-virgule et ne fonctionnent pas dans d'autres clients.
Comment lister toutes les tables dans PostgreSQL ?
Dans psql, tapez \dt (ou \dt *.* pour tous les schémas). En SQL, depuis n'importe quel client : SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
Le SQL de PostgreSQL est-il différent de celui de MySQL ?
Le cœur (SELECT, JOIN, GROUP BY) est le même. PostgreSQL ajoute ILIKE, les conversions ::, RETURNING, DISTINCT ON, les upserts ON CONFLICT, les tableaux et JSONB, et utilise des colonnes d'identité là où MySQL utilise AUTO_INCREMENT.
Quelle est la différence entre JSON et JSONB ?
json stocke le texte tel qu'il a été écrit ; jsonb stocke une forme binaire analysée, supprime les clés en double et les espaces, et prend en charge les index (GIN) et des opérateurs comme @> et ?. Utilisez jsonb sauf si vous avez besoin du texte original exact.
Puis-je m'entraîner à ces requêtes en ligne ?
Oui. Le playground PostgreSQL exécute n'importe quelle requête sur une vraie base PostgreSQL 17 dans votre navigateur, avec des tables d'exemple que vous pouvez modifier. Les instructions qui nécessitent des privilèges serveur, comme CREATE DATABASE ou CREATE ROLE, demandent votre propre PostgreSQL.
Cet aide-mémoire PostgreSQL est-il gratuit ?

Oui. Il est gratuit à lire, copier et imprimer, sans inscription.

Illustration des langages de programmation de Coddy

Apprenez PostgreSQL avec Coddy

Commencer