Un CTE de modificación de datos de PostgreSQL combina operaciones en una sola sentencia con una instantánea compartida. Las sentencias separadas de una transacción se ejecutan en secuencia y pueden leer escrituras anteriores. Elige en función de cómo necesita recibir sus datos cada operación.

Para un desarrollador que trabaja en Terminal en un Mac, la tarea concreta consiste en decidir si un flujo de trabajo sintético de artículos debe ejecutarse en una sola sentencia SQL o en varias consultas transaccionales. Los ejemplos siguientes muestran la diferencia de visibilidad, un flujo de trabajo adecuado con CTE y una transacción completa de node-postgres.

¿Qué cambia al combinar operaciones en un CTE?

Un CTE de modificación de datos contiene un INSERT, UPDATE, DELETE o, en PostgreSQL actual, MERGE dentro de WITH. Sus componentes comparten una instantánea; el orden en que se aplican físicamente las modificaciones es impredecible. Pasa las filas modificadas mediante RETURNING cuando otro componente las necesite. Escribir los CTE de arriba abajo no establece un orden de ejecución procedimental. PostgreSQL: consultas WITH

Las sentencias SQL separadas dentro de BEGIN y COMMIT tienen límites entre sentencias. En el nivel de aislamiento predeterminado Read Committed de PostgreSQL, cada sentencia toma una nueva instantánea y puede ver los cambios anteriores realizados por su propia transacción. Los commits de otras transacciones también pueden hacerse visibles entre sentencias. PostgreSQL: aislamiento de transacciones

Ambos enfoques pueden proporcionar una operación atómica. PostgreSQL ejecuta una sentencia individual dentro de una transacción implícita cuando no existe un bloque de transacción explícito; una transacción explícita agrupa varias sentencias. Un CTE de modificación de datos también puede ser una sentencia dentro de una transacción más amplia. PostgreSQL: transacciones

Requisito Punto de partida adecuado
Pasar filas modificadas directamente a otra tabla Un CTE con RETURNING
Leer una tabla después de que una sentencia anterior la haya modificado Sentencias secuenciales dentro de una transacción
Modificar la misma fila en dos etapas deliberadas Sentencias secuenciales o combinar el cálculo final en una sola actualización
Inspeccionar un resultado intermedio antes de elegir la siguiente consulta Sentencias secuenciales con lógica en la aplicación
Reducir el tiempo de espera entre solicitudes independientes Evaluar las opciones de transporte por separado de la semántica SQL

Esta guía de decisión sigue las reglas de visibilidad documentadas. Empieza por describir la dependencia: ¿la siguiente operación necesita una relación devuelta, una lectura nueva de la tabla o una decisión tomada por JavaScript? Esa pregunta resulta más útil que contar cuántas operaciones caben dentro de WITH.

¿Cómo puedes reproducir localmente la diferencia de visibilidad?

Usa una conexión existente a PostgreSQL 18 en psql o un editor SQL en tu Mac. Ejecuta el siguiente script completo en una sola sesión. Solo crea una tabla temporal, usa un artículo sintético y termina con ROLLBACK. Los valores esperados se derivan de la documentación; los ejemplos de base de datos no se ejecutaron durante la preparación.

BEGIN ISOLATION LEVEL READ COMMITTED;

CREATE TEMP TABLE cte_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO cte_demo_articles (id, title, stage)
VALUES (1, 'Sample article', 'draft');

WITH edited AS (
    UPDATE cte_demo_articles
    SET stage = 'edited'
    WHERE id = 1
    RETURNING id, stage
)
SELECT a.stage AS table_stage,
       e.stage AS returned_stage
FROM cte_demo_articles AS a
JOIN edited AS e ON e.id = a.id;
-- Expected: table_stage = draft, returned_stage = edited

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: edited, because this is a subsequent statement.

UPDATE cte_demo_articles SET stage = 'draft' WHERE id = 1;

UPDATE cte_demo_articles SET stage = 'edited' WHERE id = 1;
UPDATE cte_demo_articles
SET stage = 'ready'
WHERE id = 1 AND stage = 'edited';

SELECT stage FROM cte_demo_articles WHERE id = 1;
-- Expected: ready

ROLLBACK;

