Code Mamba

In MySQL you can select a record set with a limit and an offset and then return the count(*) of all the records without running the same query like this:

SELECT SQL_CALC_FOUND_ROWS
  Id, Name
FROM my_table
WHERE
  Name LIKE '%prashant%'
LIMIT 0, 10;
SELECT FOUND_ROWS();

In PostgreSQL there is no such thing, so you have to be creative. What we’ll do is:
1. Create a temp table with all the required data.
2. Select the data from the temp table with the limit and the offset.
3. Select the count(*) from the temp table.
4. Select everything else you need from the temp table.
5. Drop the temp table.
Why and when is this a good idea.
When your query has many limitaons like a full-text-search clause:

...WHERE textsearchable_index_col @@@ q...

or when your data is a join from many tables – you don’t have to join the tables many times – for the data, for the count(*), and for everything else.
Here’s the example:

select * into tmp_tbl from tbl where [limitations];
select * from tmp_tbl offset 10 limit 10;
select count(*) from tmp_tbl;
drop table tmp_tbl;