tuple compare involving NULL

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

Responses

Browse pgsql-novice by date

  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