I want to create a multicolumn index (A,B,C) on a table where C is a nullable column. Will this index store values when C is null? Oracle seems to allow this, but not sure about Postgres.
-
1Yes - it is ok. Primary keys do not accept null values though.Bjarni Ragnarsson– Bjarni Ragnarsson2020-11-25 10:18:40 +00:00Commented Nov 25, 2020 at 10:18
Add a comment
|
2 Answers
Yes, NULL values will be stored in the index and unlike Oracle, this is also true if all column values are null.
There is a difference on how Oracle and Postgres handle unique indexes with null values though:
The following works in Postgres, but will fail in Oracle
create table test (a int, b int, c int);
create unique index on test(a, b, c);
insert into test values (1,1,null);
insert into test values (1,1,null);
Edit
Since Postgres 15, this behaviour can be changed in Postgres:
The following index will not allow the above two inserts
create unique index on test(a, b, c) nulls not distinct;
2 Comments
mhd
Can someone explain the postgres example? looks like a gotcha to me. Oracle behavior is what I expected
@mhd:
null is not "equal" to null - so two rows with null values in the same column(s) don't evaluate to "equal", so Postgres allows it. Note that both comply with the SQL standard, because this specific corner case is marked as "implementation dependent". Note that since Postgres 15, you can specify how it should behave when creating the index.PostgreSQL treats NULL as distinct value, therefore, you can have multiple NULL values in a column with an index.