En la primera consulta, el escaneo de la tabla lee draft, mientras que la salida del CTE contiene edited. La siguiente sentencia lee edited de la tabla. Esto ilustra la distinción documentada entre leer una tabla de destino dentro de la sentencia que la modifica y leer las filas devueltas por el CTE. PostgreSQL: consultas WITH

Las dos últimas actualizaciones modifican deliberadamente la misma fila en sentencias separadas. Combinarlas en CTE de modificación hermanos no está admitido: PostgreSQL no realiza de forma fiable dos modificaciones de la misma fila dentro de una sola sentencia. Hacer referencia a un CTE anterior no elimina esa restricción. PostgreSQL: SELECT

¿Cuándo es adecuado un CTE de modificación de datos?

Mover filas seleccionadas de artículos a un archivo es una tarea relacional útil: la eliminación identifica las filas exactas y su relación devuelta proporciona los datos para la inserción. Con DELETE, RETURNING expone el contenido de la fila eliminada; con UPDATE, su salida predeterminada contiene los valores actualizados. PostgreSQL: devolver datos de filas modificadas

Este ejemplo independiente proporciona sus propias tablas y datos de entrada. Ejecútalo en una sesión después de que termine el ejemplo anterior.

BEGIN;

CREATE TEMP TABLE move_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

CREATE TEMP TABLE move_demo_archive (
    id integer PRIMARY KEY,
    title text NOT NULL,
    stage text NOT NULL
) ON COMMIT DROP;

INSERT INTO move_demo_articles (id, title, stage)
VALUES
    (1, 'Sample article A', 'ready'),
    (2, 'Sample article B', 'draft');

WITH moved AS (
    DELETE FROM move_demo_articles
    WHERE stage = 'ready'
    RETURNING id, title, stage
)
INSERT INTO move_demo_archive (id, title, stage)
SELECT id, title, stage
FROM moved
RETURNING id, title, stage;
-- Expected: article 1, with stage ready.

SELECT id, stage FROM move_demo_articles ORDER BY id;
-- Expected: article 2, with stage draft.

ROLLBACK;

La inserción consume moved directamente. El WITH de modificación debe estar asociado a la sentencia de nivel superior. Un CTE de modificación se ejecuta hasta completarse una vez, aunque su salida no se use; omitir RETURNING elimina su relación de resultado utilizable, pero no la modificación. PostgreSQL: consultas WITH

El límite de la transacción también importa en caso de error: si la inserción falla, la operación de traslado no se completa correctamente. En una transacción explícita, ejecuta ROLLBACK después de un error. No consideres que recibir filas intermedias correctamente demuestra que la transacción circundante se ha confirmado. PostgreSQL: transacciones

¿Cómo debe ejecutar node-postgres las sentencias dependientes?

Para un flujo de trabajo que examina un resultado antes de enviar la siguiente consulta, usa llamadas secuenciales a await en un mismo cliente. node-postgres requiere el mismo cliente para todas las sentencias de una transacción; despachar los pasos de la transacción mediante llamadas separadas a pool.query() no garantiza la continuidad de esa conexión. node-postgres: transacciones

Los requisitos previos son Node.js, npm y una conexión existente a una base de datos local PostgreSQL 18. En el directorio de tu proyecto de Node, en Terminal, ejecuta npm install pg. Guarda el programa completo siguiente como article-transaction.mjs y luego ejecuta node article-transaction.mjs. new Client() usa las variables de entorno habituales de conexión de PostgreSQL; configúralas para tu base de datos existente si los valores predeterminados no coinciden. node-postgres: conexión

import pg from 'pg';

const { Client } = pg;
const client = new Client();
await client.connect();

