I am implementing pagination in PostgreSQL using the (createdon, id) > (...) condition to fetch the next batch of records. However, I am facing an issue where records with the same createdon timestamp are not being correctly excluded, causing duplicate records in the second query.

SELECT id, createdon 
FROM "schema_name"."table_name"
ORDER BY createdon ASC, id ASC
LIMIT 2;

Returns

uuid1    timestamp1
uuid2    timestamp1

Second query:

SELECT id, createdon 
FROM "schema_name"."table_name"
createdon > timestamp 'timestamp1' 
OR (createdon = timestamp 'timestamp1' AND id > 'uuid2')
ORDER BY createdon ASC, id ASC;

Expected output:

uuid3    timestamp1
uuid4    timestamp1
uuid5    timestamp1
uuid6    timestamp1

But Returned output:

uuid1    timestamp1  -- Should be excluded
uuid2    timestamp1  -- Should be excluded
uuid3    timestamp1
uuid4    timestamp1
uuid5    timestamp1
uuid6    timestamp1

Even though I am using:

(createdon > timestamp 'timestamp1' 
 OR (createdon = timestamp 'timestamp1' AND id > 'uuid2'))

The first two records should have been skipped in Query 2.

However, the second query still returns the first two records, leading to duplicates in pagination.

I am implementing pagination in PostgreSQL using the (createdon, id) > (...) condition to fetch the next batch of records. However, I am facing an issue where records with the same createdon timestamp are not being correctly excluded, causing duplicate records in the second query.

SELECT id, createdon 
FROM "schema_name"."table_name"
ORDER BY createdon ASC, id ASC
LIMIT 2;

Returns

uuid1    timestamp1
uuid2    timestamp1

Second query:

SELECT id, createdon 
FROM "schema_name"."table_name"
createdon > timestamp 'timestamp1' 
OR (createdon = timestamp 'timestamp1' AND id > 'uuid2')
ORDER BY createdon ASC, id ASC;

Expected output:

uuid3    timestamp1
uuid4    timestamp1
uuid5    timestamp1
uuid6    timestamp1

But Returned output:

uuid1    timestamp1  -- Should be excluded
uuid2    timestamp1  -- Should be excluded
uuid3    timestamp1
uuid4    timestamp1
uuid5    timestamp1
uuid6    timestamp1

Even though I am using:

(createdon > timestamp 'timestamp1' 
 OR (createdon = timestamp 'timestamp1' AND id > 'uuid2'))

The first two records should have been skipped in Query 2.

However, the second query still returns the first two records, leading to duplicates in pagination.

• sql
• postgresql
• postgresql-9.1
• postgresql-9.3

• Please demonstrate the case with a fiddle. – The Impaler Commented Mar 29 at 23:45

1 Answer 1

By loss of precision

As demonstrated in this fiddle,
you have probably lost precision during the multiple conversions:

• to your applicative layer's internal timestamp type
  (your database stores milliseconds but your application only keeps the seconds)
• due to back and forth string conversions
  (you pass the timestamp back as a string generated from a format to the second)
• potentially from a timezone change
  (your database is not on the same TZ as your application, then a timestamp at 01:00:00 CET is converted to 00:00:00 UTC when received by your application, which then asks for createdon > timestamp '00:00:00')

Alternatives?

Why not using ORDER BY createdon, uuid OFFSET 2 to pass by the 2 first rows, after you received them?

Of course one could say that this does work only if your table is stable (no new rows were created inbetween, no keys deleted),
but as your example shows, as well as your use of uuid to discriminate between two equally dated rows,
it looks like a monotonally incremented value, and new ones would be generated with a value greater than any existing one,
so the only real problem would be on deletions, which you probably could handle with soft deletes (marking entries as removable instead of removing them immediately).