pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConstraint UNIQUE on a column not case sensitive
4+ messages / 3 participants
[nested] [flat]
* Constraint UNIQUE on a column not case sensitive
@ 2006-07-01 13:47 Daniel CAUNE <d.caune@free.fr>
2006-07-01 14:01 ` Re: Constraint UNIQUE on a column not case sensitive Michael Glaesemann <grzm@seespotcode.net>
2006-07-01 14:04 ` Re: Constraint UNIQUE on a column not case sensitive Jorge Godoy <jgodoy@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Daniel CAUNE @ 2006-07-01 13:47 UTC (permalink / raw)
To: 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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Constraint UNIQUE on a column not case sensitive
2006-07-01 13:47 Constraint UNIQUE on a column not case sensitive Daniel CAUNE <d.caune@free.fr>
@ 2006-07-01 14:01 ` Michael Glaesemann <grzm@seespotcode.net>
2006-07-01 15:48 ` Re: Constraint UNIQUE on a column not case sensitive Daniel CAUNE <d.caune@free.fr>
1 sibling, 1 reply; 4+ messages in thread
From: Michael Glaesemann @ 2006-07-01 14:01 UTC (permalink / raw)
To: Daniel CAUNE <d.caune@free.fr>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Constraint UNIQUE on a column not case sensitive
2006-07-01 13:47 Constraint UNIQUE on a column not case sensitive Daniel CAUNE <d.caune@free.fr>
2006-07-01 14:01 ` Re: Constraint UNIQUE on a column not case sensitive Michael Glaesemann <grzm@seespotcode.net>
@ 2006-07-01 15:48 ` Daniel CAUNE <d.caune@free.fr>
0 siblings, 0 replies; 4+ messages in thread
From: Daniel CAUNE @ 2006-07-01 15:48 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; Jorge Godoy <jgodoy@gmail.com>; +Cc: pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Constraint UNIQUE on a column not case sensitive
2006-07-01 13:47 Constraint UNIQUE on a column not case sensitive Daniel CAUNE <d.caune@free.fr>
@ 2006-07-01 14:04 ` Jorge Godoy <jgodoy@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jorge Godoy @ 2006-07-01 14:04 UTC (permalink / raw)
To: Daniel CAUNE <d.caune@free.fr>; +Cc: pgsql-sql
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>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-07-01 15:48 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-01 13:47 Constraint UNIQUE on a column not case sensitive Daniel CAUNE <d.caune@free.fr>
2006-07-01 14:01 ` Michael Glaesemann <grzm@seespotcode.net>
2006-07-01 15:48 ` Daniel CAUNE <d.caune@free.fr>
2006-07-01 14:04 ` Jorge Godoy <jgodoy@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox