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, Jun 03 '13 at 21:39
Check the `EXCLUDE` constraints. They can be set up, to act like case insensitive `UNIQUE`. — Igor Romanchenko, Jun 03 '13 at 21:44
@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, Jun 04 '13 at 08:06

score 19 · Accepted Answer · answered Jun 03 '13 at 23:17

19

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
);

answered Jun 03 '13 at 23:17

Erwin Brandstetter

479,275
111
893
1,042

Great, exactly what I want in this case (still hoped for some solution that allows for function in constraints ;) ). For Ubuntu users - `citext` is in `postgresql-contrib` package. – Code Painters Jun 04 '13 at 08:15
2

I should not had to add an extension to have a string type not case sensitive ... postgress should provide a better solution – Leonel Dec 01 '15 at 17:12
10

@Leonel: Your opinion is duly noted. Not sure how it translates to a downvote on my answer, though. If you have a specific problem to solve, consider posting a question, there are various cases that can be solved without installing the extension `citext`. But there's nothing wrong with installing it and it's really just a matter of `CREATE EXTENSION citext;`. The module is part of the distribution, maintained and released by the same team and tested just as thoroughly. The only reason it's not in the core release is because most people don't need it. It's "Postgres", btw, short for PostgreSQL. – Erwin Brandstetter Dec 02 '15 at 02:05

Deferrable, case-insensitive unique constraint

1 Answers1

Linked