Re: postgresql 9.5 upsert issue with nulls

On Tue, Oct 04, 2016 at 07:46:48PM +0000, Shaun McCready wrote:
> 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

Hi Shaun,

NULL <> NULL so each insert with a NULL will create a new row. A NULL value is defined to be
an unknown value so two INSERTs of:

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) VALUES ('shaun',1,null,'ident', 1)
INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) VALUES ('shaun',1,null,'ident', 1)

are inserting different rows. You might want to change your NULL to the empty string
or some other fixed token if you actually want them to work as equal.

Regards,
Ken