Equivalent of FOUND_ROWS() function in Postgresql

Question

I am doing some paging in my application, returning 20 rows from the database at a time using PostgreSQL's standard OFFSET and LIMIT keywords. For instance, to get page 1 page:

SELECT stuff FROM table WHERE condition ORDER BY stuff OFFSET 0 LIMIT 20

It is a requirement of the application that we also show to the user the total number of records. So, obviously, I can get the total by issuing a separate query:

SELECT COUNT(*) FROM table WHERE condition

But if there are a large number of rows then this is not an optimal solution. I notice that MySQL has a very useful function called FOUND_ROWS() that does exactly what I am looking for:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function%5Ffound-rows

Is there an equivalent in PostgreSQL?

Mike Chamberlain · Accepted Answer · 2014-03-13 04:18:44Z

58

PostgreSQL has had window functions for a while now which can be used to do many things including counting rows before LIMIT is applied.

Based on the example above:

SELECT stuff,
       count(*) OVER() AS total_count
FROM table
WHERE condition
ORDER BY stuff OFFSET 40 LIMIT 20

edited Mar 13, 2014 at 4:18

Mike Chamberlain

43k28 gold badges113 silver badges160 bronze badges

answered Mar 12, 2014 at 13:59

Andrew Hacking

6,37633 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tylik Over a year ago

will count(*) OVER() AS total_count be executed for every row? e.g. if we have 20 rows, does it mean it will be calculated 20 times, or only once?

Julian Mehnle Over a year ago

It will be executed only once.

Ivan Pruchai Over a year ago

Thank you very much! This is the best solution for my needs.

Haim Evgi · Accepted Answer · 2010-10-21 06:05:45Z

0

There is no equivalent. look at

http://archives.postgresql.org/pgsql-novice/2007-07/msg00108.php

answered Oct 21, 2010 at 6:05

Haim Evgi

126k46 gold badges222 silver badges226 bronze badges

Comments

Rich · Accepted Answer · 2023-06-18 15:54:42Z

-2

SELECT
    n_live_tup     
FROM
    pg_stat_user_tables     
WHERE 
    relname = 'table_Name';

edited Jun 18, 2023 at 15:54

Rich

6,58520 gold badges39 silver badges58 bronze badges

answered Jun 16, 2023 at 21:47

Paulo Reis

1

Collectives™ on Stack Overflow

Equivalent of FOUND_ROWS() function in Postgresql

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related