POSTGRES - Handling several ON CONFLICT constraints/indexes

Question

Have the following in the table where:

CONSTRAINT unique_position UNIQUE (id,city,type)

and

CREATE UNIQUE INDEX unique_position_sat_null ON public."position" (id,city) where type is null

on insert conflict there is currently:

ON CONFLICT ON CONSTRAINT unique_position DO UPDATE SET
        .....

How can I incorporate both unique_position and unique_position_sat_null into ON CONFLICT ON CONSTRAINT

Tried:

ON CONFLICT ON CONSTRAINT unique_position DO UPDATE SET
            .....,
ON CONFLICT ON CONSTRAINT unique_position_sat_null DO UPDATE SET
            .....,

and

ON CONFLICT ON CONSTRAINT unique_position and unique_position_sat_null  DO UPDATE SET
            .....,

Thank you.

Answer 1

Unfortunately, unique indexes and unique constraints are not the same things in Postgres.

When you define a unique index, not constraint, Postgres doesn't create a constraint with the same name (in case of collisions during INSERTs it will produce error "unique constraint violation" mentioning index' name, but this is a minor bug, see https://www.postgresql.org/message-id/flat/CAH2-Wzn-uXcLgC5uFbqe2rUfmJWP9AxKnMKAEgqU26hbURxk5A%40mail.gmail.com#CAH2-Wzn-uXcLgC5uFbqe2rUfmJWP9AxKnMKAEgqU26hbURxk5A@mail.gmail.com)

So you cannot use index name like it is a constraint name.

You could do ALTER TABLE .. ADD CONSTRAINT .. UNIQUE, but it cannot be unique.

So this can be considered as a not yet implemented feature in Postgres -- either "ON CONFLICT" clause needs to learn how to use index names or ALTER TABLE .. ADD CONSTRAINT .. UNIQUE needs to be patched to allow partial constraints.

I suggest discussing it in pgsql-hackers@ mailing list (e.g. if you answer thread mentioned above, explaining your problem, it would be awesome).