Does postgres allow null values in multi column indexes?

Question

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.

Answer 1

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);

• Postgres example
• Oracle example

---

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;