Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.