how to get the table row count of all the tables present in particular schema in postgresql 9.5?

Question

how to get the table row count of all the tables present in particular schema in postgresql 9.5? I would like to have the result as table_name | row_count. How this can be done using query?

Answer 1

This can be done with some XML magic:

select table_schema, table_name,
       (xpath('/row/count/text()', query_to_xml('select count(*) from '||format('%I.%I', table_schema, table_name), true, true, '')))[1]::text::int as row_count
from information_schema.tables
where table_schema = 'public'

Answer 2

https://www.postgresql.org/docs/current/static/monitoring-stats.html

n_live_tup Estimated number of live rows

t=# select relname,n_live_tup 
from pg_stat_all_tables 
where schemaname = 'public' 
order by n_live_tup desc 
limit 3;
        relname       | n_live_tup
------------+---------------------+------------
  x_pricing           |   96493977
  x_forum             |   57696510
  x_uploading         |   55477043
(3 rows)

Of course that data will be up to some approximation level. to count the exact number, you will need dynamic plpgsql (which btw will give you closer numbers, but still up to some approximation level). Both approximations depend on how often you change data and run vacuum...

The benefit of this approach is of course less resources consumed (load and time). The benefit of count(*) is more precise result at price of server load and time waiting