DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=EU1xhVyMOg/ig2gHefeDy4reaaTB6+O7F6ug20UfFkIg2n+MHcsRdMVjDNuKM9xxO3
	ufN6s1U5j43L4EA65xrjJMCynNq8glil8GEn9VYuzees92IfRlytc8K8KAoYF9HaS0HD
	AbEZP5koHIsac6gsCr/Fu/JNmE8KFgEGM5vyw=
Message-ID: <4B6C8626.6080300@gmail.com>
Date: Fri, 05 Feb 2010 21:57:10 +0100
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
To: Andreas <maps.on@gmx.net>
CC: pgsql-sql@postgresql.org
Subject: Re: Howto have a unique restraint on UPPER (textfield)
References: <4B64EA46.60605@gmx.net>
In-Reply-To: <4B64EA46.60605@gmx.net>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Andreas wrote:
> is there a way to define a unique restraint on UPPER (textfield)?
> psql throws a syntax error because of the upper() function.
The third section of the create index command at 
http://www.postgresql.org/docs/8.4/interactive/sql-createindex.html 
describes a function based index with as example the function .... 
upper! :-)

postgres=# create table aap (a text);
CREATE TABLE
postgres=# create unique index ai on aap (upper(a));
CREATE INDEX
postgres=# insert into aap values ('aap');
INSERT 0 1
postgres=# insert into aap values ('aaP');
ERROR:  duplicate key value violates unique constraint "ai"

regards,
Yeb Havinga