> CD> I would like to...
> CD> ... find entries that have either a null entry or a blank entry as
> CD> a boolean result and sort by the boolean result.
> CD> (ie: select field='' or field is null as x from table order by x;)
> SELECT field
> FROM table
> WHERE field='' OR field IS NULL
> ORDER BY field;
The problem statement isn't really clear, but Colin's prototype query
suggests that he wants all fields back, with a boolean column "true" if
the field is zero-length or if the field is NULL.
So I think his original proposal is the one he wants:
SELECT (field = '' OR field IS NULL)
FROM table
ORDER by 1;
> For hackers only. IMHO I think we need to do something for NULLs. ;)
> What do you think about?
The only open issues on NULLs afaik (I'm doing this from memory, so if
there are other things on the ToDo don't take this as having removed
them :) are:
1) functions taking or returning "pass by value" arguments, as is
typical for functions returning int4 (and other 4-byte or shorter data
types except for float4), cannot signal that the returned value should
actually be NULL. So they must throw an error instead. That's why
char_length() doesn't behave gracefully with nulls.
2) NULL fields don't move from front-to-back or back-to-front when
changing the sort order from ascending to descending. I don't speak for
the Postgres team on this, but frankly I don't see this as a big issue.
I know that SQL92 specifies that nulls *should* switch ends of the
returned list, but the standard also says that *which* end of the list
they are on for, say, ascending order, is implementation-dependent. So,
it seems like portable code really needs to handle both cases anyway...
imho it is a place where the standard probably should have said less
(or, depending on your preference, more) and is inadequate as-is. If you
are getting back a mix of nulls and values, check each returned row for
whether the value is null and you won't get in trouble...
- Tom
btw, Thanks again Jose' for those great SQL reference pages. Oliver and
I have got them transcribed to sgml, and they will make a nice addition
to the v6.4 release of the docs.
