3 ms·
Doing without OFFSET and by using an index field (an autoincrement id, a timestamp, etc) is feasible if there are no order by clauses. If you want to sort the c
by pmontra 7y ago
Doing without OFFSET and by using an index field (an autoincrement id, a timestamp, etc) is feasible if there are no order by clauses. If you want to sort the columns of an on screen table and paginate it you need an index for each sort combination. It's quite a pain to keep it up to date.
Edit: I run into this today because of a table with UUIDs and NULL values in a sort field. SELECT OFFSET 0 LIMIT 10 and SELECT OFFSET 10 LIMIT 10 returned overlapping results. I eventually added the record creation timestamp to the ORDER BY and all went well.
- yowlingcat 7y agoDefinitely a very real caveat, but in this case, you're breaking the monotonicity condition that makes paging this way very simple. As you describe, adding a little bit more context to restore the monotonicity condition makes all go well!