Início / dba / Perguntas / 320371
Accepted
Don Draper
Asked: 2022-11-30 23:00:40 +0800 CST

UPSERT baseado na combinação única de (INTEGER, NULL), (NULL, INTEGER)

  • 772

Minha tabela, para simplificar, ficou assim:

id | foreign_key_1 | foreign_key_2 | value

Como fica claro pelos nomes, os campos foreign_key_1e foreign_key_2referem-se a PKs em duas outras tabelas.

O complicado aqui é que apenas um deles pode ser não nulo ao mesmo tempo, então também tenho as seguintes restrições:

UNIQUE (foreign_key_1),
UNIQUE (foreign_key_2),
CHECK ((foreign_key_1 IS NOT NULL AND foreign_key_2 IS NULL) 
    OR (foreign_key_1 IS NULL AND foreign_key_2 IS NOT NULL))

foreign_key_1Essas verificações garantem a integridade que eu queria, mas agora também quero excluir a entrada existente foreign_key_2antes de inserir uma nova entrada.

DELETE FROM my_table
WHERE ($1::INTEGER IS NULL OR foreign_key_1 = $1::INTEGER)
    AND ($2::INTEGER is NULL OR foreign_key_2 = $2::INTEGER)

Inicialmente, a ideia era ter apenas uma consulta que pudesse tratar ambas (foreign_key_1, NULL)e (NULL, foreign_key_2)passar como argumentos.

A principal preocupação é que a execução da instrução delete pode ser bem-sucedida, enquanto a inserção pode falhar e, então, a tabela ficará em um estado inválido.

Parece que usar uma transação aqui é o caminho a percorrer, mas da perspectiva da organização do código, será necessária muita refatoração agora para agrupar ambas as chamadas para repositórios na mesma transação.

Minha próxima ideia foi usar um UPSERT eON CONFLICT(target) DO UPDATE...

Não tenho uma única restrição aqui para usar como destino e a versão que estou usando (Postgres 14) ainda não possui a cláusula NULLS NOT DISTINCT.

Para reiterar, gostaria de ter uma interseção de:

  • UNIQUE(foreign_key_1)
  • UNIQUE(foreign_key_2)
  • CHECK ((foreign_key_1 IS NOT NULL AND foreign_key_2 IS NULL) OR (foreign_key_1 IS NULL AND foreign_key_2 IS NOT NULL))

... para que possa ser usado como alvo ao ON CONFLICTfazer um UPSERT. Isso é possível de alguma forma?

No geral, meu entendimento é que, nessa situação, as opções são:

  • Refatorando e executando as duas instruções em uma transação [muita refatoração]
  • Encontrar uma maneira de ter um único alvo para a ON CONFLICTcláusula
  • Criando um CTE inteligente que excluirá a linha existente primeiro
  • algo mais

Quais são suas sugestões?

Atualizar

Depois de pensar um pouco, provavelmente posso usar:

CREATE UNIQUE INDEX one_null_idx ON my_table(COALESCE(foreign_key_1, -1), COALESCE(foreign_key_2, -1));

A restrição de verificação para garantir apenas uma chave estrangeira não nula também é necessária aqui.

Dado que as chaves estrangeiras são seriais e nunca podem ser -1, parece que isso pode funcionar. O que você acha?

postgresql

1 respostas

  1. Best Answer

    Você precisa de um único "conflict_target" para ON CONFLICT DO UPDATE, então sua solução "Atualizar" é a melhor solução alternativa:

    CREATE UNIQUE INDEX my_table_one_null_idx ON my_table(COALESCE(foreign_key_1, -1), COALESCE(foreign_key_2, -1));

    ... enquanto você não pode usar NULLS NOT DISTINCTno Postgres 15. Veja:

    Mas:

    Dado que as chaves estrangeiras são seriais e nunca podem ser -1,

    serialas colunas nunca são negativas por padrão . Mas nada impede que você insira um número negativo manualmente. Aplique isso com CHECKrestrições. Além disso, use o mais simples num_nulls()para sua outra restrição:

    ...
, CONSTRAINT fk1_never_minus1 CHECK (foreign_key_1 <> -1)
, CONSTRAINT fk2_never_minus1 CHECK (foreign_key_2 <> -1)
, CONSTRAINT fk1_fk2_one_null CHECK (num_nulls(foreign_key_1, foreign_key_2) = 1)
...

    Ver:

    Alternativa

    Existe outra alternativa com vários CTEs em uma única consulta. Ver:

    • 3

relate perguntas