Thread: Constraint UNIQUE on a column not case sensitive

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

On Jul 1, 2006, at 22:47 , Daniel CAUNE wrote:

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

I think you're really close. Try

CREATE UNIQUE INDEX ci_mycolumn_idx ON mytable(lower(mycolumn));

Does that do what you're looking for?

Michael Glaesemann
grzm seespotcode net

Daniel CAUNE <d.caune@free.fr> writes:

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

What's the problem with CREATE INDEX then? 

CREATE INDEX idx_something ON mytable (lower(mycolumn));

-- 
Jorge Godoy      <jgodoy@gmail.com>

> -----Message d'origine-----
> De : Michael Glaesemann [mailto:grzm@seespotcode.net]
> Envoyé : samedi 1 juillet 2006 10:01
> À : Daniel CAUNE
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Constraint UNIQUE on a column not case sensitive
>
>
> On Jul 1, 2006, at 22:47 , Daniel CAUNE wrote:
>
> > The idea is to have an index on that column, in a not case
> > sensitive form, i.e. lower(MyColumn).
>
> I think you're really close. Try
>
> CREATE UNIQUE INDEX ci_mycolumn_idx ON mytable(lower(mycolumn));
>
> Does that do what you're looking for?
>
> Michael Glaesemann
> grzm seespotcode net
>
>

Yes, indeed!  I'm stupid!  I was searching a constraint form while the creation of an UNIQUE index makes the job too!

The following form is not valid:

ALTER TABLE MyTable ADD CONSTRAINT UNQ_MyTable_MyColumn     UNIQUE (lower(MyColumn));

But your form makes the expected job:

CREATE UNIQUE INDEX IDX_MyTable_MyColumn ON MyTable (lower(MyColumn));

Thanks,

--
Daniel CAUNE