sqlpostgresqlkeyset-pagination

How to get the initial ID for keyset pagnination?

I am new to keyset pagination and have a question.
I saw this query:

SELECT * 
FROM event 
WHERE 
 id < @UUID AND created_at < @Date
ORDER BY 
  id ASC,
  created_at ASC,
LIMIT 3;

Where does the UUID for the first query come from?

For the first request I can't send an ID while I don't have any data. For a second request I can check the ID from the previous call. But where to get my UUID for the first query?

Solution

  • For the first iteration, just omit the WHERE clause completely.
    After that, you have values to fill in to your WHERE clause from the previous iteration.

    For the record: the query you display is typically wrong for keyset pagination.
    You should use ROW value comparison instead.
    And the < operator does not match your sort order ASC.

    This would work:

    SELECT * 
FROM   event 
WHERE (id, created_at) > (@UUID, @Date)  -- ROW value comparison
ORDER  BY id, created_at                 -- ">" matches ASC sort order
LIMIT  3;

    See: