From: | Tobias Florek <postgres(at)ibotty(dot)net> |
---|---|
To: | Kevin Grittner <kgrittn(at)ymail(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
Subject: | Re: tuple compare involving NULL |
Date: | 2014-08-13 14:28:42 |
Message-ID: | 53EB761A.9040301@ibotty.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2014-08-13 14:44:14 | Re: tuple compare involving NULL |
Previous Message | Tom Lane | 2014-08-13 14:25:33 | Re: tuple compare involving NULL |