Re: Firebird and PostgreSQL at the DB Corral.

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> On Sat, Dec 20, 2003 at 04:14:51PM -0800, Jeff Davis wrote:
>> Are NULLs even indexed?

> No, but with a partial index you can acheive the same effect.

Actually, btree indexes *do* store nulls. This is not really relevant
to the topic at hand, though, since the proposal was to do something
like
CREATE INDEX ind ON tab (foo) WHERE bar IS NULL;
which does not imply anything about storing any actually-null entries
in the index.

Any multicolumn index type must be prepared to store nulls, at least in
columns after the first one (GiST exploits that fine print, btree
doesn't care). Otherwise you couldn't use an index on (a,b) to search
for only a --- if you did, a query like "WHERE a = 42" would effectively
act like "WHERE a = 42 AND b IS NOT NULL", which is wrong.

In principle we could use btree indexes to implement WHERE x IS (NOT)
NULL searches, but the operator-based API for index scans has a problem
representing such searches because IS NULL/IS NOT NULL are not treated
as operators by the parser. Someday someone will get annoyed enough to
fix that.

regards, tom lane