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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
