PostgreSQL
INSERT, UPDATE, DELETE y upsert
Operaciones de escritura con INSERT, UPDATE, DELETE, RETURNING y ON CONFLICT, incluyendo atomicidad, filtros seguros y manejo de concurrencia.
- Última actualización
- Actualizada
- Nivel
- Fundamentos
PostgreSQL
Operaciones de escritura con INSERT, UPDATE, DELETE, RETURNING y ON CONFLICT, incluyendo atomicidad, filtros seguros y manejo de concurrencia.
Las escrituras correctas no solo modifican filas: expresan qué conjunto cambia, protegen reglas bajo concurrencia y devuelven suficiente información para que la aplicación conozca el resultado real.
INSERT, UPDATE y DELETE son operaciones set-based. Una sola sentencia puede afectar cero, una o muchas filas. PostgreSQL ejecuta la sentencia dentro de una transacción, valida constraints y produce nuevas versiones de fila bajo MVCC.
El modelo mental es:
input parametrizado
→ localizar conjunto objetivo
→ adquirir locks necesarios
→ calcular nuevas filas
→ validar constraints/triggers
→ escribir WAL y nuevas versiones
→ devolver resultado con RETURNINGUna sentencia exitosa no siempre significa que modificó una fila. Por ejemplo, un UPDATE con condición de stock puede devolver cero filas porque el producto no existe o porque no había cantidad suficiente. El contrato de aplicación debe interpretar ese resultado.
Las escrituras ingenuas suelen separar comprobación y cambio:
SELECT stock
→ aplicación decide
→ UPDATE stockEntre SELECT y UPDATE otra transacción puede cambiar el mismo dato. El resultado es una carrera.
PostgreSQL permite expresar la condición dentro de la modificación:
UPDATE inventory
SET quantity = quantity - $1
WHERE branch_id = $2
AND product_id = $3
AND quantity >= $1
RETURNING quantity;La fila se evalúa y actualiza bajo el lock de la sentencia. Así, dos ventas concurrentes no pueden consumir el mismo último stock mediante esa operación individual.
INSERT INTO customers (business_id, name, email)
VALUES ($1, $2, $3)
RETURNING id, created_at;Flujo:
NOT NULL, CHECK, UNIQUE y FKs.RETURNING expone los valores finales.No asumas que los valores finales son idénticos a los enviados: defaults, generated columns o triggers pueden modificarlos.
INSERT INTO products (business_id, sku, name)
VALUES
($1, 'A-1', 'Arroz'),
($1, 'L-1', 'Leche')
RETURNING id, sku;La sentencia es atómica: si una fila viola una constraint y el error no se maneja mediante una cláusula como ON CONFLICT, toda la sentencia falla.
El orden de las filas devueltas no debe tratarse como contrato salvo que se procese explícitamente después. Para correlacionar input y output puede incluirse un identificador de importación en una CTE o staging table.
INSERT INTO archived_orders (id, customer_id, total_amount, archived_at)
SELECT id, customer_id, total_amount, now()
FROM orders
WHERE status = 'cancelled'
AND created_at < now() - interval '2 years';Esto mueve o copia conjuntos sin traer filas al backend. Si también debes eliminarlas de la fuente, coordina ambas operaciones en una transacción y define qué ocurre ante concurrencia.
Omitir una columna activa su default:
INSERT INTO orders (customer_id)
VALUES ($1);Enviar NULL explícito no utiliza el default:
INSERT INTO orders (customer_id, status)
VALUES ($1, NULL);Si status es NOT NULL, la sentencia falla.
También existe DEFAULT explícito:
INSERT INTO orders (customer_id, status)
VALUES ($1, DEFAULT);UPDATE products
SET name = $1,
updated_at = now()
WHERE business_id = $2
AND id = $3
RETURNING id, name, updated_at;Un UPDATE crea una nueva versión lógica de la fila. La versión anterior permanece hasta que vacuum pueda recuperarla.
Debes interpretar:
UPDATE inventory
SET reserved_quantity = reserved_quantity + $1
WHERE branch_id = $2
AND product_id = $3
AND quantity - reserved_quantity >= $1
RETURNING reserved_quantity;La expresión utiliza el valor visible y bloqueado para esa actualización. Es preferible a calcular newReserved en la aplicación desde un valor leído anteriormente.
Una API puede enviar la versión conocida:
UPDATE orders
SET notes = $1,
version = version + 1
WHERE id = $2
AND version = $3
RETURNING id, version;Si devuelve cero filas, la fila cambió o no existe. La aplicación puede responder conflicto y pedir al cliente recargar.
Alternativas para token de versión:
updated_at, con cuidado por resolución y semántica.xmin, que no suele recomendarse como contrato durable porque es metadata interna y puede cambiar por freeze/operación.UPDATE products AS p
SET category_id = m.category_id
FROM product_category_mapping AS m
WHERE m.business_id = p.business_id
AND m.sku = p.sku;FROM permite relacionar fuentes, pero cada fila target debe coincidir con como máximo una fila fuente. Si existen varias coincidencias, PostgreSQL elegirá una de forma no determinista para los valores; no confíes en cuál.
Protege unicidad en product_category_mapping o agrega/deduplica antes.
DELETE FROM sessions
WHERE expires_at < now()
RETURNING id;DELETE marca versiones como eliminadas para snapshots futuros; el espacio se recupera después mediante vacuum.
Para eliminar por lotes en una tabla grande, PostgreSQL no soporta DELETE ... LIMIT directamente. Puedes seleccionar IDs:
WITH batch AS (
SELECT id
FROM sessions
WHERE expires_at < now()
ORDER BY id
LIMIT 1000
FOR UPDATE SKIP LOCKED
)
DELETE FROM sessions s
USING batch b
WHERE s.id = b.id
RETURNING s.id;Este patrón permite varios workers, pero requiere evaluar índices, locks y criterio de finalización.
DELETE FROM sessions AS s
USING users AS u
WHERE s.user_id = u.id
AND u.status = 'deleted'
RETURNING s.id;Es equivalente conceptualmente a un delete basado en join. Verifica cardinalidad y no elimines más filas de las previstas.
Disponible en INSERT, UPDATE, DELETE y MERGE según la operación.
Usos:
Ejemplo:
WITH created AS (
INSERT INTO orders (customer_id, status)
VALUES ($1, 'pending')
RETURNING id, customer_id
)
INSERT INTO outbox_events (event_type, aggregate_id, payload)
SELECT
'order.created',
id,
jsonb_build_object('orderId', id, 'customerId', customer_id)
FROM created
RETURNING aggregate_id;Ambas escrituras forman parte de la misma sentencia y transacción.
INSERT INTO inventory (branch_id, product_id, quantity)
VALUES ($1, $2, $3)
ON CONFLICT (branch_id, product_id)
DO UPDATE
SET quantity = inventory.quantity + EXCLUDED.quantity,
updated_at = now()
RETURNING quantity;Elementos:
EXCLUDED: fila propuesta que no pudo insertarse.DO NOTHING o DO UPDATE.La operación es segura frente a la carrera de inserción porque la unique constraint arbitra el conflicto.
ON CONFLICT (provider, external_id)
DO UPDATE
SET payload = EXCLUDED.payload,
received_at = now()
WHERE events.payload IS DISTINCT FROM EXCLUDED.payloadLa cláusula WHERE decide si se actualiza la fila en conflicto. Si no se cumple, RETURNING puede no devolverla; diseña el contrato.
Upsert no vuelve automáticamente idempotente una operación.
Este upsert:
quantity = inventory.quantity + EXCLUDED.quantityrepetido dos veces suma dos veces.
Para un evento que debe aplicarse una sola vez necesitas una key de evento única y una transacción:
insert processed_event
→ si ya existe, no aplicar
→ si se insertó, modificar inventoryMERGE compara una fuente y el target, y ejecuta ramas WHEN MATCHED / WHEN NOT MATCHED.
Ejemplo conceptual:
MERGE INTO products AS p
USING import_products AS i
ON p.business_id = i.business_id AND p.sku = i.sku
WHEN MATCHED THEN
UPDATE SET name = i.name, price = i.price
WHEN NOT MATCHED THEN
INSERT (business_id, sku, name, price)
VALUES (i.business_id, i.sku, i.name, i.price);Trade-offs:
UPDATE products
SET deleted_at = now()
WHERE id = $1
AND deleted_at IS NULL
RETURNING id;Ventajas:
Costes:
Índice parcial:
CREATE UNIQUE INDEX products_active_sku_uidx
ON products(business_id, sku)
WHERE deleted_at IS NULL;Evita una transacción enorme sin medir:
Procesa por lotes con orden estable e idempotencia. Pero batches demasiado pequeños aumentan overhead; mide.
Valores:
client.query('DELETE FROM orders WHERE id = $1', [orderId]);No parametrices identifiers. Para una columna dinámica:
const allowedSortColumns = {
createdAt: 'created_at',
total: 'total_amount',
} as const;Selecciona desde una whitelist y construye solo el fragmento conocido.
Después de un error dentro de una transacción, PostgreSQL marca la transacción como abortada hasta ROLLBACK o rollback a savepoint.
La aplicación debe:
BEGIN
→ ejecutar
→ COMMIT
si falla
→ ROLLBACK
→ liberar conexiónNunca devuelvas al pool una conexión con transacción abierta o abortada.
PostgreSQL puede crear una nueva versión aunque los valores sean iguales. Usa condiciones IS DISTINCT FROM cuando evitar escrituras inútiles importa.
Puede significar not found, no autorizado por scope, conflicto optimista o condición de negocio. No respondas siempre 404 sin distinguir el contrato.
Pueden modificar otras tablas o valores. Incluye efectos en testing y observabilidad.
Un DELETE de una fila puede borrar muchos hijos. Estima volumen.
Una actualización de la partition key puede mover la fila entre partitions y tener implicaciones de concurrencia.
Puede afectar toda la tabla. Usa revisión, transacciones de prueba y permisos.
Introduce carreras. Expresa condición dentro del DML o bloquea conscientemente.
No existe conflicto bien definido.
Una actualización acumulativa puede duplicar efecto.
Produce valores indeterminados.
Bloquea reutilización de claves y degrada consultas.
SELECT y consultas básicas construye resultados deterministas, expresivos y seguros antes de combinarlos con joins, agregaciones y ventanas.