From: Andreas <maps.on@gmx.net>
To: Joshua Tolley <eggyknap@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Howto have a unique restraint on UPPER (textfield)
Date: Sun, 31 Jan 2010 21:48:28 +0100
Message-ID: <4B65EC9C.6000606@gmx.net> (raw)
In-Reply-To: <4b64f184.9613f30a.5d13.01b2@mx.google.com>
References: <4B64EA46.60605@gmx.net>
<4b64f184.9613f30a.5d13.01b2@mx.google.com>
Joshua Tolley schrieb:
> On Sun, Jan 31, 2010 at 03:26:14AM +0100, Andreas wrote:
>
>> Hi,
>>
>> is there a way to define a unique restraint on UPPER (textfield)?
>>
>> E.g. mytable (
>> name_id serial PRIMARY KEY,
>> name varchar(255),
>> UNIQUE ( upper (name) )
>> )
>>
>> psql throws a syntax error because of the upper() function.
>>
>> I need to prohibit that 2 of strings like cow, Cow, CoW appears in
>> the name-column.
>>
>
> Like this:
>
> 5432 josh@josh# create table c (d text);
> CREATE TABLE
> 5432 josh@josh*# create unique index c_ix on c (upper(d));
> CREATE INDEX
> 5432 josh@josh*# insert into c (d) values ('text');
> INSERT 0 1
> 5432 josh@josh*# insert into c (d) values ('tExt');
> ERROR: duplicate key value violates unique constraint "c_ix"
>
Thanks for clearing this up. :)
It works with CREATE UNIQUE INDEX.
So I had the missconception that UNIQUE (...) within CREATE TABLE (...)
was actually just an shorter way to define a unique index which it is not.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net, eggyknap@gmail.com
Subject: Re: Howto have a unique restraint on UPPER (textfield)
In-Reply-To: <4B65EC9C.6000606@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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