postgresql 9.5 upsert issue with nulls

[Shaun McCready <Shaun.McCready@xxxxxxxxxxxx>]

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.