I am using the keyset pagination method for uuids on my Postgres database described in this post:
However, I have noticed when I have two records where the date is the same, rows are being skipped from the result.
For example when I run the query:
SELECT id, created_at FROM collection ORDER BY created_at DESC, id DESC
I get the records back as I expect them, with
created_at being the primary order, then
id acting as a tiebreaker:
However when I run a query to paginate through like:
SELECT id, created_at FROM collection WHERE (created_at, id) < ('2022-05-23 23:07:22.592','d35c6bb8-06dd-4b86-b5c6-d123340520e2') ORDER BY created_at DESC, id DESC LIMIT 3
I would expect to get back the last two records, but my result set is instead
I've also tried some variations on the query to try to fix it, such as:
SELECT id, created_at FROM collection WHERE created_at < '2022-05-23 23:07:22.592' OR (created_at = '2022-05-23 23:07:22.592' AND id < 'd35c6bb8-06dd-4b86-b5c6-d123340520e2') ORDER BY created_at DESC, id DESC
But I still get back the same result set.
What's going on with my query?