Postgresql – Pagination – Text comparison with greater than and less than with DESC

pagingpostgresql

I am implementing a seek method for pagination and am wondering about how to best query on a text column with DESC. The queries for this seek approach use a less than or greater than depending on if you are sorting ASC or DESC. This works great for integers and dates but I am wondering how best to do it with text columns, specifically for the first page.

For example, for the first page when sorting by name it would be

SELECT *
FROM users
WHERE first_name > ''
ORDER BY first_name ASC
LIMIT 5;

Then the next page would be

SELECT *
FROM users
WHERE first_name > 'Caal'
ORDER BY first_name ASC
LIMIT 5;

This works great. I am unsure about DESC order though. This seems to work but I am unsure if it is 'correct'.

 SELECT     *
 FROM   users
 WHERE  last_name < 'ZZZ'
 ORDER BY last_name DESC
 LIMIT 5;

Second page

SELECT  *
FROM    users
WHERE   last_name < 'Smith'
ORDER BY last_name DESC
LIMIT 5;

P.S. I am using the jooq support for the seek method and prefer to not have to hack around the native support, so ideally there is a proper parameter to put in the 'ZZZ' place above. i.e. there WHERE part of the clause is mandatory.

Best Answer

I don't think just placing an arbitrary value would work, since you have to put a larger if not possibly the largest value.

Perhaps pass in a boolean value with an OR operand (WHERE someflag OR last_name < @key), than way you do not have to pick arbitrary lowest value and largest values.

Otherwise if you can only control what is passed and not the WHERE operation, you'll have to use whatever the max value is for the column based on encoding.

Related Question