How to get the count and row list in a single query for pagination in PostgreSQL?
SELECT COUNT(*) FROM employee;
SELECT * FROM employee WHERE employer_id = ? OFFSET 0 LIMIT 25;
Instead of making 2 requests to get the count and rows, Is there a way to do it in a single query?
I have tried the following,
SELECT
e.*,
(SELECT COUNT(*) FROM employee AS e WHERE e.employer_id = ?)
FROM employee AS e
WHERE e.employer_id = ?
OFFSET 0 LIMIT 25;
but it's inefficient, it requires the same input twice and the count will be calculated in each row.
I there any better way or built-in function to do it?
You can use window function:
select *, count(*) over()
from employee e
where e.employer_id = ?
offset 0
limit 25
Or if you want to stick to your version, you can leverage cte:
with e_cnt as (
select count(*) cnt
from employee
where e.employer_id = ?
)
select *, (select cnt from e_cnt)
from employee e
where e.employer_id = ?
offset 0
limit 25
Then you know it's evaluated once.