Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5CDC4476033 for ; Mon, 13 Jan 2003 07:08:02 -0500 (EST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id AF14D475FDA for ; Mon, 13 Jan 2003 07:08:01 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 44959D5F7; Mon, 13 Jan 2003 04:08:04 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3A5BB5C02; Mon, 13 Jan 2003 04:08:04 -0800 (PST) Date: Mon, 13 Jan 2003 04:08:04 -0800 (PST) From: Stephan Szabo To: =?iso-8859-1?Q?Jimmy_M=E4kel=E4?= Cc: "'pgsql-sql@postgresql.org'" Subject: Re: Unique indexes not unique? In-Reply-To: Message-ID: <20030113040014.Q53527-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/166 X-Sequence-Number: 11031 On Mon, 13 Jan 2003, [iso-8859-1] Jimmy M=E4kel=E4 wrote: > I found that Postgres isn't behaving like I thought when using a unique i= ndex in > combination with NULL-values... > Is this a bug or specified in the SQL-standard? If its a bug, is it fixed= in a > recent version? We are using 7.2.3 AFAIK this is standard. =46rom the unique predicate (8.9), If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the corresponding column in the other row according to Subclause 8.2, "comparison predicate", then the result of the unique predicate is true; otherwise, the result of the unique predicate is false. Unique constraints are defined in terms of the unique predicate. > And another completely unrelated question... I have got a table with a co= mposite > index on A andBb and an index on A > which I query with something like this: > > SELECT * FROM "table" > WHERE (a =3D 1 OR a =3D 2 OR a =3D 3) AND b > 1232132 AND b < 12312312321= 3123 > > Postgres then chooses to use the index for A three times, which is really= slow > on my table... On my dev (7.4devel) box I see it using the composite index three times, but you haven't given explain output for the two queries or any statistics information so that doesn't say much.