Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C647E634F43 for ; Sun, 31 Jan 2010 16:52:31 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 86054-04-5 for ; Sun, 31 Jan 2010 20:52:21 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by mail.postgresql.org (Postfix) with SMTP id E34AD633610 for ; Sun, 31 Jan 2010 16:52:18 -0400 (AST) Received: (qmail invoked by alias); 31 Jan 2010 20:48:33 -0000 Received: from i59F79B56.versanet.de (EHLO [192.168.1.113]) [89.247.155.86] by mail.gmx.net (mp062) with SMTP; 31 Jan 2010 21:48:33 +0100 X-Authenticated: #14269776 X-Provags-ID: V01U2FsdGVkX193kkNgtLF32lwX2Kl3sGyHcIWz00WX6uLghHrKot TOwO+MUG/4MDzJ Message-ID: <4B65EC9C.6000606@gmx.net> Date: Sun, 31 Jan 2010 21:48:28 +0100 From: Andreas User-Agent: Thunderbird 2.0.0.23 (Windows/20090812) MIME-Version: 1.0 To: Joshua Tolley CC: pgsql-sql@postgresql.org Subject: Re: Howto have a unique restraint on UPPER (textfield) References: <4B64EA46.60605@gmx.net> <4b64f184.9613f30a.5d13.01b2@mx.google.com> In-Reply-To: <4b64f184.9613f30a.5d13.01b2@mx.google.com> Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7bit X-Y-GMX-Trusted: 0 X-FuHaFi: 0.53000000000000003 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.185 tagged_above=-10 required=5 tests=BAYES_40=-0.185 X-Spam-Level: X-Archive-Number: 201001/54 X-Sequence-Number: 33590 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.