Hello,
I'm having an issue with using the new UPSERT feature in Postgres 9.5
I have a table that is used for aggregating data from another table. The
composite key is made up of 20 columns, 10 of which can be nullable.
Below I have created a smaller version of the issue i'm having, specifically
with NULL values.
CREATE TABLE public.test_upsert
(
upsert_id integer NOT NULL DEFAULT
nextval('test_upsert_upsert_id_seq'::regclass),
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)
)
WITH (
OIDS=FALSE
);
Running this query works as needed (First insert, then subsequent inserts
simply increment the count):
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';
However if I run this query, 1 row is inserted each time rather than
incrementing the count for the initial row:
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;
This is my issue. I need to simply increment the count value and not create
multiple identical rows with null values.
Attempting to add a partial unique index:
CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", status, test_field, identifier);
However this yields the same results, either multiple null rows being
inserted or "ERROR: there is no unique or exclusion constraint matching the ON
CONFLICT specification" messages being returned when trying to insert.
I already attempted to add extra details on the partial index such as WHERE
test_field is not null OR identifier is not null, however when inserting I get
the constraint error message.
Any suggestions would be appreciated, thanks!
-Shaun
This e-mail may be privileged and/or confidential, and the sender does not
waive any related rights and obligations. Any distribution, use or copying of
this e-mail or the information it contains by other than an intended recipient
is unauthorized. If you received this e-mail in error, please advise me (by
return e-mail or otherwise) immediately. Ce courrier ?lectronique est
confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits et obligations
qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des
renseignements qu'il contient par une personne autre que le (les)
destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courrier
?lectronique par erreur, veuillez m'en aviser imm?diatement, par retour de
courrier ?lectronique ou par un autre moyen.
