Constraint UNIQUE on a column not case sensitive - Mailing list pgsql-sql

Hi,

I would like to find an efficient solution for adding/implementing a constraint UNIQUE on a VARCHAR column not case
sensitive:

ALTER TABLE MyTableADD CONSTRAINT UNQ_MyTable_MyColumn     UNIQUE (lower(MyColumn));       -- invalid syntax

The idea is to have an index on that column, in a not case sensitive form, i.e. lower(MyColumn).

SELECT * FROM MyTable WHERE lower(MyColumn) = lower('...');

I don't know how to add such a constraint on MyTable except by defining a trigger on INSERT clause and checking whether
lower(:NEW.MyColumn)has been already inserted in MyTable.
 

Is there better and more efficient way to do that?

Regards,


--
Daniel