Re: Bug Report: INSERT ON CONFLICT sometimes does not work with partial index

On Sun, Feb 5, 2023 at 9:51 AM Danylo Miroshnichenko <
miroshnik(dot)dan(at)gmail(dot)com> wrote:

> CREATE UNIQUE INDEX IF NOT EXISTS uniq_id_test
> ON test USING btree (type, id)
> WHERE (type = 'Test');
> PREPARE test (text, int, text) AS
> INSERT INTO test (type, id)
> VALUES ($1, $2)
> ON CONFLICT (type, id)
> WHERE type = $3 DO
> UPDATE
> SET id = EXCLUDED.id;
>
>

> EXECUTE test('Test', 6, 'Test');
>
> The last EXECUTE statement always throws an error:
>
> [42P10] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
>
> All of it works fine if we replace $3 parameter with a constant value 'Test' (no errors):
>
> PREPARE test (text, int, text) AS
> INSERT INTO test (type, id)
> VALUES ($1, $2)
> ON CONFLICT (type, id)
> WHERE type = 'Test' DO
> UPDATE
> SET id = EXCLUDED.id;
>
>
This is a known deficiency that the first query ever works at all. It
should error every time.

https://www.postgresql.org/message-id/flat/CAKFQuwao4tL8gAww%3D%3DJXV66UB14GhhKDbQPg7CB8_JkyfMx%2Buw%40mail.gmail.com#3c60b77748d9b3ee9291f4632eb9a007
https://www.postgresql.org/message-id/flat/20170202140701.1401.31196%40wrigleys.postgresql.org

In short, consider the WHERE clause a part of query structure that has to
statically match one partial index specification.

David J.