Cannot use "ON CONFLICT" with postgres updatable view and partial index

Question

I have an updatable view pointing to an underlying table that has a partial index. It looks something like this

CREATE TABLE if not exists foo (
 a INT,
 b INT,
 x INT,
 y INT,
 z BOOLEAN,
 CONSTRAINT x_or_y CHECK (
 (z and x is not null and y is null)
 or 
 (not z and x is null and y is not null)
 )
);
CREATE UNIQUE INDEX ux ON foo (x, a) WHERE z=TRUE;
CREATE UNIQUE INDEX uy ON foo (y, a) WHERE z=FALSE;
CREATE OR REPLACE VIEW foo_view AS 
 SELECT * FROM foo;

That is, for each row, y must be null if z is true; x must be null if z is false; and, only one of x and y may be not null. (x, a) and (y, a) must be unique. (Sorry if this is overly complicated. I'm translating from my real table that has a lot of other cruft.)

My problem comes when I want to update with ON CONFLICT. I believe I ought to be able to do this.

INSERT INTO foo_view(x, y, a, b, z)
 VALUES
 (5, null, 1, 2, true),
 (null, 5, 1, 2, false);
 
 
select * from foo_view;
INSERT INTO foo_view(x, y, a, b, z)
 VALUES
 (5, null, 1, 2, true)
ON CONFLICT (x, a) where z=true
 DO UPDATE
 set b = EXCLUDED.b;

But, I get the exception:

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

I can insert into foo instead of foo_view with the same ON CONFLICT without error.

Here is a fiddle: https://www.db-fiddle.com/f/cX2HXg91Q7yKoPeMBYzVLg/0

• jjanes: Your fiddle doesn't match your question.
• Kyle: @jjanes - crap, must have forgotten to save. updated now. thanks!
• Erwin Brandstetter: The fiddle still doesn't match the the question - and it seems wrong. I suggest this test case to make your point clear (in the question and as fiddle): dbfiddle.uk/… Also, please provide your version of Postgres - for any such question!
• Erwin Brandstetter: The problem with updatable views aside, since x and y are mutually exclusive in your design, you might just merge both columns to something called xy and have a plain UNIQUE constraint on (xy, z) and both columns NOT NULL.

Answer 1

After debugging through the PostgreSQL code, I'd say that there is a problem in the infer_arbiter_indexes function in src/backend/optimizer/util/plancat.c. It compares the index predicate to the WHERE condition in ON CONFLICT, but seems to ignore that they are on different objects.

I'm not sure if PostgreSQL is willing to consider that as a bug, but you might report it.