Row count in each table of the schema

Satish_Padarthi

My bulk load has failed or hung up in between when I was running multiple COPY from a big file with 1100 tables. I need to restart but I need to list all the tables and num_rows under each.

marcothesane

That's a rather basic SQL-generating-SQL task; remember to do similar things whenever you end up having with a problem that affects all tables in a schema.

With vsql, go: \t ( "Showing only tuples"), then \o count_my_tabrows.sql. Then, run this query (replacing 'public' with your schema name, obviously:

select 
'select '''||table_name||''', count(*) as rownum from '||table_name||';' 
from tables where table_schema='public';

This query once run, re-direct the output to a report text file:

\o row_count_report.txt

... and run your freshly created script:

\I count_my_tabrows.sql

Happy playing ...

Marco

Prasanta_Pal

You may run the below command to see the status of COPY (accepted_row_count | rejected_row_count | table_name  etc..)

dbadmin=> select * from load_streams;