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
Last activity: Oct 26, 2023, 05:10 AM