Row count in each table of the schema

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.

Comments

  • marcothesanemarcothesane - Select Field - Administrator

    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

  • 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;



Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file