PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use keyset pagination with ordering if the key is a string

Given a table that looks like the following:

+---------------+----------+
|title          |enabled   |
+---------------+----------+
|Four           |N         |
|Two            |N         |
|Three          |N         |
|One            |Y         |
|Five           |Y         |
|Six            |Y         |
+---------------+----------+

Using keyset pagination and sorting by enabled, how would I would I be able use the title as the key?

For example, using

select title, enabled
from my_table
where title > 'Three'
order by enabled;

returns

+---------------+----------+
|title.         |enabled   |
+---------------+----------+
|Two            |N         |
+---------------+----------+

but this removes all rows that have a title that is after Three alphabetically, but I would like for it to look like the original result and sreturn the last 3 rows instead.

Tags:

2

Answers


  1. 0

    …I would like for it to look like the original result…

    There’s a misconception here. Tables do not have inherent ordering.

    In short, there’s no such a thing such as a "default ordering" or an "ordered table". You got the initial rows in ANY ORDER and — in the absence of an ORDER BY clause — that order is subject to change without notice.

    Solution: Add an extra column to your table to store the order in which you want the rows to appear.

    For example:

    +---------------+----------+----------+
|title          |enabled   | ordering |
+---------------+----------+----------+
|Four           |N         |  10      |
|Two            |N         |  20      |
|Three          |N         |  30      |
|One            |Y         |  40      |
|Five           |Y         |  50      |
|Six            |Y         |  60      |
+---------------+----------+----------+

    Then, the keyset pagination query will look like:

    select title, enabled
from my_table
where title > 'Three'
order by ordering;
    Login or Signup to reply.
  2. 0

    In Keyset Pagination, the WHERE > and ORDER BY must be in the same columns, otherwise you are not actually paginating properly.

    select
  mt.title,
  mt.enabled
from my_table
where mt.title > 'Three'
order by
  mt.title
limit 100;

    You must have an index on (title) INCLUDE (enabled) or similar, otherwise performance will suffer.

    Or you can sort by enabled, title but then you need a filter on both as well, and an index (enabled, title).

    select
  mt.title,
  mt.enabled
from my_table
where (mt.enabled, mt.title) > (false, 'Three')
order by
  mt.enabled,
  mt.title
limit 100;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search