X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 7FBED9FA140 for ; Sat, 1 Jul 2006 11:04:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 24480-05 for ; Sat, 1 Jul 2006 11:04:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.201]) by postgresql.org (Postfix) with ESMTP id DF26C9FA395 for ; Sat, 1 Jul 2006 11:04:23 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id s13so362370wxc for ; Sat, 01 Jul 2006 07:04:22 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:cc:subject:in-reply-to:references:date:message-id:user-agent:mime-version:content-type:x-virus-scanned; b=oYJmJcyeDKdlVmqxJdxokaCXWoBuyXYPh/McP/9UlfK+rAZv8gRCEC1xT3bTuFE1Ak5zde8NBAUhe+kBuQ/HsHA4qx68qODjxSsESEZel0bJYFuSIncThvfvQ8xEn9nqwq0GGEtHajWxmi4V1ecca1Hu5n1Jj0JQwSORNgmoN+I= Received: by 10.70.80.1 with SMTP id d1mr2468448wxb; Sat, 01 Jul 2006 07:04:22 -0700 (PDT) Received: from strongwill.g2ctech ( [201.22.22.212]) by mx.gmail.com with ESMTP id h19sm2881713wxd.2006.07.01.07.04.21; Sat, 01 Jul 2006 07:04:22 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by strongwill.g2ctech (Postfix) with ESMTP id A1117B73CC; Sat, 1 Jul 2006 11:04:17 -0300 (BRT) Received: from strongwill.g2ctech ([127.0.0.1]) by localhost (strongwill.g2ctech [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 03724-03-2; Sat, 1 Jul 2006 11:04:16 -0300 (BRT) Received: from jupiter.g2ctech (unknown [10.0.0.240]) by strongwill.g2ctech (Postfix) with ESMTP id D4DFDB737F; Sat, 1 Jul 2006 11:04:16 -0300 (BRT) Received: by jupiter.g2ctech (Postfix, from userid 1000) id 629921675FD; Sat, 1 Jul 2006 11:04:13 -0300 (BRT) From: Jorge Godoy To: Daniel CAUNE Cc: pgsql-sql@postgresql.org Subject: Re: Constraint UNIQUE on a column not case sensitive In-Reply-To: <0J1Q008ME8ZX7M90@VL-MO-MR003.ip.videotron.ca> (Daniel CAUNE's message of "Sat, 01 Jul 2006 09:47:59 -0400") References: <0J1Q008ME8ZX7M90@VL-MO-MR003.ip.videotron.ca> Date: Sat, 01 Jul 2006 11:04:13 -0300 Message-ID: <87irmha0bm.fsf@gmail.com> User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: amavisd-new at g2ctech.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/6 X-Sequence-Number: 25580 Daniel CAUNE 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