Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 93769476461 for ; Mon, 13 Jan 2003 05:55:06 -0500 (EST) Received: from ringhals.neutron.se (unknown [213.136.41.129]) by postgresql.org (Postfix) with ESMTP id E4C38476394 for ; Mon, 13 Jan 2003 05:55:04 -0500 (EST) Message-ID: From: =?iso-8859-1?Q?Jimmy_M=E4kel=E4?= To: 'Tomasz Myrta' Cc: "'pgsql-sql@postgresql.org'" Subject: Re: Unique indexes not unique? Date: Mon, 13 Jan 2003 11:56:09 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/165 X-Sequence-Number: 11030 From: Tomasz Myrta [mailto:jasiek@klaster.net] > I'm not sure unique index works properly for null values. I can't > explain, why. Maybe it comes from SQL standard - null i a > special value Yeah, I thought about that too, but I think that behaviour is really bad and would consider it a bug. There are good reasons for having a special SQL null, but none of these apply to unique indexes (not that I can think of anyway). > Try to rewrite your query to show postgres how to use index on AB: > SELECT * FROM "table" > WHERE > (a = 1 AND b > 1232132 AND b < 123123123213123) or > (a = 2 AND b > 1232132 AND b < 123123123213123) or > (a = 3 AND b > 1232132 AND b < 123123123213123); Sure, this works, and is an improvement to the UNION-version, but I think postgres should be able do these substitutions by itself in the planner/optimizer... Or is there any method for specifying optimizer hints? Regards, Jimmy