List tables by the number of rows in PostgreSQL database

This query returns list of tables in a database with their number of rows.

Query

select n.nspname as table_schema,
       c.relname as table_name,
       c.reltuples as rows
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
      and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;

Columns

  • table_schema - schema name
  • table_name - table name
  • rows - number of rows in a table

Rows

  • One row represents one table
  • Scope of rows: all tables in a database including tables without rows
  • Ordered by number of rows descending, from largest to smallest (in terms of number of rows)

Sample results

Tables by number of rows in pagila database :

0
There are no comments. Click here to write the first comment.