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