From: | Tobias Florek <postgres(at)ibotty(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | tuple compare involving NULL |
Date: | 2014-08-13 10:40:35 |
Message-ID: | 53EB40A3.9010708@ibotty.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2014-08-13 14:12:14 | Re: tuple compare involving NULL |
Previous Message | Jov | 2014-08-13 08:05:30 | Re: WAL log compatibility between 9.1 and 9.3 |