Deferrable, case-insensitive unique constraint

Question

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?

It's 9.1 at the moment. Yet if there's a nice solution available on newer versions I'd be perfectly happy to read about it :) — Code Painters
Check the EXCLUDE constraints. They can be set up, to act like case insensitive UNIQUE. — Ihor Romanchenko
@Igor Thanks, that's very interesting - I wasn't aware of EXCLUDE constraints (perhaps didn't spend enough time with 9.x docs). I find it very useful in other context as well. — Code Painters

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2013-06-03T23:17:56

You can circumvent the restriction by using the special type citext provided by the additional module of the same name. Quoting the manual:

The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text.

It addresses your case exactly. Run once per database:

CREATE EXTENSION citext;

Then you can:

CREATE TABLE sample_table ( 
   my_column citext
  ,CONSTRAINT my_unique_constraint UNIQUE(my_column)
   DEFERRABLE INITIALLY IMMEDIATE
);

Deferrable, case-insensitive unique constraint

1 Answers