Skip site navigation (1) Skip section navigation (2)

Re: ORDER BY and NULLs

From: Murphy Pope <pope_murphy(at)hotmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: ORDER BY and NULLs
Date: 2004-10-31 00:00:25
Message-ID: AWVgd.1165$j15.1000@fe25.usenetserver.com (view raw or flat)
Thread:
Lists: pgsql-sql
> You should just cross out that whole section. It's just flatly wrong.
> 
> I had always assumed it was just people bringing assumptions over from
> Oracle where it is true. Perhaps this book is to blame for some of the
> confusion. Which book is it?
> 
> Postgres indexes NULLs. It can use them for ORDER BY clauses.

I know this is an old-ish topic, but the question keeps coming up and I see
different answers every time.

I think I found the definitive answer and it looks like everyone (Bruce,
Tom, the book) is half-right.  Maybe this should go in a FAQ or something
since there seems to be so much confusion.

From section 41.3 of the documentation - this section describes the pg_am
table:

> An index access method that supports multiple columns 
> (has amcanmulticol true) must  support indexing null 
> values in columns after the first, because the planner 
> will assume the index can be used for queries on just 
> the first column(s). For example, consider an index 
> on (a,b) and a query with WHERE a = 4. The system will 
> assume the index can be used to scan for rows 
> with a = 4, which is wrong if the index omits rows 
> where b is null. It is, however, OK to omit rows 
> where the first indexed column is null. (GiST 
> currently does so.) amindexnulls should be set true 
> only if the index access method indexes all rows, 
> including arbitrary combinations of null values.

Here's what I get when I look at pg_am:

select amname, amcanmulticol, amindexnulls from pg_am;
 amname | amcanmulticol | amindexnulls
--------+---------------+--------------
 rtree  | f             | f
 btree  | t             | t
 hash   | f             | f
 gist   | t             | f

So it looks like btree indexes will index completely-NULL values, but the
other types won't index a row where all of the index columns are NULL.

Am I reading that right?

It sounds like the explanation quoted from the book is correct for all types
except for btree?



In response to

pgsql-sql by date

Next:From: Stephan SzaboDate: 2004-10-31 06:22:00
Subject: Re: Foreign Key Non-Null Problem in 8.0
Previous:From: Bruno Wolff IIIDate: 2004-10-29 17:57:54
Subject: Re: How do you compare (NULL) and (non-NULL)?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group