Date: Sat, 01 Jul 2006 09:47:59 -0400
From: Daniel CAUNE <d.caune@free.fr>
Subject: Constraint UNIQUE on a column not case sensitive
To: pgsql-sql@postgresql.org
Message-id: <0J1Q008ME8ZX7M90@VL-MO-MR003.ip.videotron.ca>
MIME-version: 1.0
Content-type: text/plain; charset=UTF-8
Content-transfer-encoding: 7BIT
Thread-index: AcadFPZ+cCBofkzMSwOynUgrJR4khA==

Hi,

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

ALTER TABLE MyTable
 ADD 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