Re: tuple compare involving NULL

Tobias Florek wrote:
> i guess my problem reduces to the following question, but if there is
> not enough detail feel free to ask for more details.
>
>
> the following query returns true (as expected).
>
> =# SELECT (2,4,'a string') > (2,3,'another string');
>
> 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
> =# 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 can (of course) expand the tuple compare (a1, a2, a3) > (b1, b2, b3) to
> =# SELECT a1 > b1
> or (a1 = b1 and (a2 > b2
> or (a2 = b2 and a3 > b3))
>
> and insert appropriate COALESCEs and IS NULLs and much conditional
> logic. but i really hope, there is a better way.

I can't think of any.
There is IS DISTINCT FROM for "equality" involving NULLs, but that won't solve
your problem.

I'd say that you are basically trying to abuse NULL, which means something
like "don't know" or "missing value", and that cannot be compared with other values.

Something with COALESCE would maybe be the simplest workaround, like

(COALESCE(a, -1000), COALESCE(b, -1000)) > (COALESCE(c, -1000), COALESCE(d, -1000))

Yours,
Laurenz Albe