X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 467A19DC99B for ; Thu, 9 Feb 2006 15:38:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16097-08 for ; Thu, 9 Feb 2006 15:38:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from outbound.internet-mail-service.net (outbound6.internet-mail-service.net [216.240.47.197]) by postgresql.org (Postfix) with ESMTP id 2AF309DC9A6 for ; Thu, 9 Feb 2006 15:38:45 -0400 (AST) Received: from idiom.com (idiom.com [216.240.32.1]) by outbound.internet-mail-service.net (Postfix) with ESMTP id 67F86240B21 for ; Thu, 9 Feb 2006 11:38:45 -0800 (PST) Received: from [192.168.1.109] (adsl-69-107-112-198.dsl.pltn13.pacbell.net [69.107.112.198]) by idiom.com (8.12.11/8.12.11) with ESMTP id k19Jcit0028560 for ; Thu, 9 Feb 2006 11:38:45 -0800 (PST) (envelope-from bryce1@obviously.com) Message-ID: <43EB9A3B.2080507@obviously.com> Date: Thu, 09 Feb 2006 11:38:35 -0800 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050921 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: UNIQUE, btree index allows duplicate records, if some fields are null Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.106 required=5 tests=[AWL=0.106] X-Spam-Score: 0.106 X-Spam-Level: X-Archive-Number: 200602/106 X-Sequence-Number: 24116 This threw me for a loop. Is this my fault, or a problem in postgres? I have a table with the following: "eg_vehicle_event_pkey" PRIMARY KEY, btree (event_id) "no_duplicate_events" UNIQUE, btree (thing, other_thing, "timestamp", number, other_number) The "no_duplicate_events" constraint works fine, but if I insert records where "other_thing" is null, they all go in without complaint. I can insert as many duplicates as I want. I had expected two records, identical in all respects including the null value, to be rejected by the constraint. This is with: PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-20)