Tobias Florek <postgres@ibotty.net> wrote:
> =# SELECT (2,NULL, 'a') > (1, NULL, 'b'); # no 1
> =# SELECT (NULL, 2, 'a') > (NULL, 1, 'b'); # no 2
> =# SELECT (NULL, 1, 'b') > (NULL, 2, 'a'); # no 3
>
> does anyone knows a way to modify the queries to return true for
> number 1, true for 2 and false for 3, i.e. treat NULL in a tuple
> such that it compares smaller than anything not NULL?
I think it might make sense for the first one to return true,
although I would want to closely review the spec on that.
Logically, I think it *should be* equivalent to:
SELECT 2 >= 1 AND (2 > 1 OR (NULL >= NULL AND (NULL > NULL or 'a' > 'b')));
... and *that* returns true. I don't think there's any hope for
the other two without using COALESCE, although you could do that
within the row value constructors:
SELECT (coalesce(NULL, ''), 2, 'a') > (coalesce(NULL, ''), 1, 'b');
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company