Re: any way for ORDER BY x to imply NULLS FIRST in 8.3?

On Tue, 2007-11-06 at 11:38 +0400, rihad wrote:
> Is there any way to "hardcode" the NULL handling in an index (as per
> http://www.postgresql.org/docs/8.3/static/indexes-ordering.html) so
> that
> SELECT * FROM t ORDER BY foo automatically implies NULLS FIRST (and,
> similarly so that SELECT * FROM t ORDER BY foo DESC automatically
> implies NULLS LAST)? Thing is, I'm using PHP Symfony/Propel to
> generate
> their SQL and have no easy way to influence how they do so.

As Tom already stated, the ordering of NULLs with respect to non-NULLs
is defined by the implementation.

However, it's not clear that you've considered a clause like 'ORDER BY
(foo IS NULL), foo', which I believe is not implementation dependent.
(In SQL2003 draft, true is defined to sort before false. I can't find a
similar statement in SQL92 or SQL99.)

Bear in mind that the ordering of rows with the same value (incl. NULL)
of foo is arbitrary. And, I guess that the equivalence of this query on
two systems will depend on the collating locale also (but I'm a neophyte
in this area).

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0