Re: tuple compare involving NULL

Tobias Florek <postgres(at)ibotty(dot)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