Re: tuple compare involving NULL

Tobias Florek <postgres(at)ibotty(dot)net> writes:
> but any comparison involving NULL also returns NULL (i also kind of
> expected that). e.g.:

> =# SELECT (2,NULL, 'a') > (1, NULL, 'b'); # no 1

Really? I get

regression=# SELECT (2,NULL, 'a') > (1, NULL, 'b');
?column?
----------
t
(1 row)

> 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?

No, it doesn't work like that exactly. But in this example, 2 > 1 is
sufficient to determine the result, so the nulls need not be compared.

By and large, though, I'd say that this question is an indicator that
you're misusing NULL. Rethink your data design.

regards, tom lane