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

On Wed, 2007-11-07 at 16:05 +0100, Martijn van Oosterhout wrote:
> On Wed, Nov 07, 2007 at 02:37:41PM +0000, Simon Riggs wrote:
> > Editing an application, you would be required to add the words NULLS
> > FIRST to every single ORDER BY and every single CREATE INDEX in an
> > application. If we know that is what people would do, why not have one
> > parameter to do this for them?
>
> I find it hard to beleive that every single query in an application
> depends on the ordering of NULLs. In fact, I don't think I've even
> written a query that depended on a particular way of sorting NULLs. Is
> it really that big a deal?

True, but how would you know for certain? You'd need to examine each
query to be able to tell, which would take even longer. Or would you not
bother, catch a few errors in test and then wait for the application to
break in random ways when a NULL is added later? I guess that's what
most people do, if they do convert.

I'd like to remove one difficult barrier to Postgres adoption. We just
need some opinions from people who *havent* converted to Postgres, which
I admit is difficult cos they're not listening.

> > Implement SQLServer and MySQL behaviour? Now we're talking about
> > hundreds of new applications that might decide to migrate/support
> > PostgreSQL because of our flexibility in being able to support both
> > kinds of sorting.
>
> TBH I think long term is should be attached to each column, as it is a
> property of the collation (my COLLATE patch let you specify it per
> column).

That's a great idea, but orthogonal to the discussion about migrating
from other databases. No other database works like that, nor does the
SQL standard, but I'll admit its sound thinking otherwise.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com