Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3309A476060 for ; Mon, 13 Jan 2003 05:04:35 -0500 (EST) Received: from ringhals.neutron.se (unknown [213.136.41.129]) by postgresql.org (Postfix) with ESMTP id 26024476033 for ; Mon, 13 Jan 2003 05:04:34 -0500 (EST) Message-ID: From: =?iso-8859-1?Q?Jimmy_M=E4kel=E4?= To: "'pgsql-sql@postgresql.org'" Subject: Unique indexes not unique? Date: Mon, 13 Jan 2003 11:05:33 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/163 X-Sequence-Number: 11028 I found that Postgres isn't behaving like I thought when using a unique ind= ex in combination with NULL-values... Is this a bug or specified in the SQL-standard? If its a bug, is it fixed i= n a recent version? We are using 7.2.3 This is the results I got: intranet=3D# create table foo (a varchar(10), b varchar(10)); CREATE intranet=3D# create unique index foo_idx on foo using btree(a, b); CREATE intranet=3D# insert into "foo" (a, b) values ('apa', 'banan'); INSERT 26229704 1 intranet=3D# insert into "foo" (a, b) values ('apa', 'banan'); ERROR: Cannot insert a duplicate key into unique index foo_idx intranet=3D# insert into "foo" (a, b) values ('apa', null); INSERT 26229706 1 intranet=3D# insert into "foo" (a, b) values ('apa', null); INSERT 26229707 1 And another completely unrelated question... I have got a table with a comp= osite 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 < 1231231232131= 23 Postgres then chooses to use the index for A three times, which is really s= low on my table... Then I rewrote the query like: SELECT * FROM "table" WHERE a =3D 1 AND b > 1232132 AND b < 123123123213123 UNION SELECT * FROM "table" WHERE a =3D 2 AND b > 1232132 AND b < 123123123213123 UNION SELECT * FROM "table" WHERE a =3D 3 AND b > 1232132 AND b < 123123123213123 Postgres then behaved better and choosed the composite index in all three c= ases resulting in a very large improvement... Why is this, and has it been improved in more recent versions? Thanks in advance, Jimmy M=E4kel=E4 ------------------------------------------------ Jimmy M=E4kel=E4 Programmerare Nybrogatan 55, Box 55708 114 83 Stockholm Direkt: 08-527 90 457 Mobil: 073-623 05 51 ------------------------------------------------ Jag tycker att du borde anlita en agent. G=E5 till: www.agent25.se