PostgreSQL: Returning ordered rows after a specific ID

Question

Scenario: I am displaying a table of records. It initially displays the first 500 with "show more" at the bottom, which returns the next 500.

Issue: If between initial display and clicking "show more" 1 record is added, that will cause "order by date, offset 500, limit 500" to overlap by 1 row.

I'd like to "order by date, offset until 'id of last row shown', limit 500"

My row IDs are UUIDs. I am open to alternative approaches that achieve the same result.

Answer 1

i think you can use a subquery in the where to accomplish this.

e.g. given you're paginating through a users table, and you want the records after a given user:

SELECT *
  FROM users
  WHERE created_at > (
    SELECT created_at
      FROM users
      WHERE users.id = '00000000-1111-2222-3333-444444444444'
      LIMIT 1
  )
  ORDER BY created_at DESC limit 5;