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

Postgresql – Keyset pagination with ordering in postgres

I’m using keyset pagination to paginate in a table. Table looks like this:

+-------------------------+
| ID | Name | private_key |
+-------------------------+
| 1  | John | null        |
| 2  | James| abc123      |
| 3  | Jane | null        |
| 4  | Dave | def456      |
| 5  | Will | null        |
| ...                     |
+-------------------------+

I’m using the ID field for the keyset pagination.

I fetch 50 records per time. After every fetch, I get the highest ID in the result set. When I fetch the next 50 results, I fetch the first 50 results AFTER this ID. An example:

SELECT
    id,
    name,
    private_key
FROM
    users
WHERE
    id > [last_id_fetched]
LIMIT
    50

This works perfectly. The problem is, if I use an ORDER statement, the ORDER statement is only applied to the 50 results that are fetched, not the total result set.

I have about 2,000 records, and about 500 of them have a private_key. I want to paginate with keyset pagination on the ID, but I want to fetch all the users where the private_key field is not null first.

How can I achieve this in postgres?

Tags:

2

Answers


  1. 0

    To achieve keyset pagination in PostgreSQL while ensuring that rows with a non-null private_key are fetched first, you can use a combination of subqueries and a custom sorting strategy. Here’s how you can do it:

    SELECT
    id,
    name,
    private_key
FROM
    users
WHERE
    private_key IS NOT NULL
ORDER BY
    private_key ASC, id ASC
LIMIT
    50;

-- After fetching the above 50 results, get the last private_key and id from the result set.

SELECT
    id,
    name,
    private_key
FROM
    users
WHERE
    private_key IS NULL
AND (
    private_key > [last_private_key_fetched]
    OR (private_key = [last_private_key_fetched] AND id > [last_id_fetched])
)
ORDER BY
    private_key ASC, id ASC
LIMIT
    50;

    In the first query, you fetch the first 50 rows where private_key is not null, sorted by private_key and then id. After fetching these results, note the last private_key and id.

    In the second query, you fetch the next 50 rows where private_key is null and use a combination of sorting by private_key and then id. The WHERE clause ensures that you continue fetching results after the last private_key and id values obtained in the first query.

    This way, you will paginate using keyset pagination on the ID, while ensuring that rows with non-null private_key values are fetched first.

    Login or Signup to reply.
  2. 0

    I think, you can use OFFSET and FETCH FIRST for your pagination.

    It’s very simple to use:

    SELECT
    id,
    name,
    private_key
FROM
    users
ORDER BY name
OFFSET :page_size*:page_number ROWS 
FETCH FIRST :page_size ROW ONLY

    Or, whithout variables, if we need to populate second page with 5 rows on the each page:

    SELECT
    id,
    name,
    private_key
FROM
    users
ORDER BY name
OFFSET 5*2 ROWS 
FETCH FIRST 5 ROW ONLY

    Some exaple for playing: https://dbfiddle.uk/zplwAj9A

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search