Row counts for all tables in a postgres db.

postgres_table_row_count.sql

SELECT schemaname,relname,n_live_tup 
  FROM pg_stat_user_tables 
  ORDER BY n_live_tup DESC;

Exact Row Counts in Postgres

everything's copy&paste from this awesome blog Exact Row Counts for All Tables in MySQL and Postgres,

If tldr;

Make a function first

create or replace function 
count_rows(schema text, tablename text) returns integer
as
$body$
declare
  result integer;
  query varchar;
begin
  query := 'SELECT count(1) FROM ' || schema || '.' || tablename;
  execute query into result;
  return result;
end;
$body$
language plpgsql;

loop it through the information_schema.tables

select 
  table_schema,
  table_name, 
  count_rows(table_schema, table_name)
from information_schema.tables
where 
  table_schema not in ('pg_catalog', 'information_schema') 
  and table_type='BASE TABLE'
order by 3 desc

similar threads: if above failed 🤣

1. Stackoverflow