# 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.