hi,
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.
thank you in advance,
tobias florek