CODEWITHSUNDEEP
postgresqlpagination
Michael Hsu
Michael Hsu

Reputation: 982

how to paginate ordering by non-distinct / non-unique values in PostgreSQL?

How can I properly page by ordering on a column that could possibly have repeated values? I have a table called posts, which has a column that holds the number of likes of a certain post, called num_likes, and I want to order by num_likes DESC. But the image below shows a problem that I run into - the new row inserted between the two pages causes repeated data to be fetched.

This link here explains the problem, and gives the solution of keyset pagination, but from what I've seen, that only works if the column that the rows are being sorted on are distinct / unique. How would I do this if that is not the case?

new row problem pagination

Upvotes: 4

Views: 2108

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247445

You can easily make the sort key unique by adding the primary key to it.

You don't have to display the primary key to the user, just use it internally to tell “equal” rows apart.

For querying and indexing, you can make use of PostgreSQL's ability to compare like this: (num_likes, id) >= (4, 325698).

Upvotes: 10

Related Questions