4

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

4
  • Your fiddle doesn't match your question.
    – jjanes
    Jul 10, 2020 at 16:39
  • @jjanes - crap, must have forgotten to save. updated now. thanks!
    – Kyle
    Jul 10, 2020 at 18:48
  • 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! Jul 19, 2020 at 15:10
  • 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. Jul 19, 2020 at 15:19

1 Answer 1

4
+25

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.