How to use multiple "unique index inferences" in a Postgresql query

Question

In a Postgresql 9.6 DB, there is a existing table named X that has four columns, a, b, c, and d with indices setup like this:

"uidx_a_b" UNIQUE, btree (a, b) WHERE c IS NULL AND d IS NULL
"uidx_a_c" UNIQUE, btree (a, b, c) WHERE c IS NOT NULL AND d IS NULL
"uidx_a_d" UNIQUE, btree (a, b, c, d) WHERE c IS NOT NULL AND d IS NOT NULL

I don't know why this was done as it was done by someone long gone and before I had to modify it.

I am trying to get the syntax correct for specifying all three of these in an ON CONFLICT statement. I tried every variation I could think of all with error. The Postgresql Documentation indicates this is possible, specifically the [, ...] described in the conflict_target here:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )

Also, this blog from one of the committers says so. Finally, I looked at this unit test for the functionality again to no avail! Having thus given up, I am turning to SO to seek help.

This is what I believe is the closest syntax I tried that should work:

ON CONFLICT (
((a, b) WHERE c IS NULL AND d IS NULL),
((a, b, c) WHERE c IS NOT NULL AND d IS NULL),
((a, b, c, d) WHERE release_id IS NOT NULL AND d IS NOT NULL)
)

However this fails with:

ERROR: syntax error at or near ","

While I am open to suggestions to improve the design of those indices, I really want to know if there is a valid syntax for specifying the ON CONFLICT clause as it seems there should be!

Dirty data-hack: make the {c,d} columns not NULLable and use a special value (-1 or 0) to indicate NULLness. Now you only need the unique index on (a,b,c,d) . The case with c IS NULL and d NOT IS NULL should be handled specially. — joop, Mar 01 '18 at 13:30
@joop I think that is a pretty good idea - might improve performance too, would certainly save some data as these actual tables are large! Still want to know about the syntax though.... — JoeG, Mar 01 '18 at 13:33
@a_horse_with_no_name - I updated the question to indicate where it seems to say multiple conditions ARE supported. Maybe they aren't though? — JoeG, Mar 01 '18 at 13:34
Think before you act: if the {c,d} columns are FK's (which I suspect) you'll also need dummy-rows in the referred tables. **Plus:** additional code to suppress/handle these in queries. — joop, Mar 01 '18 at 13:38
Just to clarify why these multiple indexes were needed, see this question: https://stackoverflow.com/q/8289100/157957 Basically, a normal unique constraint will allow any number of NULLs, because they aren't considered equal. — IMSoP, Mar 01 '18 at 14:25

score 1 · Accepted Answer · answered Mar 01 '18 at 14:18

In the following I am referring to the syntax description from the documentation:

    ON CONFLICT [ conflict_target ] conflict_action

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) }
            [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

INSERT ... ON CONFLICT allows only a single conflict_target. The [, ...] means that more than one column or expression can be specified (to indicate a single condition), like this:

ON CONFLICT (col1, (col2::text), col3)

Moreover, if it is a partial index, the WHERE condition must be implied by index_predicate.

So what can you do?

You can follow the advice from @joop and find a value that cannot occur in columns c and d.

Then you can replace your three indexes with:

CREATE UNIQUE INDEX ON x (a, b, coalesce(c, -1), coalesce(d, -1));

The conflict_target would then become:

ON CONFLICT (a, b, coalesce(c, -1), coalesce(d, -1))

How to use multiple "unique index inferences" in a Postgresql query

1 Answers1