Cours PostgreSQL Complet 🐘

— Se connecter (CLI)
psql -U postgres
psql -U postgres -d mon_app — Directement dans une BDD

— Commandes psql
\l — Lister les bases
\c mon_app — Se connecter à une base
\dt — Lister les tables
\d users — Structure d'une table
\di — Lister les index
\q — Quitter

— Clients graphiques
— • DBeaver (gratuit, multi-BDD)
— • pgAdmin (officiel PostgreSQL)
— • TablePlus / DataGrip

— Créer une table avec des types PostgreSQL
CREATE TABLE users (
id SERIAL PRIMARY KEY, — Auto-incrémenté (ou BIGSERIAL)
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
role VARCHAR(20) NOT NULL DEFAULT 'user'
CHECK (role IN ('user', 'admin', 'editor')),
bio TEXT,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
metadata JSONB DEFAULT ''::jsonb,
tags TEXT[] DEFAULT '', — Array de textes !
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

— Table avec clé étrangère
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL UNIQUE,
content TEXT,
user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
published BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

— Trigger pour updated_at automatique
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
price NUMERIC(10,2) NOT NULL CHECK (price > 0),
tags TEXT[] DEFAULT '',
attributes JSONB DEFAULT ''::jsonb
);

— Insertion multiple
INSERT INTO users (name, email) VALUES
('Bob', 'bob@mail.com'),
('Claire', 'claire@mail.com')
RETURNING *;

— UPSERT (INSERT ou UPDATE si conflit)
INSERT INTO users (email, name) VALUES ('alice@mail.com', 'Alice M.')
ON CONFLICT (email) DO UPDATE SET
name = EXCLUDED.name,
updated_at = NOW();

