UPSERT en PostgreSQL: INSERT ... ON CONFLICT en la práctica

UPSERT significa «inserta una fila, o actualízala si ya existe» en una única sentencia atómica. En PostgreSQL ese trabajo le corresponde a INSERT ... ON CONFLICT. Cierra la clásica condición de carrera en la que primero haces un SELECT, no encuentras nada, haces el INSERT — y entonces chocas con una violación de unicidad porque una transacción concurrente insertó la misma fila por el medio. ON CONFLICT resuelve eso dentro del motor, sin bloqueo explícito y sin el bucle de «intenta insertar, captura el error, actualiza».

Aquí tienes un esquema funcional y ejemplos listos para copiar y pegar en psql.

Sintaxis básica: DO NOTHING y DO UPDATE

Empezamos con una tabla de usuarios que tiene un email único:

CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    name       text NOT NULL,
    visits     int  NOT NULL DEFAULT 0,
    updated_at timestamptz NOT NULL DEFAULT now()
);

DO NOTHING ignora silenciosamente un conflicto en lugar de fallar con un error:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann')
ON CONFLICT (email) DO NOTHING;

DO UPDATE actualiza en su lugar la fila existente. Un detalle clave: tras ON CONFLICT indicas el objetivo del conflicto (conflict target), es decir, la columna o columnas respaldadas por un índice o restricción únicos que PostgreSQL usa para detectar la colisión:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name,
              updated_at = now();

• El objetivo del conflicto debe coincidir con un índice único o una PK reales. Sin un índice que case, PostgreSQL rechaza la sentencia.
• Puedes apuntar por nombre de restricción en su lugar: ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE ....

EXCLUDED: los valores de la inserción rechazada

EXCLUDED es una pseudotabla que contiene la fila que intentaste insertar pero no pudiste por culpa del conflicto. Dentro de DO UPDATE extraes de ahí los valores «nuevos» en lugar de volver a escribirlos. Eso resulta especialmente cómodo para inserciones por lotes:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann'),
       ('bob@example.com', 'Bob')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

Puedes combinar lo viejo y lo nuevo; por ejemplo, sobrescribir solo cuando llega un nombre no vacío:

INSERT INTO users (email, name)
VALUES ('ann@example.com', NULL)
ON CONFLICT (email)
DO UPDATE SET name = COALESCE(EXCLUDED.name, users.name);

Aquí users.name es el valor actual en la tabla y EXCLUDED.name es lo que intentaste escribir. Eso te da un control fino sobre qué columnas sobrescribir y cuáles conservar.

Inserciones idempotentes y WHERE en DO UPDATE

Idempotencia significa que ejecutar la misma sentencia de nuevo no cambia el resultado. Eso importa para la entrega de mensajes «al menos una vez», los reintentos y las importaciones reejecutables. La forma más simple es DO NOTHING sobre una clave natural:

INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'paid')
ON CONFLICT (order_id) DO NOTHING;

Ejecútala diez veces y la fila aparece exactamente una vez. Si en cambio sí quieres actualizar, pero solo cuando los datos realmente cambiaron, añade un WHERE al DO UPDATE: esto evita escrituras inútiles e impide que los triggers se disparen para nada:

INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'shipped')
ON CONFLICT (order_id)
DO UPDATE SET status = EXCLUDED.status,
              updated_at = now()
WHERE orders.status IS DISTINCT FROM EXCLUDED.status;

IS DISTINCT FROM compara valores de forma segura incluso cuando hay NULL de por medio. Si el estado no ha cambiado, no se produce ninguna actualización.

Contadores atómicos

Una tarea clásica: contar visitas o vistas de página sin condiciones de carrera. En lugar de SELECT ... + UPDATE, haz el incremento directamente dentro de DO UPDATE, haciendo referencia al valor actual de la fila:

INSERT INTO users (email, name, visits)
VALUES ('ann@example.com', 'Ann', 1)
ON CONFLICT (email)
DO UPDATE SET visits = users.visits + 1,
              updated_at = now();

La primera llamada crea la fila con visits = 1; cada llamada posterior incrementa el contador en uno de forma atómica. Las transacciones concurrentes se encolan a nivel de fila, así que no se pierde ninguna actualización. Añade RETURNING para leer de inmediato el nuevo valor:

... DO UPDATE SET visits = users.visits + 1
RETURNING visits;

Trampas y la diferencia con MySQL

• Varios conflictos en una sola sentencia. Si una fila viola dos índices únicos distintos a la vez, ON CONFLICT solo gestiona el objetivo que nombraste; el otro índice sigue lanzando un error.
• Duplicados dentro de una misma lista VALUES. No puedes actualizar la misma fila objetivo dos veces en una sola sentencia: ON CONFLICT DO UPDATE command cannot affect row a second time. Deduplica tu entrada antes de insertar.
• Tablas particionadas. El objetivo del conflicto debe incluir la clave de partición.

El equivalente en MySQL es INSERT ... ON DUPLICATE KEY UPDATE. Se dispara con cualquier clave única (no se nombra ningún objetivo) y, en lugar de EXCLUDED, usas la función VALUES() (un alias de fila en las versiones más recientes):

-- MySQL
INSERT INTO users (email, name, visits)
VALUES ('ann@example.com', 'Ann', 1)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    visits = visits + 1;

ClickHouse no tiene un UPSERT de verdad: el motor no impone la unicidad sobre la marcha. Los patrones habituales son ReplacingMergeTree (la deduplicación ocurre en segundo plano durante las fusiones) o un INSERT simple seguido de un filtrado en el momento de la lectura. Si necesitas inserciones idempotentes estrictas y contadores atómicos ahora mismo, ese es el terreno de PostgreSQL y MySQL, no el de los almacenes columnares analíticos.