Postgres UPSERT row with null constraint value not working

Question

Im trying to write a stored procedure in which I can upsert a row even if one of the values in the key is null. I've read the documentation and found that Postgres doesn't work with comparing equality of null values.

I've also read other forum posts and noticed that what I want to achieve can be done through a partial index. I'm able to successfully get a constraint violation, however my "on conflict" never gets hit when i pass in a value that has a null birthday.

I want to be able to pass in a null birthday and update an ID for a person even if their birthday is null.

(
    id bigint not null,
    name text,
    birthday date
);

I create an index and partial index so that it allows birthday to be null

CREATE UNIQUE INDEX name_birthday_key on people (name, birthday);
CREATE UNIQUE INDEX birthday_null_key on people (name) where birthday is null;

create or replace procedure store_person(_identifier bigint, _name character varying, _birthday date)
    language plpgsql
as
$$
begin
   insert into people (
    id, name, birthday
    )
    values (
    _identifier, _name, _birthday
    )
    on conflict (name, birthday)
    do update
    set
        id = _identifier
        where people.birthday = _date and people.name = _name;
end
$$;

if I run:

call public.store_person(1, 'Bob', '1955-01-09')
call public.store_person(2, 'Bob', '1955-01-09')

i successfully see that the only row in the DB is Bob with an ID of 2. however, if i run

call public.store_person(3, 'Joe', null)
call public.store_person(4, 'Joe', null)

the only row i get is ID 3. the second insert for ID 4 never updates the existing row. I do get a violation error but the "on conflict" update never is hit.

can someone point me in the right direction of how to do this?

Vesa Karjalainen · Accepted Answer · 2020-09-04 20:11:17Z

1

The CONFLICT doesn't match because NULLis not equal to NULL. This is not a PostgreSQL thing, it's defined in SQL standard.

Use something like COALESCE(birthday, '0001-01-01') when inserting your data, that will match; and remove the partial index.

Your code has an error, in DO UPDATE...WHERE: there's nothing named _date, should be _birthday.

answered Sep 4, 2020 at 20:11

Vesa Karjalainen

1,1058 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres UPSERT row with null constraint value not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related