— ON CONFLICT DO NOTHING (ignorer les doublons)
INSERT INTO users (email, name) VALUES ('alice@mail.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

— UPDATE avec RETURNING
UPDATE users SET role = 'editor' WHERE id = 2
RETURNING *;

— DELETE avec RETURNING
DELETE FROM users WHERE id = 3
RETURNING id, name;

— CTE (Common Table Expression)
WITH top_authors AS (
SELECT user_id, COUNT(*) AS nb
FROM posts WHERE published = TRUE
GROUP BY user_id
ORDER BY nb DESC LIMIT 10
)
SELECT u.name, t.nb
FROM top_authors t
JOIN users u ON u.id = t.user_id;

— CTE récursif (hiérarchies : catégories, commentaires imbriqués)
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;

— WINDOW FUNCTIONS — calculs sur un « fenêtre » de lignes
SELECT
name, role, salary,
RANK() OVER (ORDER BY salary DESC) AS rang,
AVG(salary) OVER (PARTITION BY role) AS avg_role,
salary – AVG(salary) OVER (PARTITION BY role) AS ecart_moyenne,
LAG(salary) OVER (ORDER BY salary) AS salaire_precedent
FROM employees;

— FILTER — agréger conditionnellement (très pratique !)
SELECT
COUNT(*) AS total,
COUNT(*) FILTER (WHERE published = TRUE) AS published,
COUNT(*) FILTER (WHERE published = FALSE) AS drafts
FROM posts;

— string_agg — concaténer en une string
SELECT user_id, string_agg(tag, ', ' ORDER BY tag) AS all_tags
FROM post_tags
GROUP BY user_id;

— array_agg — agréger en array
SELECT user_id, array_agg(title ORDER BY created_at DESC) AS post_titles
FROM posts
GROUP BY user_id;

— Casting (::)
SELECT '42'::INTEGER; — String → Integer
SELECT NOW()::DATE; — Timestamp → Date
SELECT price::NUMERIC(10,2); — Arrondir un float

— Lire des champs JSONB
SELECT name,
attributes->>'brand' AS brand, — ->> retourne du texte
attributes->'ram' AS ram, — -> retourne du JSONB
attributes->'colors'->>0 AS first_color — Accès array
FROM products;

— Filtrer sur du JSONB
SELECT * FROM products WHERE attributes->>'brand' = 'Apple';
SELECT * FROM products WHERE (attributes->'ram')::int >= 16;

— Contient (@>)
SELECT * FROM products
WHERE attributes @> ''::jsonb;

— Modifier du JSONB
UPDATE products SET attributes = attributes || ''::jsonb; — Ajouter
UPDATE products SET attributes = attributes – 'warranty'; — Supprimer une clé
UPDATE products SET attributes = jsonb_set(attributes, '', '32'); — Modifier

— Index GIN sur JSONB (accélère @>, ?, ?|, ?&)
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

— Filtrer
SELECT * FROM users WHERE 'python' = ANY(tags); — Contient 'python'
SELECT * FROM users WHERE tags @> ARRAY['python', 'django']; — Contient les deux

— Ajouter/Retirer
UPDATE users SET tags = array_append(tags, 'react') WHERE id = 1;
UPDATE users SET tags = array_remove(tags, 'django') WHERE id = 1;

— Index GIN sur arrays
CREATE INDEX idx_users_tags ON users USING GIN (tags);

— Avec ranking (pertinence)
SELECT title,
ts_rank(to_tsvector('french', title || ' ' || content),
to_tsquery('french', 'postgresql')) AS rank
FROM posts
WHERE to_tsvector('french', title || ' ' || content)
@@ to_tsquery('french', 'postgresql')
ORDER BY rank DESC;

— Colonne tsvector pré-calculée (performance)
ALTER TABLE posts ADD COLUMN search_vector TSVECTOR;

UPDATE posts SET search_vector =
setweight(to_tsvector('french', COALESCE(title,  »)), 'A') ||
setweight(to_tsvector('french', COALESCE(content,  »)), 'B');

— Index GIN sur la colonne tsvector
CREATE INDEX idx_posts_search ON posts USING GIN (search_vector);

— Recherche rapide
SELECT title FROM posts
WHERE search_vector @@ to_tsquery('french', 'postgresql')
ORDER BY ts_rank(search_vector, to_tsquery('french', 'postgresql')) DESC;

— Index partiel — seulement certaines lignes
CREATE INDEX idx_posts_published ON posts(created_at)
WHERE published = TRUE;

— GIN — JSONB, arrays, full-text
CREATE INDEX idx_attrs ON products USING GIN (attributes);

— GiST — géographique, ranges
CREATE INDEX idx_location ON places USING GIST (coordinates);

— Index d'expression
CREATE INDEX idx_email_lower ON users(LOWER(email));

— EXPLAIN ANALYZE — analyser les performances
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@mail.com';
— → Seq Scan (full scan) = lent, pas d'index
— → Index Scan = rapide, utilise l'index

— Savepoints (rollback partiel)
BEGIN;
INSERT INTO orders (…) VALUES (…);
SAVEPOINT before_payment;
UPDATE accounts SET balance = balance – 100 WHERE user_id = 1;
— Si erreur de paiement :
ROLLBACK TO before_payment; — Annule seulement le paiement
COMMIT; — La commande est quand même créée

— SELECT FOR UPDATE — verrouiller des lignes
BEGIN;
SELECT * FROM products WHERE id = 42 FOR UPDATE;
— Personne d'autre ne peut modifier ce produit jusqu'au COMMIT
UPDATE products SET stock = stock – 1 WHERE id = 42;
COMMIT;

— Permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

— Lecture seule
CREATE ROLE readonly WITH LOGIN PASSWORD 'readonly_pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

— Restaurer
psql -U postgres mon_app < backup.sql
pg_restore -U postgres -d mon_app backup.dump

— Extensions populaires
CREATE EXTENSION IF NOT EXISTS « pgcrypto »; — UUID, cryptographie
CREATE EXTENSION IF NOT EXISTS « pg_trgm »; — Recherche floue (trigrammes)
CREATE EXTENSION IF NOT EXISTS « postgis »; — Données géographiques
CREATE EXTENSION IF NOT EXISTS « pgvector »; — Embeddings IA (RAG)
CREATE EXTENSION IF NOT EXISTS « pg_stat_statements »; — Requêtes lentes

— Recherche floue avec pg_trgm
SELECT name, similarity(name, 'postgre') AS sim
FROM products
WHERE name % 'postgre'
ORDER BY sim DESC;