My Database Friend PostgreSQL

Next: Generate Series on Months with PostgreSQL UP: TOC

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) Next: Generate Series on Months with PostgreSQL UP: TOC