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.

Answer 1

There is a couple of things at play here.

1. Creating a unique index doesn't automatically create a unique constraint (despite the term constraint being used in the error message when you tried to insert duplicate records). On the other hand, adding a unique constraint will automatically create a unique B-tree index on the column or group of columns (see this doc).

2. Unlike in the case of regular indexes, constraints cannot be added using partial indexes:

   ALTER TABLE x ADD CONSTRAINT uniq_x_a_constraint UNIQUE USING INDEX uniq_x_a;
   ERROR:  "uniq_x_a" is a partial index ...
   DETAIL:  Cannot create a primary key or unique constraint using such an index.
   

In essence, you'll need to create a separate unique constraint on (a, b) (unrelated to the partial indexes) in order to make ON CONFLICT function in the expected way.

[UPDATE]

Even though combination of a and b is unique, ON CONFLICT won't see any valid constraint enforcing the uniqueness:

INSERT INTO x (a, b, c) VALUES ('hello', 'there', 'c4') 
ON CONFLICT (a, b) DO UPDATE SET c = excluded.c;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

Now, if you add a constraint, both ON CONFLICT (a, b) and ON CONFLICT ON CONSTRAINT will work:

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

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

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