2

I have a table users that has, say, 42 records. To get the total rows and some other details, I use a window function:

select count(*) over() as count, name, id from users

And since the consumer app is paginated, I can utilise limit and offset to get the desired data:

select count(*) over() as count, name, id from users limit 5 offset 20

This gives me a (sample) result:

 count |  name   | id
-------+---------+----
    42 | Dummy05 | 27
    42 | Dummy06 | 30
    42 | Dummy07 | 31
    42 | Dummy08 | 32
    42 | Dummy09 | 33
(5 rows)

Of course, this can be optimised as described in this answer.

However when the offset value overshoots, I get 0 rows which is correct.

select count(*) over() as count, name, id from users limit 5 offset 50

Is there a way that gives me the total count despite the offset returning 0 rows? Something on the similar lines:

 count |  name   | id
-------+---------+----
    42 |         | 
(0 rows)
4

You can adapt something like this:

select c.count, u.*
from
  (
    select count(*) as count
    from users
  ) as c
  left join 
  (
    select name, id
    from users
    order by id
    limit 5 offset 50
  ) as u
  on true
order by u.id
 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.