Thanks for putting me straight - I thought I remembered a previous post
from Tom about nulls not being indexed but it was probably referring to
partial indexes not indexing values that are null...
Coalescing null values might still be helpful to ensure that they are
ordered in the index at a specific location (either the beginning or the
end depending on your substitution value).
John
Greg Stark wrote:
> John Sidney-Woollett <johnsw@wardbrook.com> writes:
>
>
>>I don't think that null values are indexed - you'll probably need to coalesce
>>your null data value to some value if you want it indexed.
>
>
> That is most definitely not true for Postgres. NULL values are included in the
> index.
>
> However NULLs sort as greater than all values in Postgres. So when you sort
> descending they'll appear *first*. If you sort ascending they'll appear last.
>
> If you have any clauses like 'WHERE col > foo' then it will not be true for
> NULL values of col regardless of what foo is and those records will be
> dropped. This is true regardless of whether there's an index.
>
>
