5 exercices · Solutions masquées
Créez une table products avec un champ JSONB attributes. Insérez 3 produits avec des attributs différents (couleur, taille, poids, etc.). Puis : (a) filtrez par couleur rouge, (b) ajoutez un champ "discount" à tous les produits, (c) créez un index GIN et vérifiez son utilisation.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price NUMERIC(8,2),
attributes JSONB DEFAULT '{}'
);
INSERT INTO products (name, price, attributes) VALUES
('T-shirt rouge', 19.99, '{"color":"red","size":"M","weight":200,"material":"coton"}'),
('Jean bleu', 49.99, '{"color":"blue","size":"L","weight":600,"material":"denim"}'),
('Polo rouge', 29.99, '{"color":"red","size":"S","weight":180,"material":"polo"}');
-- (a) Filtre par couleur
SELECT name, attributes->>'color' AS color
FROM products
WHERE attributes @> '{"color":"red"}';
-- (b) Ajouter champ discount
UPDATE products SET attributes = attributes || '{"discount":0}'::jsonb;
-- (c) Index GIN
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);
EXPLAIN SELECT * FROM products WHERE attributes @> '{"color":"red"}';Créez une table articles avec titre et contenu. Ajoutez une colonne search_vector tsvector, un trigger pour la maintenir automatiquement, et un index GIN. Effectuez une recherche full-text avec ranking.
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT,
search_vector TSVECTOR
);
-- Trigger de mise à jour
CREATE OR REPLACE FUNCTION update_article_fts() RETURNS trigger AS $$
BEGIN
NEW.search_vector := to_tsvector('french',
coalesce(NEW.title, '') || ' ' || coalesce(NEW.body, ''));
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_articles_fts
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION update_article_fts();
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vector);
INSERT INTO articles (title, body) VALUES
('Introduction à PostgreSQL', 'PostgreSQL est une base de données relationnelle open-source très performante.'),
('Les index en PostgreSQL', 'Les index B-Tree et GIN améliorent les performances des requêtes.'),
('Redis et le caching', 'Redis est une base de données clé-valeur en mémoire utilisée pour le cache.');
-- Recherche avec ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('french', 'postgresql & index') query
WHERE search_vector @@ query
ORDER BY rank DESC;À partir d'une table sales(id, seller, region, amount, sale_date), calculez pour chaque vente : (a) le rang du vendeur dans sa région, (b) le total cumulé des ventes de sa région, (c) la moyenne mobile sur 3 mois, (d) la différence avec la vente précédente du même vendeur.
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
seller VARCHAR(50),
region VARCHAR(50),
amount NUMERIC(10,2),
sale_date DATE
);
INSERT INTO sales (seller, region, amount, sale_date) VALUES
('Alice', 'Nord', 1200, '2024-01-15'), ('Bob', 'Nord', 900, '2024-01-20'),
('Alice', 'Nord', 1500, '2024-02-10'), ('Charlie', 'Sud', 800, '2024-01-05'),
('David', 'Sud', 1100, '2024-02-12'), ('Charlie', 'Sud', 950, '2024-03-01');
SELECT
seller, region, amount, sale_date,
RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank,
SUM(amount) OVER (PARTITION BY region ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumul,
AVG(amount) OVER (PARTITION BY seller ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3m,
amount - LAG(amount) OVER (PARTITION BY seller ORDER BY sale_date) AS vs_prev
FROM sales
ORDER BY region, sale_date;Modélisez une hiérarchie d'employés (manager/subordonné). Créez la CTE récursive qui retourne l'arborescence complète depuis un manager racine, avec le niveau de profondeur et le chemin complet (breadcrumb).
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'CEO', NULL), (2, 'CTO', 1), (3, 'CFO', 1),
(4, 'Lead Dev', 2), (5, 'Dev 1', 4), (6, 'Dev 2', 4),
(7, 'Comptable', 3);
WITH RECURSIVE org AS (
-- Racine
SELECT id, name, manager_id, 0 AS depth,
ARRAY[name] AS path, CAST(name AS TEXT) AS breadcrumb
FROM employees WHERE manager_id IS NULL
UNION ALL
-- Récursion
SELECT e.id, e.name, e.manager_id, o.depth + 1,
o.path || e.name,
o.breadcrumb || ' > ' || e.name
FROM employees e
JOIN org o ON e.manager_id = o.id
)
SELECT id, REPEAT(' ', depth) || name AS tree_name,
depth, breadcrumb
FROM org
ORDER BY path;Créez une vue matérialisée mv_monthly_revenue qui calcule le CA mensuel par catégorie. Créez un index UNIQUE dessus. Écrivez une procédure de refresh et programmez-la (simulation via NOTIFY).
-- Tables source
CREATE TABLE categories (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (
id SERIAL PRIMARY KEY, category_id INT REFERENCES categories(id),
amount NUMERIC(10,2), created_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO categories (name) VALUES ('Electronics'),('Clothing'),('Books');
-- Vue matérialisée
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT
DATE_TRUNC('month', o.created_at) AS month,
c.name AS category,
COUNT(o.id) AS nb_orders,
SUM(o.amount) AS revenue
FROM orders o
JOIN categories c ON c.id = o.category_id
GROUP BY 1, 2
WITH DATA;
CREATE UNIQUE INDEX ON mv_monthly_revenue (month, category);
-- Refresh en arrière-plan (CONCURRENTLY grâce à l'index UNIQUE)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_revenue;
-- Trigger pour notifier les changements
CREATE OR REPLACE FUNCTION notify_mv_stale() RETURNS trigger AS $$
BEGIN
PERFORM pg_notify('mv_refresh_needed', TG_TABLE_NAME);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_orders_notify
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH STATEMENT EXECUTE FUNCTION notify_mv_stale();