Count all rows for all tables in a schema in PostgreSQL 9.3

We wish to count all rows in all tables of a schema in PostgreSQL 9.3.

For this tasks we need to use a xml function such as query_to_xml.

select tablename as sttable, xpath('/table/row/count/text()', x) as stcount from 
     (select tablename,query_to_xml('select count(*) from '||tablename,false,false,'') as x 
     from pg_tables where schemaname = 'public' order by 1) as z

Here is the result:

   sttable   | stcount 
-------------+---------
 sh_category | {4}
 sh_image    | {5}
 sh_item     | {5}
 sh_user     | {4}
(4 rows)

Here's an extented version showing the result without curly brackets:

select tablename as sttable, btrim(xpath('/table/row/count/text()', x)::text,'{}')::integer as stcount from (
     select tablename,query_to_xml('select count(*) from '||tablename,false,false,'') as x 
     from pg_tables where schemaname = 'public' order by 1
) as z

sttable | stcount ----------------+--------- cdr | 65 clubtyp | 107 control | 0 country | 242

Return all Tables and row counts as json:

select array_to_json(array_agg(t)) from (
     select tablename as table, btrim(xpath('/table/row/count/text()', x)::text,'{}')::integer as rowcnt from (
         select tablename,query_to_xml('select count(*) from '||tablename,false,false,'') as x 
         from pg_tables where schemaname = 'public' order by 1
     ) as z
) as t;

array_to_json ---------------------------------------------------------------- [{"table":"customer","rowcnt":265},{"table":"user","rowcnt":12}] (1 row)

Change Content

The information on this page is user generated content. The content does not claim to be complete or correct.

Everybody is invited to add or change the data. Just click on this link. No login or email is required. Thanks.