View all tables row count in PostgreSQL

This script will make your life easier to view the total row on all your tables, you need to create a function for it, but its quite easy to maintain

Create the function first

I named it maint_countrows so I can easily identify it as my collection of maintenance function, basically its just count how many rows inside a table

create or replace function 
maint_countrows(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;

Run the script

Then you can run it with

select 
  table_schema,
  table_name, 
  maint_countrows(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;

The result will be as follow

table_schema |  table_name   | maint_countrows
--------------+---------------+-----------------
 public       | inventory     |               0
 public       | staff         |               0
 public       | country       |               0
 public       | store         |               0
 public       | test          |               0
 public       | film          |               0
 public       | customer      |               0
 public       | film_actor    |               0
 public       | language      |               0
 public       | actor         |               0
 public       | film_category |               0
 public       | city          |               0
 public       | payment       |               0
 public       | temp          |               0
 public       | category      |               0
 public       | rental        |               0
 public       | address       |               0