UPSERT - INSERT ... ON CONFLICT fails with a function based index as 'lower()' unique constraint

Question

I have a database table with a code column that uses a lowercase index to prevent code values that only differ in case (e.g. 'XYZ' = 'xYZ' = 'xyz'). The typical way in Postgresql is to create a function based index, like this: CREATE UNIQUE INDEX mytable_lower_code_idx ON mytable (lower(code)).

Now I have a case where I need upsert behaviour on that column:

-- first insert
INSERT INTO mytable (code) VALUES ('abcd');

-- second insert, with upsert behaviour
INSERT INTO mytable (code) VALUES ('Abcd')
  ON CONFLICT (code) DO UPDATE
  SET code='Abcd';

For the second insert I get a unique key violation: ERROR: duplicate key value violates unique constraint "mytable_lower_code_idx"

(I also tried to use ON CONFLICT ON CONSTRAINT mytable_lower_code_idx but Postgresql tells me that this constraint does not exist so maybe it doesn't treat the index as a constraint.)

My final question: Is there any way to make INSERT ... ON CONFLICT work together with indexes on expressions? Or must I introduce a physical indexed lowercase column to accomplish the task?

Answer 1

Use ON CONFLICT (lower(code)) DO UPDATE:

CREATE TABLE mytable (
    code text
);
CREATE UNIQUE INDEX mytable_lower_code_idx ON mytable (lower(code));
INSERT INTO mytable VALUES ('abcd');

INSERT INTO mytable (code) VALUES ('Abcd')
  ON CONFLICT (lower(code)) DO UPDATE
  SET code='Abcd';

SELECT * FROM mytable;

yields

| code |
|------|
| Abcd |

---

Note that ON CONFLICT syntax allows for the conflict target to be an index_expression (my emphasis):

ON CONFLICT conflict_target
where conflict_target can be one of:

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

and index_expression:

Similar to index_column_name, but used to infer expressions on table_name columns appearing within index definitions (not simple columns). Follows CREATE INDEX format.