🐘 BD01 — PostgreSQL Fondamentaux

1. Introduction à PostgreSQL

PostgreSQL est un SGBD relationnel objet open-source, reconnu pour sa conformité aux standards SQL, sa robustesse et ses fonctionnalités avancées (JSONB, full-text search, extensions).

Pourquoi PostgreSQL ?

• Conformité ACID complète (Atomicité, Cohérence, Isolation, Durabilité)
• Support JSON natif avec indexation
• Extensible : PostGIS, pg_trgm, uuid-ossp, etc.
• Réplication native, partitionnement, logical decoding
• Licence open-source permissive (PostgreSQL License)

Installation et connexion

# Ubuntu / Debian
sudo apt install postgresql postgresql-contrib

# Démarrer le service
sudo systemctl start postgresql

# Connexion via psql
psql -U postgres -d mydb

# Créer une base et un utilisateur
CREATE DATABASE myapp;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'secret';
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

2. Types de données essentiels

-- Exemple : types courants
CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  uuid       UUID DEFAULT gen_random_uuid(),
  email      VARCHAR(255) NOT NULL UNIQUE,
  role       TEXT DEFAULT 'user' CHECK (role IN ('admin','user','moderator')),
  metadata   JSONB DEFAULT '{}',
  tags       TEXT[],
  created_at TIMESTAMPTZ DEFAULT NOW()
);

3. DDL — Définition des structures

CREATE TABLE

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  price       NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock       INTEGER DEFAULT 0,
  category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

ALTER TABLE

-- Ajouter une colonne
ALTER TABLE products ADD COLUMN description TEXT;

-- Modifier un type
ALTER TABLE products ALTER COLUMN name TYPE TEXT;

-- Ajouter une contrainte
ALTER TABLE products ADD CONSTRAINT chk_stock CHECK (stock >= 0);

-- Supprimer une colonne
ALTER TABLE products DROP COLUMN description;

-- Renommer
ALTER TABLE products RENAME TO items;

Schémas

CREATE SCHEMA app;
CREATE TABLE app.users (id SERIAL PRIMARY KEY, email TEXT);
SET search_path TO app, public;

4. DML — Manipulation des données

-- INSERT
INSERT INTO products (name, price, stock, category_id)
VALUES ('Laptop', 999.99, 10, 1),
       ('Mouse',   19.99, 50, 2);

-- INSERT ... RETURNING
INSERT INTO users (email) VALUES ('alice@example.com')
RETURNING id, created_at;

-- SELECT avec filtres
SELECT p.name, p.price, c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price BETWEEN 10 AND 1000
ORDER BY p.price DESC
LIMIT 10 OFFSET 0;

-- UPDATE
UPDATE products SET price = price * 1.1 WHERE category_id = 1;

-- DELETE
DELETE FROM products WHERE stock = 0 RETURNING name;

-- UPSERT (INSERT ... ON CONFLICT)
INSERT INTO products (name, price)
VALUES ('Laptop', 1099.99)
ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price;

-- TRUNCATE (plus rapide que DELETE)
TRUNCATE TABLE products RESTART IDENTITY;

5. Jointures

-- INNER JOIN
SELECT o.id, u.email, p.name, oi.quantity
FROM orders o
INNER JOIN users u     ON o.user_id = u.id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p  ON oi.product_id = p.id;

-- LEFT JOIN (tous les utilisateurs, même sans commandes)
SELECT u.email, COUNT(o.id) AS nb_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
ORDER BY nb_orders DESC;

-- Self-join (hiérarchie)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- CROSS JOIN (produit cartésien)
SELECT a.color, b.size FROM colors a CROSS JOIN sizes b;

6. Contraintes d'intégrité

-- Contrainte composite unique
ALTER TABLE order_items
  ADD CONSTRAINT uq_order_product UNIQUE (order_id, product_id);

-- FK avec comportement ON DELETE
ALTER TABLE orders
  ADD CONSTRAINT fk_user FOREIGN KEY (user_id)
  REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE;

7. Index

Les index accélèrent les lectures au prix d'un coût en écriture et en espace.

-- Index B-Tree (défaut, opérateurs =, <, >, BETWEEN, LIKE 'abc%')
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_users_email ON users(email);

-- Index partiel (sous-ensemble de lignes)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- Index composé
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Index unique
CREATE UNIQUE INDEX uq_users_email ON users(lower(email));

-- Supprimer un index
DROP INDEX idx_products_category;

-- Vérifier l'utilisation
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

8. Mise en pratique

-- Top 5 produits les plus vendus
SELECT p.name, SUM(oi.quantity) AS total_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY total_sold DESC
LIMIT 5;

-- Chiffre d'affaires par mois
SELECT DATE_TRUNC('month', o.created_at) AS mois,
       SUM(oi.quantity * oi.unit_price) AS ca
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY mois
ORDER BY mois;