Estou tendo um problema ao usar o novo recurso UPSERT no Postgres 9.5
Eu tenho uma tabela que é usada para agregar dados de outra tabela. A chave composta é composta por 20 colunas, 10 das quais podem ser anuláveis. Abaixo, criei uma versão menor do problema que estou tendo, especificamente com valores NULL.
CREATE TABLE public.test_upsert (
upsert_id serial,
name character varying(32) NOT NULL,
status integer NOT NULL,
test_field text,
identifier character varying(255),
count integer,
CONSTRAINT upsert_id_pkey PRIMARY KEY (upsert_id),
CONSTRAINT test_upsert_name_status_test_field_key UNIQUE (name, status, test_field)
);
A execução desta consulta funciona conforme necessário (primeiro insira, depois as inserções subsequentes simplesmente incrementam a contagem):
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';
No entanto, se eu executar esta consulta, 1 linha será inserida a cada vez, em vez de incrementar a contagem da linha inicial:
INSERT INTO test_upsert as tu(name,status,test_field,identifier, count)
VALUES ('shaun',1,null,'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 = null;
Este é o meu problema. Preciso simplesmente incrementar o valor de contagem e não criar várias linhas idênticas com valores nulos.
Tentando adicionar um índice exclusivo parcial:
CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", status, test_field, identifier);
No entanto, isso produz os mesmos resultados, várias linhas nulas sendo inseridas ou esta mensagem de erro ao tentar inserir:
ERRO: não há restrição exclusiva ou de exclusão correspondente à especificação ON CONFLICT
Já tentei adicionar detalhes extras no índice parcial, como WHERE test_field is not null OR identifier is not null. No entanto, ao inserir, recebo a mensagem de erro de restrição.