Une expression de table commune (CTE) est une sous-requête nommée dans une clause WITH, placée avant l'instruction principale : WITH name AS (SELECT ...) SELECT ... FROM name. La requête principale lit la CTE comme une table, donc les sous-requêtes imbriquées deviennent des étapes qui se lisent de haut en bas.
La CTE totals additionne les commandes de chaque client, et la requête principale garde les clients au-dessus de 100 : cy avec 300 et ana avec 180. Une CTE n'existe que pour l'instruction à laquelle elle appartient.
Enchaîner plusieurs CTE
Séparez les CTE par des virgules. Chacune peut lire les CTE définies avant elle.
average lit totals, et la requête principale lit les deux. Les totaux par client valent 180, 70 et 300, et leur moyenne est d'environ 183, donc seul cy est au-dessus.
Parcourir un arbre avec WITH RECURSIVE
WITH RECURSIVE permet à une CTE de se référer à elle-même. Elle a deux parties reliées par UNION ALL :
- la requête d'ancrage, qui renvoie les lignes de départ ;
- la requête récursive, qui joint la CTE à la table pour trouver le niveau suivant.
PostgreSQL exécute la partie récursive sur les lignes produites par l'étape précédente, et s'arrête quand une étape ne renvoie aucune ligne.
Chaque étape trouve les personnes qui dépendent des lignes de l'étape précédente. Mia arrive à la profondeur 3 avec le chemin Rosa > Omar > Ivo > Mia. Pour lister toutes les personnes sous un seul manager au lieu de l'arbre entier, remplacez le WHERE de l'ancrage par id = 2.
Pour remonter, d'un employé jusqu'au sommet, inversez la condition de jointure en s.id = c.manager_id.
Arrêter une CTE récursive infinie
Une CTE récursive ne se termine que lorsqu'une étape ne renvoie rien. Avec des données qui bouclent (A dépend de B, B dépend de A), elle ne se termine jamais. Ajoutez une limite de profondeur comme WHERE c.depth < 20, ou utilisez la clause CYCLE (PostgreSQL 14+), qui marque la première ligne répétée et arrête de la suivre :
WITH RECURSIVE chain AS (
SELECT id, manager_id FROM staff WHERE id = 5
UNION ALL
SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;
Générer des lignes avec une CTE récursive
La partie récursive n'a pas besoin de table. Cet exemple compte de 1 à 5 en réinjectant la valeur de chaque ligne :
Pour de simples séries de nombres ou de dates, generate_series est plus court.
Déplacer des lignes avec une CTE qui modifie des données
Une CTE peut être un INSERT, un UPDATE ou un DELETE avec une clause RETURNING, et l'instruction principale lit les lignes renvoyées. Cet exemple déplace les anciennes commandes vers une table d'archive en une seule instruction :
Les commandes 1 et 3 sont supprimées et insérées dans l'archive ensemble, donc un échec laisse les deux tables telles qu'elles étaient. Toutes les parties de l'instruction voient le même instantané : la requête principale ne voit pas l'effet du DELETE sur orders, seulement ce que RETURNING lui transmet (voir RETURNING).
Contrôler l'intégration avec MATERIALIZED
Depuis PostgreSQL 12, une CTE sans effet de bord que la requête principale référence une seule fois est intégrée. Le planificateur l'optimise avec la requête externe, donc un WHERE écrit en dehors de la CTE peut quand même utiliser un index. Une CTE référencée deux fois ou plus est calculée une seule fois et son résultat est réutilisé. Vous pouvez imposer l'un ou l'autre comportement :
WITH recent AS MATERIALIZED (
SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;
WITH recent AS NOT MATERIALIZED (...)
MATERIALIZED calcule la CTE une fois, à part, et garde le WHERE externe en dehors. Utilisez-le quand le plan intégré se révèle plus lent. NOT MATERIALIZED demande l'intégration même quand la CTE est référencée plusieurs fois. Les CTE récursives et celles qui modifient des données sont toujours calculées à part.
Questions fréquentes
Une CTE est-elle plus rapide qu'une sous-requête dans PostgreSQL ?
En général, aucune n'est plus rapide. Depuis PostgreSQL 12, une CTE référencée une seule fois et sans effet de bord est intégrée à la requête principale, donc elle obtient le même plan que la sous-requête équivalente. Avant la version 12, chaque CTE était calculée à part, ce qui pouvait empêcher l'utilisation d'un index.
Peut-on utiliser une CTE dans un UPDATE ou un DELETE ?
Oui. WITH peut précéder INSERT, UPDATE, DELETE et MERGE, et la CTE elle-même peut être un INSERT, un UPDATE ou un DELETE avec RETURNING, dont l'instruction principale lit les lignes.
Comment arrêter une boucle infinie dans une CTE récursive ?
Donnez à la partie récursive une condition qui finit par ne plus renvoyer de lignes, comme une limite de profondeur (WHERE depth < 10). Pour des données qui peuvent boucler sur elles-mêmes, la clause CYCLE (PostgreSQL 14+) marque une ligne répétée et s'arrête là.
Peut-on utiliser une CTE dans plusieurs requêtes ?
Non. Une CTE n'existe que pour l'instruction dans laquelle elle est écrite. Pour réutiliser une requête dans plusieurs instructions, créez une vue ou une table temporaire.