Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 67DF76324FF for ; Sat, 30 Jan 2010 22:57:23 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 53301-01 for ; Sun, 31 Jan 2010 02:57:03 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-px0-f194.google.com (mail-px0-f194.google.com [209.85.216.194]) by mail.postgresql.org (Postfix) with ESMTP id 8F2BC63249E for ; Sat, 30 Jan 2010 22:57:12 -0400 (AST) Received: by pxi32 with SMTP id 32so2762773pxi.15 for ; Sat, 30 Jan 2010 18:57:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:cc :subject:references:mime-version:content-type:content-disposition :in-reply-to:user-agent; bh=EOq/OGIisKRWHBN/sfxGiloalkyfmXnDPJEDnzWmick=; b=W86KyP+nEFkTQgGruW2NYwzGSjWa0phOwPWN8zvAalVqxg/vEaG6rfPUJMT2SPOPQk iK5TmE9o6di3Fto/s+2hRoPI7Adsyh7jxYYMTbfyW7EfNqdXZfhpkh/HmbAKnto8fuU8 3CEvu/GdwOYohTbSpjk6MpVOHvfuVJdsUaG3E= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:cc:subject:references:mime-version :content-type:content-disposition:in-reply-to:user-agent; b=C8KpXRA+00MTcc+mra82ZrN1TRrLIdOWhkLVAXbQBy97DKmN0txjhsq3Qny0uS8oe5 /ren3BceUAAsl6hzzdbFJh7kF0SrqI4VuQh/TfRTEd2M3edyrAlAW3uC1m9o4TS9zUgQ TytAumkWO/yHPC+i7tLUT+SmG9nP56Li7AZbk= Received: by 10.143.26.33 with SMTP id d33mr1915462wfj.38.1264906630399; Sat, 30 Jan 2010 18:57:10 -0800 (PST) Received: from eddie (c-67-161-209-136.hsd1.ut.comcast.net [67.161.209.136]) by mx.google.com with ESMTPS id 22sm3078882pzk.10.2010.01.30.18.57.08 (version=TLSv1/SSLv3 cipher=RC4-MD5); Sat, 30 Jan 2010 18:57:08 -0800 (PST) Message-ID: <4b64f184.9613f30a.5d13.01b2@mx.google.com> Date: Sat, 30 Jan 2010 19:56:53 -0700 From: Joshua Tolley To: Andreas Cc: pgsql-sql@postgresql.org Subject: Re: Howto have a unique restraint on UPPER (textfield) References: <4B64EA46.60605@gmx.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="u3gIr+u2NK768I+f" Content-Disposition: inline In-Reply-To: <4B64EA46.60605@gmx.net> User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.491 tagged_above=-10 required=5 tests=AWL=0.108, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201001/52 X-Sequence-Number: 33588 --u3gIr+u2NK768I+f Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable 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 = =20 > 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" -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com --u3gIr+u2NK768I+f Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAktk8XUACgkQRiRfCGf1UMP5aACeNe2itI1z04C1n9dD7g/k58aJ 9qsAmQFF0bDk20hx4VqgW2AvktTUIC0r =0lug -----END PGP SIGNATURE----- --u3gIr+u2NK768I+f--