Introduction

It is sometimes nice to count all rows in all tables in a given schema. Just remember this will be an expensive operation if the dataset is big. It is possible to count rows based on statistics, but this solution will hit the blocks.

Count all rows in all tables in current schema

with x (y) as (
    select
      format('select %L as tablename, count(*) from %I ', tablename, tablename)
    from pg_tables
    where schemaname=current_schema()
)
select
    string_agg(y,' union all '||chr(10)) || ' order by tablename'
from x \gexec

The query generates a statement that is sent to psql \gexec for execution.

You can any time alter the current schema

set search_path to myschema;

Output

[postgresql] bjarte@rs  => \i /Users/BJBRA/projects/postgesql/psql/count_rows_all_tables.sql

┌──────────────────────────┬──────────┐
│        tablename         │  count   │
├──────────────────────────┼──────────┤
│ country                  │     1764 │
│ event                    │  2282626 │
│ event_participant        │  5528421 │
│ event_participant_lineup │ 20632176 │
│ image                    │    21446 │
│ incident                 │ 15592035 │
│ participant              │  1402271 │
│ participant_relation     │  3439748 │
│ sport                    │       64 │
│ standing                 │   352985 │
│ standing_participant     │  4982098 │
│ tournament               │    23237 │
│ tournament_stage         │    67119 │
│ tournament_template      │     4670 │
│ venue                    │    25783 │
└──────────────────────────┴──────────┘
(19 rows)

Time: 31209.640 ms (00:31.210)
Time: 49.374 ms

Enjoy!