Menu

CTE en PostgreSQL: consultas WITH y WITH RECURSIVE

Una expresión de tabla común da nombre a una subconsulta en una cláusula WITH para que la consulta principal la lea como una tabla. WITH RECURSIVE permite que se refiera a sí misma para recorrer árboles y grafos.

Esta página incluye editores ejecutables: edita, ejecuta y ve el resultado al instante.

Una expresión de tabla común (CTE) es una subconsulta con nombre en una cláusula WITH antes de la sentencia principal: WITH name AS (SELECT ...) SELECT ... FROM name. La consulta principal lee la CTE como si fuera una tabla, así que las subconsultas anidadas se convierten en pasos que lees de arriba abajo.

La CTE totals suma los pedidos de cada cliente, y la consulta principal se queda con los que superan 100: cy con 300 y ana con 180. Una CTE solo existe en la sentencia a la que pertenece.

Encadenar varias CTE

Separa las CTE con comas. Cada una puede leer las CTE definidas antes que ella.

average lee totals, y la consulta principal lee las dos. Los totales por cliente son 180, 70 y 300 y su media es unos 183, así que solo cy la supera.

Recorrer un árbol con WITH RECURSIVE

WITH RECURSIVE permite que una CTE se refiera a sí misma. Tiene dos partes unidas con UNION ALL:

  • la consulta ancla, que devuelve las filas de partida;
  • la consulta recursiva, que une la CTE con la tabla para encontrar el siguiente nivel.

PostgreSQL ejecuta la parte recursiva sobre las filas que produjo el paso anterior, y se detiene cuando un paso no devuelve filas.

Cada paso encuentra a las personas que dependen de las filas del paso anterior. Mia termina con profundidad 3 y la ruta Rosa > Omar > Ivo > Mia. Para listar a todos los que están bajo un responsable en vez del árbol entero, cambia el WHERE del ancla a id = 2.

Para subir, de un empleado hasta la cima, cambia la condición del join a s.id = c.manager_id.

Detener una CTE recursiva infinita

Una CTE recursiva solo termina cuando un paso no devuelve nada. Con datos que forman un ciclo (A depende de B, B depende de A), nunca termina. Añade un límite de profundidad como WHERE c.depth < 20, o usa la cláusula CYCLE (PostgreSQL 14+), que marca la primera fila repetida y deja de seguirla:

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;

Generar filas con una CTE recursiva

La parte recursiva no necesita una tabla. Esto cuenta de 1 a 5 devolviendo el valor de cada fila a la siguiente vuelta:

Para series simples de números o fechas, generate_series es más corto.

Mover filas con una CTE que modifica datos

Una CTE puede ser un INSERT, UPDATE o DELETE con una cláusula RETURNING, y la sentencia principal lee las filas devueltas. Esto mueve los pedidos antiguos a una tabla de archivo en una sola sentencia:

Los pedidos 1 y 3 se borran y se insertan en el archivo a la vez, así que si algo falla las dos tablas quedan como estaban. Todas las partes de la sentencia ven la misma instantánea: la consulta principal no ve el efecto del DELETE sobre orders, solo lo que le pasa RETURNING (ver RETURNING).

Controlar la integración con MATERIALIZED

Desde PostgreSQL 12, una CTE sin efectos secundarios que la consulta principal referencia una vez se integra en ella. El planificador la optimiza junto con la consulta externa, así que un WHERE escrito fuera de la CTE todavía puede usar un índice. Una CTE referenciada dos o más veces se calcula una vez y su resultado se reutiliza. Puedes forzar cualquiera de los dos comportamientos:

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 calcula la CTE una sola vez, por separado, y deja el WHERE externo fuera de ella. Úsalo cuando el plan integrado resulte más lento. NOT MATERIALIZED pide la integración incluso cuando la CTE se referencia más de una vez. Las CTE recursivas y las que modifican datos siempre se calculan por separado.

Preguntas frecuentes

¿Una CTE es más rápida que una subconsulta en PostgreSQL?

Normalmente ninguna es más rápida. Desde PostgreSQL 12, una CTE que se referencia una sola vez y no tiene efectos secundarios se integra en la consulta principal, así que recibe el mismo plan que la subconsulta equivalente. Antes de la versión 12 cada CTE se calculaba por separado, lo que podía impedir el uso de índices.

¿Puedo usar una CTE en un UPDATE o un DELETE?

Sí. WITH puede ir antes de INSERT, UPDATE, DELETE y MERGE, y la propia CTE puede ser un INSERT, UPDATE o DELETE con RETURNING, cuyas filas lee la sentencia principal.

¿Cómo evito un bucle infinito en una CTE recursiva?

Dale a la parte recursiva una condición que al final no devuelva filas, como un límite de profundidad (WHERE depth < 10). Para datos que pueden volver sobre sí mismos, la cláusula CYCLE (PostgreSQL 14+) marca la fila repetida y se detiene ahí.

¿Se puede usar una CTE en más de una consulta?

No. Una CTE solo existe en la sentencia donde se escribe. Para reutilizar una consulta en varias sentencias, crea una vista o una tabla temporal.

Ilustración de los lenguajes de programación de Coddy

Aprende a programar con Coddy

Comenzar