Trying to upsert a table with partial index

Question

So I have a table with structure similar to:

CREATE TABLE x (
    id SERIAL,
    a character varying(1024) NOT NULL,
    b character varying(2048),
    c character varying(1024)
);

CREATE UNIQUE INDEX uniq_x_a ON x USING btree (a) WHERE (b IS NULL);
CREATE UNIQUE INDEX uniq_x_a_b ON x USING btree (a, b) WHERE (b IS NOT NULL);

Now I upgraded this instance to 9.5 and want to use the ON CONFLICT DO UPDATE.

So executing this

INSERT INTO x (a, b, c) VALUES ('hello', 'there', 'c1');
INSERT INTO x (a, b, c) VALUES ('hello', 'there', 'c2');

now gives me

ERROR:  duplicate key value violates unique constraint "uniq_x_a_b"
DETAIL:  Key (a, b)=(hello, there) already exists.

Then when I do

INSERT INTO x (a, b, c) VALUES ('hello', 'there', 'c4') 
ON CONFLICT ON CONSTRAINT uniq_x_a_b DO UPDATE SET c = excluded.c;

I get

ERROR:  constraint "uniq_x_a_b" for table "x" does not exist

I don't understand, I'm violating a non existing constraint? Anybody has a tip?

Edit

Per suggestion I added

ALTER TABLE x ADD CONSTRAINT uniq_x_a_b_constraint UNIQUE (a, b);

So this now works for the following case:

INSERT INTO x (a, b, c) VALUES ('hello', 'there', 'c3') 
ON CONFLICT ON CONSTRAINT uniq_x_a_b_constraint DO UPDATE SET c = excluded.c;

But fails for

INSERT INTO x (a, b, c) VALUES ('hello', NULL, 'c3') 
ON CONFLICT ON CONSTRAINT uniq_x_a_b_constraint DO UPDATE SET c = excluded.c;

with

ERROR:  duplicate key value violates unique constraint "uniq_x_a"
DETAIL:  Key (a)=(hello) already exists.

And I can't add a unique constraint on column a alone because it's the combination of a and b that is supposed to be unique. It also seems impossible to construct a constraint on a subset of the rows like it's possible with creating an index.

Trying to upsert a table with partial index

1 Answers