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