sirve - postgresql version

Manejo de conflictos de Postgres con múltiples restricciones únicas.

Question

Me gustaría usar la sintaxis insert ... on confict do update ... con una tabla que tiene restricciones únicas en dos columnas. es posible?

Por ejemplo, mytable tiene restricciones únicas separadas en col1 y col2.

Puedo escribir:

INSERT INTO mytable(col1, col2, col3) values (''A'', ''B'', 0) ON CONFLICT DO NOTHING;

Sin embargo, esto no funciona:

INSERT INTO mytable(col1, col2, col3) VALUES (''A'', ''B'', 0) 
ON CONFLICT 
DO UPDATE SET col3 = EXCLUDED.col3 + 1;

ERROR: SOBRE LA ACTUALIZACIÓN DE CONFLICTO DE CONFIGURACIÓN requiere la especificación de la inferencia o el nombre de la restricción

Esto tampoco funciona:

INSERT INTO mytable(col1, col2, col3) VALUES (''A'', ''B'', 0)
ON CONFLICT (col1, col2) 
DO UPDATE SET col3 = EXCLUDED.col3 + 1;

ERROR: no hay ninguna restricción exclusiva o de exclusión que coincida con la especificación ON CONFLICT

Esta sintaxis parece estar diseñada para una única restricción única compuesta sobre dos columnas, en lugar de dos restricciones.

¿Hay alguna manera de hacer una actualización condicional si se viola cualquiera de las restricciones únicas? Esta pregunta ¿Cómo aumentar en Postgres el conflicto en una de las 2 columnas? Alude a ello pero no proporciona la sintaxis.

Answer 1

La cláusula ON CONFLICT necesita una única restricción única cuando le pedimos a DO UPDATE . Cuando se define una clave principal, es suficiente con solo hacer referencia al nombre de la columna; que es el ejemplo dominante que se tiende a encontrar.

Usted menciona que tiene ''restricciones únicas separadas en col1 y col2'', por lo que podría suponer que la definición de su tabla es similar a esto:

CREATE TABLE mytable(       
    col1 varchar UNIQUE,    
    col2 varchar UNIQUE,    
    col3 int
);

Pero su consulta está haciendo referencia a una restricción compuesta; en lugar de restricciones separadas. Una definición de tabla modificada como esta:

CREATE TABLE mytable2(  
    col1 varchar UNIQUE,
    col2 varchar UNIQUE,
    col3 int,
    CONSTRAINT ux_col1_col2 UNIQUE (col1,col2)
);

trabajaría con su consulta anterior:

INSERT INTO mytable(col1, col2, col3) VALUES (''A'', ''B'', 0)
ON CONFLICT (col1, col2) 
DO UPDATE SET col3 = EXCLUDED.col3 + 1;

Puede hacer referencia a esta restricción única como ON CONFLICT (col1, col2) o como ON CONFLICT ON CONSTRAINT ux_col1_col2 .

Pero espera hay mas...

La idea es mantener actualizada una columna de contador que coincida con una columna única o insertar cero si no existe ninguno ...

Ese es un camino diferente al que estás tomando aquí. " coincidencias en cualquiera de las columnas únicas " permite la coincidencia en ambas, una o la otra. Si entiendo su intención, solo tenga una sola etiqueta e incremente los contadores en los registros correspondientes. Asi que:

CREATE TABLE mytable2(  
    col1 varchar PRIMARY KEY,
    col3 int
);
INSERT INTO mytable2(col1,col3)
SELECT incr_label,0
FROM (VALUES (''A''),(''B''),(''C'')) as increment_list(incr_label)
ON CONFLICT (col1)
DO UPDATE SET col3 = mytable2.col3 + 1
RETURNING col1,col3;