Do a Postgres for loop that SELECTs data from each table [duplicate]

Question

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.

See How do you find the row count for all your tables in Postgres — Daniel Vérité
– Daniel Vérité, Commented Feb 24, 2015 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
– Don P, Commented Feb 24, 2015 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. — Daniel Vérité
– Daniel Vérité, Commented Feb 25, 2015 at 1:39

lnrdo · Accepted Answer · 2015-02-24 21:07:13Z

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;

answered Feb 24, 2015 at 21:07

lnrdo

4062 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Do a Postgres for loop that SELECTs data from each table [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related