Problema de PostgreSQL UPSERT con valores NULL

Este equipo de expertos luego de ciertos días de investigación y recopilar de información, obtuvieron la solución, queremos que resulte de gran utilidad para tu proyecto.

Solución:

Aclarar ON CONFLICT DO UPDATE comportamiento

Considere el manual aquí:

Para cada fila individual propuesto para la inserción, la inserción procede, o, si una restricción de árbitro o índice especificado por
conflict_target es violada, la alternativa conflict_action se toma.

El énfasis audaz es mío. Por lo tanto, no tiene que repetir predicados para columnas incluidas en el índice único en el WHERE cláusula a la UPDATE (los conflict_action):

INSERT INTO test_upsert AS tu
       (name   , status, test_field  , identifier, count) 
VALUES ('shaun', 1     , 'test value', 'ident'   , 1)
ON CONFLICT (name, status, test_field) DO UPDATE
SET count = tu.count + 1;
WHERE tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value'

La infracción única ya establece lo que agregaste. WHERE cláusula se aplicaría de forma redundante.

Aclarar índice parcial

Agrega un WHERE cláusula para convertirlo en un índice parcial real como usted mismo mencionó (pero con lógica invertida):

CREATE UNIQUE INDEX test_upsert_partial_idx
ON public.test_upsert (name, status)
WHERE test_field IS NULL;  -- not: "is not null"

Para usar este índice parcial en su UPSERT necesita una coincidencia conflict_target como demuestra @ypercube:

ON CONFLICT (name, status) WHERE test_field IS NULL

Ahora se infiere el índice parcial anterior. Sin embargo, como también señala el manual:

[…] un índice único no parcial (un índice único sin un predicado) será inferido (y por lo tanto utilizado por ON CONFLICT) si se dispone de un índice que satisfaga todos los demás criterios.

Si tiene un índice adicional (o solo) en solo (name, status) se utilizará (también). Un índice sobre (name, status, test_field) lo haría explícitamente no inferirse. Esto no explica su problema, pero puede haber aumentado la confusión durante la prueba.

Solución

AIUI, nada de lo anterior resuelve su problema, todavía. Con el índice parcial, solo se detectarían casos especiales con valores NULL coincidentes. Y se insertarán otras filas duplicadas si no tiene otros índices / restricciones únicos coincidentes, o generará una excepción si la tiene. Supongo que eso no es lo que quieres. Usted escribe:

El compuesto key se compone de 20 columnas, 10 de las cuales pueden ser anulables.

¿Qué consideras exactamente un duplicado? Postgres (según el estándar SQL) no considera que dos valores NULL sean iguales. El manual:

En general, se infringe una restricción única si hay más de una fila en la tabla donde los valores de todas las columnas incluidas en la restricción son iguales. Sin embargo, dos null los valores nunca se consideran iguales en esta comparación. Eso significa que incluso en presencia de una restricción única es posible almacenar filas duplicadas que contienen un null valor en al menos una de las columnas restringidas. Este comportamiento cumple con el estándar SQL, pero hemos escuchado que otras bases de datos SQL podrían no seguir esta regla. Por lo tanto, tenga cuidado al desarrollar aplicaciones que estén destinadas a ser portátiles.

Relacionado:

• Permitir null en columna única

Asumo quieres NULL valores en las 10 columnas que aceptan valores NULL para que se consideren iguales. Es elegante y práctico cubrir una sola columna anulable con un índice parcial adicional como se muestra aquí:

• Restricción única de varias columnas de PostgreSQL y valores NULL

Pero esto se sale de control rápidamente para columnas que aceptan valores NULL. Necesitaría un índice parcial para cada combinación distinta de columnas que aceptan valores NULL. Para solo 2 de esos, son 3 índices parciales para (a), (b) y (a,b). El número crece exponencialmente con 2^n - 1. Para sus 10 columnas que aceptan valores NULL, para cubrir todas las combinaciones posibles de valores NULL, ya necesitaría 1023 índices parciales. No vayas.

La solución simple: reemplace los valores NULL y defina las columnas involucradas NOT NULL, y todo funcionaría bien con un simple UNIQUE restricción.

Si esa no es una opción, sugiero un índice de expresión con COALESCE para reemplazar NULL en el índice:

CREATE UNIQUE INDEX test_upsert_solution_idx
    ON test_upsert (name, status, COALESCE(test_field, ''));

El vacío string ('') es un candidato obvio para los tipos de caracteres, pero puedes usar alguna valor legal que nunca aparece o se puede plegar con NULL según tu definición de “único”.

Entonces usa esta declaración:

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) 
VALUES ('shaun', 1, null        , 'ident', 11)  -- works with
     , ('bob'  , 2, 'test value', 'ident', 22)  -- and without NULL
ON     CONFLICT (name, status, COALESCE(test_field, '')) DO UPDATE  -- match expr. index
SET    count = COALESCE(tu.count + EXCLUDED.count, EXCLUDED.count, tu.count);

Como @ypercube, supongo que realmente quieres agregar count al recuento existente. Dado que la columna puede ser NULL, agregar NULL establecería la columna NULL. Si tu defines count NOT NULL, puedes simplificar.

---

Otra idea sería simplemente dejar caer el objetivo_conflicto de la declaración para cubrir todas las violaciones únicas. Luego, podría definir varios índices únicos para una definición más sofisticada de lo que se supone que es “único”. Pero eso no volará con ON CONFLICT DO UPDATE. El manual una vez más:

Para ON CONFLICT DO NOTHING, es opcional especificar un conflict_target; cuando se omite, se manejan los conflictos con todas las restricciones utilizables (e índices únicos). Para ON CONFLICT DO UPDATE, un objetivo_conflicto debe ser proporcionado.

Creo que el problema es que no tienes un índice parcial y el ON CONFLICT la sintaxis no coincide con la test_upsert_upsert_id_idx index pero la otra restricción única.

Si define el índice como parcial (con WHERE test_field IS NULL):

CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", status)
WHERE test_field IS NULL ;

y estas filas ya están en la tabla:

INSERT INTO test_upsert as tu
    (name, status, test_field, identifier, count) 
VALUES 
    ('shaun', 1, null, 'ident', 1),
    ('maria', 1, null, 'ident', 1) ;

entonces la consulta tendrá éxito:

INSERT INTO test_upsert as tu
    (name, status, test_field, identifier, count) 
VALUES 
    ('peter', 1,   17, 'ident', 1),
    ('shaun', 1, null, 'ident', 3),
    ('maria', 1, null, 'ident', 7)
ON CONFLICT 
    (name, status) WHERE test_field IS NULL   -- the conflicting condition
DO UPDATE SET
    count = tu.count + EXCLUDED.count 
WHERE                                         -- when to update
    tu.name = 'shaun' AND tu.status = 1 ;     -- if you don't want all of the
                                              -- updates to happen

con los siguientes resultados:

('peter', 1,   17, 'ident', 1)  -- no conflict: row inserted

('shaun', 1, null, 'ident', 3)  -- conflict: no insert
                           -- matches where: row updated with count = 1+3 = 4

('maria', 1, null, 'ident', 1)  -- conflict: no insert
                     -- doesn't match where: no update

Recuerda dar difusión a este escrito si te fue de ayuda.

---

---

Utiliza Nuestro Buscador