Una tabla de muestra y datos
CREATE TABLE dupes(col1 int primary key, col2 int, col3 text,
CONSTRAINT col2_unique UNIQUE (col2)
);
INSERT INTO dupes values(1,1,'a'),(2,2,'b');
Reproduciendo el problema
INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1) DO UPDATE SET col3 = 'c', col2 = 2
Llamemos a esto Q1. El resultado es
ERROR: llave duplicada viola la restricción de unicidad "col2_unique"
DETALLE: La llave (col2)=(2) ya existe.
conflict_target puede inferir un índice único. Al realizar la inferencia, consiste en una o más columnas indexadas y/o expresiones de índice y un predicado de índice opcional. Todos los índices únicos de table_name que, sin tener en cuenta el orden, contienen exactamente las columnas/expresiones conflict_target especificadas se infieren (se eligen) como índices árbitros. Si se especifica un predicado de índice, debe, como un requisito adicional para la inferencia, satisfacer los índices árbitros.
Esto da la impresión de que la siguiente consulta debería funcionar, pero no lo hace porque realmente requeriría un índice único conjunto en col1 y col2. Sin embargo, dicho índice no garantizaría que col1 y col2 fueran únicos individualmente, lo cual es uno de los requisitos del OP.
INSERT INTO dupes values(3,2,'c')
ON CONFLICT (col1,col2) DO UPDATE SET col3 = 'c', col2 = 2
Llamemos a esta consulta Q2 (esto falla con un error de sintaxis)
¿Por qué?
Postgresql se comporta de esta manera porque lo que debería suceder cuando ocurre un conflicto en la segunda columna no está bien definido. Existen varias posibilidades. Por ejemplo, en la consulta Q1 anterior, ¿debería postgresql actualizar col1 cuando hay un conflicto en col2? Pero, ¿qué pasa si eso conduce a otro conflicto en col1? ¿Cómo se espera que postgresql maneje eso?
Una solución
Una solución es combinar ON CONFLICT con UPSERT de la vieja escuela.
CREATE OR REPLACE FUNCTION merge_db(key1 INT, key2 INT, data TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
-- primero intentar actualizar la llave
UPDATE dupes SET col3 = data WHERE col1 = key1 and col2 = key2;
IF found THEN
RETURN;
END IF;
-- si no está allí, intentar insertar la llave
-- si alguien más inserta la misma llave concurrentemente o key2
-- ya existe en col2,
-- podríamos obtener un fallo de llave única
BEGIN
INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col1) DO UPDATE SET col3 = data;
RETURN;
EXCEPTION WHEN unique_violation THEN
BEGIN
INSERT INTO dupes VALUES (key1, key2, data) ON CONFLICT (col2) DO UPDATE SET col3 = data;
RETURN;
EXCEPTION WHEN unique_violation THEN
-- No hacer nada y volver a intentar la actualización.
END;
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
Necesitarías modificar la lógica de esta función almacenada para que actualice las columnas exactamente de la manera que desees. Llámala de la siguiente manera
SELECT merge_db(3,2,'c');
SELECT merge_db(1,2,'d');
