PostgreSQL : requêtes SQL

Requêtes sur les tables clients et commandes de la page Bases et tables.

Insérer

INSERT INTO clients (nom, email, ville) VALUES
  ('Alice Martin', 'alice@exemple.com', 'Lyon'),
  ('Bruno Durand', 'bruno@exemple.com', 'Paris'),
  ('Chloé Petit',  'chloe@exemple.com', 'Lyon'),
  ('David Moreau', 'david@exemple.com', NULL);

INSERT INTO commandes (client_id, montant, statut) VALUES
  (1, 49.90, 'payee'),
  (1, 120.00, 'nouvelle'),
  (2, 15.50, 'payee'),
  (3, 300.00, 'expediee');

INSERT INTO clients (nom, email) VALUES ('Emma Roux', 'emma@exemple.com') RETURNING id, cree_le;

RETURNING renvoie les valeurs générées (identifiant, date) sans second SELECT.

Lire des données

SELECT * FROM clients;
SELECT nom, ville FROM clients WHERE ville = 'Lyon';
SELECT nom FROM clients WHERE ville IS NULL;
SELECT nom FROM clients WHERE nom ILIKE 'a%';          -- insensible à la casse
SELECT nom FROM clients WHERE ville IN ('Lyon', 'Paris');
SELECT id, montant FROM commandes WHERE montant BETWEEN 20 AND 200;
SELECT DISTINCT ville FROM clients;
SELECT nom FROM clients ORDER BY nom DESC LIMIT 2;
SELECT nom FROM clients ORDER BY nom LIMIT 2 OFFSET 2;  -- pagination
SELECT COUNT(*) AS nb FROM clients;

Modifier et supprimer

UPDATE clients SET ville = 'Marseille' WHERE email = 'david@exemple.com' RETURNING id, ville;
UPDATE commandes SET statut = 'payee' WHERE id = 2;
DELETE FROM commandes WHERE statut = 'annulee';

Jointures

SELECT c.nom, o.montant, o.statut
FROM clients c
JOIN commandes o ON o.client_id = c.id
ORDER BY c.nom;

SELECT c.nom, COUNT(o.id) AS nb_commandes
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
GROUP BY c.id, c.nom;

Agrégats

SELECT statut, COUNT(*) AS nb, SUM(montant) AS total, ROUND(AVG(montant), 2) AS moyenne
FROM commandes
GROUP BY statut
ORDER BY total DESC;

SELECT client_id, SUM(montant) AS total
FROM commandes
GROUP BY client_id
HAVING SUM(montant) > 100;

SELECT COUNT(*) FILTER (WHERE statut = 'payee') AS payees,
       COUNT(*) FILTER (WHERE statut = 'nouvelle') AS nouvelles
FROM commandes;

Expressions de table, sous-requêtes et fenêtres

WITH totaux AS (
  SELECT client_id, SUM(montant) AS total FROM commandes GROUP BY client_id
)
SELECT c.nom, t.total
FROM totaux t
JOIN clients c ON c.id = t.client_id
ORDER BY t.total DESC;

SELECT id, client_id, montant,
       SUM(montant) OVER (PARTITION BY client_id ORDER BY id) AS cumul_client,
       RANK() OVER (ORDER BY montant DESC) AS rang
FROM commandes;

-- Dernière commande de chaque client
SELECT DISTINCT ON (client_id) client_id, id, montant
FROM commandes
ORDER BY client_id, passee_le DESC;

Insérer ou mettre à jour (upsert)

INSERT INTO clients (nom, email, ville) VALUES ('Alice Martin', 'alice@exemple.com', 'Nice')
ON CONFLICT (email) DO UPDATE SET ville = EXCLUDED.ville;

INSERT INTO clients (nom, email) VALUES ('Alice Martin', 'alice@exemple.com')
ON CONFLICT (email) DO NOTHING;

Dates, chaînes et séries

SELECT now(), current_date, to_char(now(), 'DD/MM/YYYY HH24:MI');
SELECT * FROM commandes WHERE passee_le >= now() - interval '7 days';
SELECT upper(nom), nom || ' <' || email || '>' FROM clients;
SELECT coalesce(ville, 'inconnue') FROM clients;
SELECT generate_series(1, 5) AS n;                                     -- suite de nombres
SELECT d::date FROM generate_series('2026-10-01', '2026-10-05', interval '1 day') AS d;

JSONB et tableaux

CREATE TABLE evenements (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, donnees jsonb NOT NULL);
INSERT INTO evenements (donnees) VALUES ('{"type": "connexion", "utilisateur": "alice", "tags": ["web", "mobile"]}');

SELECT donnees->>'utilisateur' AS utilisateur FROM evenements;          -- ->> : texte
SELECT donnees->'tags'->>0 AS premier_tag FROM evenements;
SELECT * FROM evenements WHERE donnees @> '{"type": "connexion"}';      -- @> : contient
SELECT * FROM evenements WHERE donnees->'tags' ? 'web';                 -- ? : clé ou valeur présente

SELECT ARRAY['a', 'b', 'c'] AS liste, 'b' = ANY (ARRAY['a', 'b', 'c']) AS present;

Transactions

BEGIN;
UPDATE commandes SET statut = 'payee' WHERE id = 2;
SAVEPOINT avant_insertion;
INSERT INTO commandes (client_id, montant) VALUES (2, 10.00);
ROLLBACK TO avant_insertion;       -- annule seulement depuis le point de sauvegarde
COMMIT;                            -- valide le reste
-- ROLLBACK;                       -- annule tout depuis BEGIN

En cas d’erreur dans une transaction, PostgreSQL refuse les instructions suivantes jusqu’à ROLLBACK.

Importer et exporter du CSV : `\copy`

\copy clients (nom, email, ville) FROM 'clients.csv' WITH (FORMAT csv, HEADER true)
\copy (SELECT * FROM commandes) TO 'commandes.csv' WITH (FORMAT csv, HEADER true)

\copy lit et écrit les fichiers côté client (la machine où tourne psql). L’instruction SQL COPY, elle, utilise des fichiers du serveur et demande des droits d’administration.

Expliquer une requête

EXPLAIN SELECT * FROM commandes WHERE statut = 'payee';

Voir Index et performances.

Pour aller plus loin

Manuel : Queries

Dans la même rubrique