List row counts for all tables in a database with PostgreSQL
To list the number of rows for each table in the current database with PostgreSQL, use the following SQL statement:
SELECT
pg_class.relname AS table_name,
pg_class.reltuples AS row_count
FROM
pg_class
LEFT JOIN
pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
WHERE
pg_namespace.nspname NOT IN ('pg_catalog', 'information_schema') AND
pg_class.relkind = 'r'
ORDER BY row_count DESC
;
This will output the results in the format below:
table_name | row_count
---------------+-----------
customers | 39
bookings | 24
(2 rows)
The table with the most rows will be at the top.
Last updated: 10/11/2015