postgresql 9.5 upsert issue with nulls - Mailing list pgsql-general

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