Re: tuple compare involving NULL

Tobias Florek wrote:
> 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.

That would probably make it harder to use a multicolumn index correctly.

The best solution would probably be to set the relevant fields NOT NULL.

NULLs usually make things harder on the database side.

Yours,
Laurenz Albe