Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id E53229FB2CE for ; Thu, 26 Jul 2007 23:49:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 79737-04 for ; Thu, 26 Jul 2007 23:49:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 032599FA061 for ; Thu, 26 Jul 2007 23:49:32 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l6R2nVIL006522; Thu, 26 Jul 2007 22:49:31 -0400 (EDT) To: Dmitry Ruban cc: pgsql-sql@postgresql.org Subject: Re: unique index on fields with possible null values In-reply-to: <248746715.20070727125938@ruban.biz> References: <248746715.20070727125938@ruban.biz> Comments: In-reply-to Dmitry Ruban message dated "Fri, 27 Jul 2007 12:59:38 +1200" Date: Thu, 26 Jul 2007 22:49:31 -0400 Message-ID: <6521.1185504571@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/163 X-Sequence-Number: 28898 Dmitry Ruban writes: > I'm trying to find a solution for unique index on fields with possible > null values. You appear to be hoping that a unique index would constrain a column to contain at most one null entry. It doesn't work like that, and I strongly urge you to reconsider what you're using null for. I think you are in for a world of hurt, well beyond this one particular point, because the SQL spec's semantics for null are not designed around the idea that it represents a single distinguishable value. What it represents is "unknown". regards, tom lane