Sample Header Ad - 728x90

In PostgreSQL, what is the `index_predicate` for in `INSERT`?

0 votes
1 answer
486 views
The [docs on INSERT](https://www.postgresql.org/docs/16/sql-insert.html) say: > If an index_predicate is specified, it must, as a further requirement for inference, satisfy arbiter indexes. Note that this means a non-partial unique index (a unique index without a predicate) will be inferred (and thus used by ON CONFLICT) if such an index satisfying every other criteria is available. I'm not sure I understand this. I **think** it's saying that if I have a non-partial index like this:
CREATE UNIQUE INDEX ON managers (department_id);
and I write an insert with ON CONFLICT (department_id), it will infer that it should use the index above. But if I have a partial index like this:
CREATE UNIQUE INDEX ON managers (department_id) WHERE active = true;
then I need to use the index_predicate to direct it to use the partial index, like ON CONFLICT (department_id) WHERE active = true. Is that correct?
Asked by Nathan Long (1005 rep)
Oct 25, 2023, 07:21 PM
Last activity: Oct 26, 2023, 05:10 AM