Query to Display the Table Name and the Number of Records in PostgreSQL

The following Query will give the list of table name and the corresponding Record count in PostgresPlus Advanced Server or EnterpriseDB.

1. SELECT table_name, CAST((xpath('//row/count/text()', xml_data))[1] AS varchar(50))::numeric As total_count FROM (  
2. SELECT table_name, query_to_xml( 'SELECT COUNT(1) FROM '||TABLE_NAME,true,false,'') AS xml_data FROM (SELECT * FROM USER_TABLES WHERE SCHEMA_NAME ='PUBLIC' ) AS tab  
3. ) AS xml;  

With the slight modification in the query, you can use the same in both Vanilla Postgres and PostgresPlus Advanced Server to fetch the table name and the record count. 

1. SELECT table_name, CAST((xpath('//row/count/text()', xml_data))[1] AS varchar(50))::numeric As total_count FROM (  
2. SELECT table_name, query_to_xml( 'SELECT COUNT(1) FROM '||TABLE_NAME,true,false,'') AS xml_data FROM (SELECT tablename AS table_name FROM pg_tables WHERE schemaname ='public' ) AS tab  
3. ) AS xml;