I found that Postgres isn't behaving like I thought when using a unique index 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
This is the results I got: intranet=# create table foo (a varchar(10), b varchar(10)); CREATE intranet=# create unique index foo_idx on foo using btree(a, b); CREATE intranet=# insert into "foo" (a, b) values ('apa', 'banan'); INSERT 26229704 1 intranet=# insert into "foo" (a, b) values ('apa', 'banan'); ERROR: Cannot insert a duplicate key into unique index foo_idx intranet=# insert into "foo" (a, b) values ('apa', null); INSERT 26229706 1 intranet=# insert into "foo" (a, b) values ('apa', null); INSERT 26229707 1 And another completely unrelated question... I have got a table with a composite index on A andBb and an index on A which I query with something like this: SELECT * FROM "table" WHERE (a = 1 OR a = 2 OR a = 3) AND b > 1232132 AND b < 123123123213123 Postgres then chooses to use the index for A three times, which is really slow on my table... Then I rewrote the query like: SELECT * FROM "table" WHERE a = 1 AND b > 1232132 AND b < 123123123213123 UNION SELECT * FROM "table" WHERE a = 2 AND b > 1232132 AND b < 123123123213123 UNION SELECT * FROM "table" WHERE a = 3 AND b > 1232132 AND b < 123123123213123 Postgres then behaved better and choosed the composite index in all three cases resulting in a very large improvement... Why is this, and has it been improved in more recent versions? Thanks in advance, Jimmy Mäkelä ------------------------------------------------ Jimmy Mäkelä 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å till: www.agent25.se ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly