Problema UPSERT do PostgreSQL com valores NULL

esclarecer o ON CONFLICT DO UPDATEcomportamento

Considere o manual aqui :

Para cada linha individual proposta para inserção, a inserção continua ou, se uma restrição de árbitro ou índice especificado por conflict_targetfor violado, a alternativa conflict_actioné tomada.

Ênfase em negrito minha. Portanto, você não precisa repetir predicados para colunas incluídas no índice exclusivo na WHEREcláusula para o UPDATE(o 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'

A violação única já estabelece o que sua WHEREcláusula adicionada aplicaria de forma redundante.

Esclarecer índice parcial

Adicione uma WHEREcláusula para torná-lo um índice parcial real, como você mesmo mencionou (mas com 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 em seu UPSERT, você precisa de uma correspondência como @ypercube demonstra :conflict_target

ON CONFLICT (name, status) WHERE test_field IS NULL

Agora o índice parcial acima é inferido. No entanto , como o manual também observa :

[...] um índice exclusivo não parcial (um índice exclusivo sem um predicado) será inferido (e, portanto, usado por ON CONFLICT) se um índice que satisfaça todos os outros critérios estiver disponível.

Se você tiver um índice adicional (ou apenas) apenas (name, status)ele será (também) usado. Um índice em (name, status, test_field)explicitamente não seria inferido. Isso não explica seu problema, mas pode ter aumentado a confusão durante o teste.

Soluções

AIUI, nenhuma das opções acima resolve seu problema , ainda. Com o índice parcial, apenas casos especiais com valores NULL correspondentes seriam capturados. E outras linhas duplicadas seriam inseridas se você não tiver outros índices/restrições exclusivos correspondentes ou levantaria uma exceção se tivesse. Acho que não é isso que você quer. Você escreve:

A chave composta é composta por 20 colunas, 10 das quais podem ser anuláveis.

O que exatamente você considera uma duplicata? Postgres (de acordo com o padrão SQL) não considera dois valores NULL iguais. O manual:

Em geral, uma restrição única é violada se houver mais de uma linha na tabela onde os valores de todas as colunas incluídas na restrição são iguais. No entanto, dois valores nulos nunca são considerados iguais nesta comparação. Isso significa que, mesmo na presença de uma restrição exclusiva, é possível armazenar linhas duplicadas que contenham um valor nulo em pelo menos uma das colunas restritas. Esse comportamento está de acordo com o padrão SQL, mas ouvimos dizer que outros bancos de dados SQL podem não seguir essa regra. Portanto, tenha cuidado ao desenvolver aplicativos destinados a serem portáteis.

Relacionado:

• Permitir nulo em coluna única

Suponho que você deseja queNULLos valores em todas as 10 colunas anuláveis ​​sejam considerados iguais. É elegante e prático cobrir uma única coluna anulável com um índice parcial adicional, como demonstrado aqui:

• Restrição exclusiva de várias colunas do PostgreSQL e valores NULL

Mas isso fica fora de controle rapidamente para mais colunas anuláveis. Você precisaria de um índice parcial para cada combinação distinta de colunas anuláveis. Para apenas 2 desses, são 3 índices parciais para (a), (b)e (a,b). O número está crescendo exponencialmente com 2^n - 1. Para suas 10 colunas anuláveis, para cobrir todas as combinações possíveis de valores NULL, você já precisaria de 1.023 índices parciais. Não vá.

A solução simples: substitua os valores NULL e defina as colunas envolvidas NOT NULL, e tudo funcionaria bem com uma UNIQUErestrição simples.

Se essa não for uma opção, sugiro uma expressão index com COALESCEpara substituir NULL no índice:

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

A string vazia ( '') é um candidato óbvio para tipos de caracteres, mas você pode usar qualquer valor legal que nunca apareça ou que possa ser dobrado com NULL de acordo com sua definição de "único".

Em seguida, use esta declaração:

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, suponho que você realmente deseja adicionar countao arquivo count. Como a coluna pode ser NULL, adicionar NULL definiria a coluna NULL. Se você definir count NOT NULL, poderá simplificar.}

Ou considere esta resposta posterior relacionada usando um valor de hash para a linha:

• Por que minha restrição UNIQUE não é acionada?

Uma ideia diferente seria apenas eliminar o conflict_target da instrução para cobrir todas as violações exclusivas . Em seguida, você pode definir vários índices exclusivos para uma definição mais sofisticada do que deveria ser "exclusivo". Mas isso não vai voar com ON CONFLICT DO UPDATE. O manual mais uma vez:

Para ON CONFLICT DO NOTHING, é opcional especificar um conflict_target; quando omitido, os conflitos com todas as restrições utilizáveis ​​(e índices exclusivos) são tratados. Para ON CONFLICT DO UPDATE, um conflict_target deve ser fornecido.

Acho que o problema é que você não tem um índice parcial e a ON CONFLICTsintaxe não corresponde ao test_upsert_upsert_id_idxíndice, mas à outra restrição exclusiva.

Se você definir o índice como parcial (com 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 ;

e essas linhas já na tabela:

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

então a query terá sucesso:

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

com os seguintes 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