1

I want to loop through each table in my database, and print it's row count.

I can get all table names, but I do not know how to loop through them, assigning SELECT COUNT(*) to its table.

-- Get all tablenames
SELECT relname
FROM pg_stat_user_tables

Example Result

table_name     row_count
-------------  -------------
users          321
questions      807
comments       3,145
tags           58
...

Note: I know you can use pg_stat_user_tables to get every table's row counts, but I want to see a "hello world" Postgres loop that SELECTS values. I couldn't find any other SO question or example online that was this simplified.

Don P
  • 60,113
  • 114
  • 300
  • 432
  • See [How do you find the row count for all your tables in Postgres](http://stackoverflow.com/questions/2596670) – Daniel Vérité Feb 24 '15 at 19:59
  • Are you referring to @Paul's answer? It doesn't work if you try to run it. Gives a syntax error – Don P Feb 24 '15 at 20:22
  • Not this one specifically, but reading all these answers closely, I'm surprised that none of them is good for exact counts. I've just submitted [another one](http://stackoverflow.com/a/28709658/238814). – Daniel Vérité Feb 25 '15 at 01:39

1 Answers1

2

I believe you'll need to create a Pl/SQL procedure for that as documented in PostgreSQL manual.

Inside your loop write something like:

EXECUTE 'SELECT count(*) FROM ' || tabname::regclass;
lnrdo
  • 396
  • 1
  • 13