try {
  await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

  await client.query(`
    CREATE TEMP TABLE articles (
      id integer PRIMARY KEY,
      title text NOT NULL,
      stage text NOT NULL
    ) ON COMMIT DROP
  `);

  await client.query(
    'INSERT INTO articles VALUES ($1, $2, $3)',
    [1, 'Sample article', 'draft']
  );

  const first = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 RETURNING id, stage`,
    ['edited', 1]
  );
  if (first.rowCount !== 1) {
    throw new Error('Expected one article');
  }

  const second = await client.query(
    `UPDATE articles SET stage = $1
     WHERE id = $2 AND stage = $3
     RETURNING id, stage`,
    ['ready', 1, 'edited']
  );
  if (second.rowCount !== 1) {
    throw new Error('Expected one edited article');
  }

  await client.query('COMMIT');
  console.log(second.rows);
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  await client.end();
}

La fila impresa esperada tiene id: 1 y stage: 'ready'. La tabla temporal se elimina al confirmar la transacción. Se comprobó la sintaxis de JavaScript con Node.js; no se ejecutó el código contra una base de datos.

Las llamadas de consulta pasan los valores por separado mediante $1, $2 y $3, de acuerdo con la API de consultas parametrizadas de node-postgres. Cada llamada contiene una sentencia SQL. node-postgres: consultas

Comprobar rowCount convierte una actualización inesperada que afecta a cero filas en un error explícito antes de confirmar la transacción. rowCount indica el número de filas procesadas por el comando, mientras que rows contiene los registros devueltos; una actualización sin RETURNING puede afectar filas y, aun así, devolver un array rows vacío. node-postgres: pg.Result

¿Agrupar sentencias cambia su semántica SQL?

Un lote describe cómo se envía el trabajo. No convierte varias sentencias SQL en un único CTE de modificación de datos.

El protocolo de consulta simple de PostgreSQL acepta sentencias separadas por punto y coma. Sin control explícito de transacciones, las sentencias de un mismo mensaje Query simple se ejecutan en una transacción implícita. Un error detiene las sentencias restantes. El mensaje Parse del protocolo extendido acepta una sola sentencia, por lo que un lote separado por punto y coma no reemplaza de forma general las consultas parametrizadas independientes. PostgreSQL: flujo de mensajes

Un lote explícito también necesita gestión de errores fuera del propio lote: si se produce un error antes de un ROLLBACK final, nunca se llega a ejecutar esa reversión. Envía una solicitud de reversión por separado para finalizar la transacción fallida. PostgreSQL: varias sentencias en una consulta simple

La documentación actual de node-postgres también describe el procesamiento en canalización, que se activa de forma opcional. Envía solicitudes sin esperar cada respuesta, mientras que PostgreSQL las procesa en orden. La documentación recomienda llamadas secuenciales a await cuando la siguiente consulta de una transacción depende de un resultado anterior. El procesamiento en canalización cambia el momento de envío de las solicitudes; no crea una instantánea de CTE compartida. node-postgres: procesamiento en canalización

¿Qué límites de versión y aislamiento deberían guiar la decisión?

La documentación actual de PostgreSQL consultada para este artículo corresponde a la versión 18. PostgreSQL 16 permite INSERT, UPDATE y DELETE como cuerpos de CTE de modificación, pero excluye MERGE. La documentación de la versión 17 incluye MERGE y sus notas de la versión documentan la incorporación de MERGE RETURNING. Para un flujo de trabajo en PostgreSQL 16, usa sentencias de modificación admitidas o sentencias separadas en lugar de copiar sintaxis de CTE más reciente. PostgreSQL 16: consultas WITH, PostgreSQL 17: consultas WITH, Notas de la versión 17 de PostgreSQL

Repeatable Read no hace que las sentencias secuenciales se comporten como CTE hermanos. Mantiene una instantánea de transacción establecida por la primera sentencia que no sea de control de transacciones, pero las sentencias posteriores siguen viendo las escrituras anteriores de la propia transacción. Las aplicaciones que usan este nivel de aislamiento también deben estar preparadas para reintentar transacciones tras errores de serialización. PostgreSQL: nivel de aislamiento Repeatable Read

MATERIALIZED y NOT MATERIALIZED no ofrecen una solución alternativa para la visibilidad de los CTE de modificación. PostgreSQL ignora NOT MATERIALIZED en los CTE que no están libres de efectos secundarios. Esos controles del planificador no pueden establecer una secuencia de escrituras sobre la misma fila. PostgreSQL: SELECT

Elige un CTE de modificación cuando las filas devueltas formen naturalmente la entrada de la siguiente operación. Elige sentencias secuenciales cuando el trabajo posterior necesite cambios anteriores en la tabla o una decisión de la aplicación. Conserva esa dependencia al optimizar el momento de las solicitudes y usa una transacción explícita siempre que deban completarse juntas varias sentencias.

Fuentes