Escribir un upsert con INSERT ... ON CONFLICT

Objetivo

Insertar una fila cuando su clave es nueva y, en caso contrario, actualizar la fila existente, sin una condición de carrera entre SELECT e INSERT y sin bucles de reintento en el código de la aplicación.

Requisitos previos

Un índice único o una restricción única sobre las columnas de la clave (la documentación de INSERT llama índice árbitro al índice elegido para detectar el conflicto y describe cómo se infiere a partir de las columnas indicadas, de un predicado de índice o de ON CONSTRAINT name), y una decisión sobre qué columnas puede sobrescribir una actualización.

Pasos

1. Escribe el INSERT simple con todas las columnas.
2. Añade ON CONFLICT (key_columns) con las columnas del índice único. Si el índice único es parcial, repite su predicado WHERE después de la lista de columnas para que la inferencia lo encuentre.
3. Elige la acción: DO NOTHING cuando una fila existente no deba modificarse; DO UPDATE SET col = EXCLUDED.col, updated_at = now() para sobrescribir columnas concretas. EXCLUDED es la fila que se había propuesto para insertar.
4. Protege las actualizaciones sin efecto con WHERE t.col IS DISTINCT FROM EXCLUDED.col después de la lista SET, de modo que las filas sin cambios no se reescriban; cada actualización crea una versión muerta de la fila y dispara triggers.
5. Añade RETURNING id cuando quien llama necesite la clave de la fila insertada o actualizada.
6. Para lotes, usa un único INSERT con muchas filas en VALUES o INSERT ... SELECT; no recorras en un bucle upserts de una sola fila.
7. Cuando la lógica tenga varias ramas (actualizar si hay coincidencia y se cumple una condición, borrar si hay coincidencia en caso contrario, insertar si no hay coincidencia), escribe MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...; la documentación de MERGE enumera las acciones WHEN MATCHED, WHEN NOT MATCHED y WHEN NOT MATCHED BY SOURCE.

Resultado esperado

Una sentencia por upsert, sin filas duplicadas con escritores concurrentes, porque el conflicto se detecta en el índice único dentro de la propia sentencia, y con la generación de filas muertas limitada a las filas que realmente cambiaron.

Límites y base de verificación

ON CONFLICT DO UPDATE requiere un destino del conflicto; solo se gestionan los conflictos sobre los índices árbitros que selecciona (la violación de otro índice único sigue produciendo un error), y solo las restricciones NOT DEFERRABLE y los índices únicos pueden actuar como árbitros. DO NOTHING sin destino del conflicto gestiona los conflictos con todas las restricciones utilizables. La documentación de las secuencias indica que un INSERT con cláusula ON CONFLICT calcula la fila, incluidas las llamadas a nextval, antes de detectar el conflicto, de modo que se consumen valores de identidad por filas que finalmente no se insertan. La documentación de MERGE dice que, con concurrencia, se aplican las reglas de aislamiento habituales y señala INSERT ... ON CONFLICT como la sentencia que puede ejecutar un UPDATE cuando se produce un INSERT concurrente; las dos sentencias no son intercambiables entre sí. No se afirma ninguna medición de tiempos.

Cuándo MERGE es la herramienta adecuada

MERGE e INSERT ... ON CONFLICT no son intercambiables con concurrencia. MERGE evalúa la coincidencia sobre un snapshot y no realiza la inserción especulativa en la que se apoya ON CONFLICT, por lo que dos sesiones que ejecuten MERGE sobre la misma clave nueva pueden tomar ambas la rama WHEN NOT MATCHED, y la segunda falla con una violación de unicidad. Usa MERGE cuando un único escritor sea dueño del rango de claves en cada momento: cargas por lotes, migraciones, importadores de un solo hilo o filas bloqueadas de antemano. Usa ON CONFLICT siempre que puedan existir escritores concurrentes; varias ramas de actualización caben en expresiones CASE dentro de la lista SET, y una rama de borrado pasa a ser una sentencia aparte. WHEN NOT MATCHED BY SOURCE y RETURNING en MERGE requieren PostgreSQL 17 o posterior.