Is it possible in PostgreSQL to create a deferrable unique constraint on a character column, but case-insensitive?
Let's assume the following basic table:
CREATE TABLE sample_table ( my_column VARCHAR(100) );
If deferrable constraint is not needed, it is as simple as creating unique index with function, e.g.:
CREATE UNIQUE INDEX my_unique_index ON sample_table(UPPER(my_column));
Deferred constraint check requires creating the constraint explicitly, e.g.:
ALTER TABLE sample_table ADD CONSTRAINT my_unique_constraint UNIQUE(my_column) DEFERRABLE INITIALLY IMMEDIATE;
And unfortunately it is not possible to use arbitrary functions in unique constraint.
One possible workaround would be to create additional column with the same content as
my_column, but upper case, updated via a trigger after each update/insert, then create a deferrable unique constraint on this artificial column. This, however, sounds like a really ugly hack.
Alternatively, it should be possible to use
CREATE CONSTRAINT TRIGGER and manually check for case-insensitive uniqueness (of course a regular index would still be necessary). This sounds a bit overcomplicated for such a simple (and popular, I suppose) requirement.
Is there any simpler and/or more elegant way around this limitation?