PostgreSQL count rows in multiple tables
Thursday, September 15, 2022
PostgreSQL Count Rows in all Tables
Postgresql Count Rows in All Tables
Overview
Open Pgadmin
Now it will look like the below window
Type this below Query or Just Copy From Here.
WITH TBL AS
(SELECT TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME not like 'pg_%' )
SELECT TABLE_SCHEMA,
TABLE_NAME,
(XPATH('/row/c/text()',
QUERY_TO_XML(FORMAT('select count(*) as c from %I.%I',
TABLE_SCHEMA,
TABLE_NAME),
FALSE,
TRUE,
'')))[1]::text::int AS TOTAL_ROWS
FROM TBL
ORDER BY 1 DESC,
3 DESC;
Results
This is better to use this query when you are planning either to migrate your database or you have recovered your database from any backups and verify PostgreSQL records.
Hello everyone, My name is Muhammad Kamran . I'm always keen to share my best of knowledge with all concerned audience with best wishes and prayers for their career groom.