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'