Postgres can... Oracle can't (index NULLs)

Well, it was a revelation.... For almost ages, I have been made to believe that NULLs cannot be indexed; primarily because Oracle chose not to, and I had used Oracle quite a lot in the past (and worked for them too at one time).

A couple of days ago I was asked by a developer (Arie) this very question, but in context of Postgres:

Subject: Are nulls indexed?
Body:
If I have an index on column A (null-able) will null values be indexed? Basically, if not, does it mean that such a query "select * from T where A is null" will have to do a full table scan?

I blindly said no... that was until I had to test something out based on his next question. And I was in fact surprised! Postgres can use a non-unique index for 'IS NULL' and 'IS NOT NULL' predicates!

Here's a small session to demonstrate that Postgres stores index-records for NULLs and can use such indexes for IS [NOT] NULL predicates:

postgres=# create table t ( a int, b char(32) );
CREATE TABLE
postgres=# create index t_i on t( a );
CREATE INDEX
postgres=# insert into t select s, s+1 from generate_series( 1, 1000000 ) as s;
analyze t;
INSERT 0 1000000
postgres=# analyze t;
ANALYZE
postgres=# select relpages, relname from pg_class where relname like 't%';
relpages | relname
----------+--------------------------
<snip>
8334 | t
2745 | t_i
(7 rows)

postgres=# explain select * from t where a is null;
QUERY PLAN
--------------------------------------------------------------
Index Scan using t_i on t (cost=0.00..8.38 rows=1 width=37)
Index Cond: (a IS NULL)
(2 rows)

postgres=# truncate t;
TRUNCATE TABLE
postgres=# insert into t select case s%5 when 0 then null else s end, s+1 from generate_series( 1, 1000000 ) as s;
INSERT 0 1000000
postgres=# reindex index t_i;
REINDEX
postgres=# analyze t;
ANALYZE
postgres=# select relpages, relname from pg_class where relname like 't%'; relpages | relname
----------+--------------------------
<snip>
8334 | t
2745 | t_i
(7 rows)

postgres=# explain select * from t where a is null;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using t_i on t (cost=0.00..7332.37 rows=198972 width=37)
Index Cond: (a IS NULL)
(2 rows)

postgres=#

I hope you get the picture.

So, another good reason to choose Postgres (or for that matter EnterpriseDB) over Oracle... :)

PS: This works only with the B-Tree indexes though.