PostgreSQL no tiene la palabra clave UPSERT. Se escribe INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. Cuando la fila nueva rompería una restricción única sobre esa columna, PostgreSQL actualiza la fila existente en su lugar. DO NOTHING la omite.
A-100 ya existía, así que su precio pasó a 12.5. B-200 era nuevo y se insertó. Una sola sentencia resuelve los dos casos, y sigue siendo correcta cuando otras sesiones insertan las mismas claves a la vez.
Insertar o actualizar una fila
La sentencia tiene tres partes:
INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
- El objetivo del conflicto nombra las columnas de un índice o una restricción única, como la clave primaria. Tiene que coincidir exactamente con uno, o PostgreSQL lanza "there is no unique or exclusion constraint matching the ON CONFLICT specification". También puedes nombrar la restricción:
ON CONFLICT ON CONSTRAINT products_pkey. EXCLUDEDes la fila que intentaste insertar.EXCLUDED.pricees el precio nuevo, yproducts.pricees el precio ya guardado.DO UPDATE SETcambia solo las columnas que enumeras. Las demás columnas de la fila existente conservan sus valores.
Un índice único con cláusula WHERE (un índice parcial) también sirve como objetivo, si repites su condición: ON CONFLICT (email) WHERE deleted_at IS NULL.
Omitir duplicados con DO NOTHING
DO NOTHING inserta las filas nuevas y descarta las que chocan, sin error. Sin objetivo de conflicto, omite cualquier fila que rompa alguna restricción única.
RETURNING solo muestra lin@example.com. Una fila omitida no se devuelve, así que DO NOTHING ... RETURNING muestra qué filas eran nuevas.
Sumar a un contador
Para aumentar un valor guardado en lugar de reemplazarlo, suma EXCLUDED al valor actual. Califica el valor actual con el nombre de la tabla. Dentro de DO UPDATE, un views a secas se refiere tanto a la tabla como a EXCLUDED, y PostgreSQL se detiene con "column reference "views" is ambiguous".
La primera sentencia inserta la fila con 1 y las dos siguientes le suman, así que /home termina en 3.
Actualizar solo cuando el valor cambió
Un WHERE después de DO UPDATE SET decide, fila a fila, si se ejecuta el update. Cuando es falso, la fila se queda como está y no cuenta ni como insertada ni como actualizada. Úsalo para quedarte solo con los datos más recientes, o para no escribir una nueva versión de la fila cuando nada cambió.
A-100 conserva el 10 porque la fila entrante es más antigua. B-200 toma el 25. Para omitir updates que escribirían los mismos valores, usa WHERE prices.price IS DISTINCT FROM EXCLUDED.price, que también maneja NULL.
Distinguir filas insertadas de actualizadas
RETURNING funciona en un upsert igual que en cualquier insert. Para distinguir los dos casos, lee la columna de sistema xmax, que vale 0 en una fila que insertó esta sentencia:
A-100 vuelve con 8 e inserted en false, C-300 con 7 y true. xmax es un detalle interno, no una API documentada, así que trátalo como un atajo. La página de RETURNING explica la cláusula a fondo.
Errores con los que te vas a encontrar
- "ON CONFLICT DO UPDATE command cannot affect row a second time": la misma clave aparece dos veces en el
VALUESo elSELECTde una sentencia. Quita antes los duplicados, por ejemplo conSELECT DISTINCT ON (sku) .... - "column reference is ambiguous": califica la columna con el nombre de la tabla en las expresiones de
SETy en elWHERE, como arriba. - Huecos en los ids identity o serial: un insert que se convierte en update ya tomó un valor de la secuencia, así que los ids se saltan números. Es lo esperado.
MERGE (PostgreSQL 15 y posteriores) también puede insertar o actualizar, pero no protege frente a dos sesiones que insertan la misma clave a la vez. Para upserts por clave, usa ON CONFLICT. La referencia oficial de INSERT lista todas las opciones.
Preguntas frecuentes
¿PostgreSQL tiene una sentencia UPSERT?
No existe la palabra clave UPSERT. PostgreSQL 9.5 añadió INSERT ... ON CONFLICT, que inserta una fila o, cuando rompería una restricción única, actualiza la fila existente (DO UPDATE) o la omite (DO NOTHING).
¿INSERT ... ON CONFLICT es seguro con inserciones concurrentes?
Sí. Cada fila propuesta termina en un insert o en un update, aunque otras sesiones inserten la misma clave en el mismo momento. Un SELECT seguido de un INSERT o UPDATE en el código de la aplicación no da esa garantía.
¿Uso MERGE u ON CONFLICT para un upsert?
Usa ON CONFLICT para insertar o actualizar por clave. MERGE (PostgreSQL 15 y posteriores) cubre más casos, como borrar las filas que coinciden. Con inserciones concurrentes de la misma clave puede fallar con una violación de unicidad, algo que ON CONFLICT evita.
¿Por qué aparece "there is no unique or exclusion constraint matching the ON CONFLICT specification"?
Las columnas de ON CONFLICT (...) tienen que coincidir exactamente con un índice o una restricción única. Añade una restricción UNIQUE o un índice único sobre esas columnas, o nombra las columnas de uno que ya exista.