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?
I am using Postgres 9.1 at the moment. But solutions for newer are welcome.

Check the EXCLUDE constraints. They can be set up, to act like case insensitive UNIQUE. — Ihor Romanchenko
– Ihor Romanchenko, Commented Jun 3, 2013 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
– Code Painters, Commented Jun 4, 2013 at 8:06

Erwin Brandstetter · Accepted Answer · 2023-10-26 11:08:19Z

22

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

edited Oct 26, 2023 at 11:08

answered Jun 3, 2013 at 23:17

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Code Painters Over a year ago

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.

Leonel Over a year ago

I should not had to add an extension to have a string type not case sensitive ... postgress should provide a better solution

Erwin Brandstetter Over a year ago

@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.

Greg Over a year ago

What's your view on the practical performance overhead of citext when used with a unique constraint? Since it seems to call C str_tolower() internally: stackoverflow.com/questions/31133603/….

Collectives™ on Stack Overflow

Deferrable, case-insensitive unique constraint

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related