Re: tuple compare involving NULL

hi and thank you all for your replies,

(you are right, that i had the first example wrong.)


unfortunately

> SELECT (coalesce(NULL, ''), 2, 'a') > (coalesce(NULL, ''), 1, 'b');

or

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

will only work for specific data types and not, say, integers or dates.


maybe a little more context might be helpful. i am trying to have
reasonable efficient paging. the query i am building looks like

select t.*
   from table t,
        (select a, b from table where id = ) q
   where (q.a, t.b, t.id) > (t.a, q.b, q.id)
   order by t.a desc, t.b asc, t.id asc
   limit 10;

where t is a table with column id (primary key, serial), a and b.

that works fine and efficient (given an index (a,b) on t) without NULLs,
but (predictably) not in the presence of NULLs.

i would certainly like to handle that better, but i don't have any ideas
besides manually expanding the tuple comparison.

thank you so far,
  tobias florek