Tobias Florek wrote
> 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
This isn't really useful context, to me at least. It tells me nothing of
why you need an artificial PK or why you think a and b need to allow null.
The where clause is odd with its mix of q and t in the same row value and
q.id is technically broken though I know this is just an example.
As much as arbitrary default values suck they are at least better than null
in this regard. You can use coalesce for any data type.
Dave
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/tuple-compare-involving-NULL-tp5814686p5814716.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.