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:
id | created_at |
---|---|
e327847a-7058-49cf-bd91-f562412aedd9 | 2022-05-23 23:07:22.592 |
d35c6bb8-06dd-4b86-b5c6-d123340520e2 | 2022-05-23 23:07:22.592 |
5167cf95-953f-4f7b-9881-03ef07adcf3c | 2022-05-23 23:07:22.592 |
d14f48dc-df22-4e98-871a-a14a91e8e3c1 | 2022-05-23 23:07:21.592 |
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
id | created_at |
---|---|
d14f48dc-df22-4e98-871a-a14a91e8e3c1 | 2022-05-23 23:07:21.592 |
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?