count of records in a column

[nnani]

Hello All,

I am trying to get count of records in every table in a column alongside the table_name column.
What can be the best way to do this using only SQL.

[JimKnicely]

Hi nnani,

I hate messing with the projections to get an accurate row count for tables. So I've been doing something like the following to get the row counts for all tables in a particular schema...

Say I have schema JIM:

Code: Select all

dbadmin=> \dn jim;
     List of schemas
 Name |  Owner  | Comment 
------+---------+---------
 jim  | dbadmin | 
(1 row)

The JIM schema contains 2 tables:

Code: Select all

dbadmin=> \d jim.*;
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key 
--------+-------+--------+------+------+---------+----------+-------------+-------------
 jim    | tab1  | col1   | int  |    8 |         | f        | f           | 
 jim    | tab2  | col1   | int  |    8 |         | f        | f           | 
(2 rows)

I created a table to store the table row counts:

Code: Select all

dbadmin=> \d public.table_row_counts 
                                            List of Fields by Tables
 Schema |      Table       |    Column    |     Type     | Size | Default | Not Null | Primary Key | Foreign Key 
--------+------------------+--------------+--------------+------+---------+----------+-------------+-------------
 public | table_row_counts | table_name   | varchar(100) |  100 |         | f        | f           | 
 public | table_row_counts | row_count    | int          |    8 |         | f        | f           | 
 public | table_row_counts | at_timestamp | timestamp    |    8 |         | f        | f           | 
(3 rows)

Now I can run the following on the command line to populate my row count table:

Code: Select all

[dbadmin@vertica01 ~]$ vsql -Atc "select 'select ''' || table_name || ''' table_name, count(*) row_count, now() at_timestamp from ' || table_schema || '.' || table_name || ';' from tables where table_schema = 'jim';" | vsql -At | vsql -c "copy public.table_row_counts from stdin direct;"

[dbadmin@vertica01 ~]$ vsql -c "select * from public.table_row_counts;"
 table_name | row_count |        at_timestamp        
------------+-----------+----------------------------
 tab2       |         3 | 2014-02-19 08:44:25.887141
 tab1       |         1 | 2014-02-19 08:44:25.887141
(2 rows)

[nnani]

Hi Jim,

Somehow the last statement is not working at my end,This is what I tried

Code: Select all

 vsql -Atc "select 'select ''' || table_name || ''' table_name, count(*) row_count, now() at_timestamp from ' || table_schema || '.' || table_name  from tables" | vsql -At |  vsql -c "copy public.table_row_counts from stdin direct;" 
vsql: FATAL 2983:  Database "vsql" does not exist
ERROR 4856:  Syntax error at or near "select" at character 76
LINE 2: select MEASURES_CORE_AMOUNTTYPE table_name, count(*) row_cou...

Also, Can you please brief me on how you are running the queries once the queries are build from select clause in the first part.
Thanks

[NorbertKrupa]

The command itself has no syntax errors. This is not supposed to be run inside vsql.

[id10t]

Hi!

[DELETED]

[id10t]

Hi!

[DELETED]

[nnani]

Thanks for that skwa, but I am looking for pure SQL solution towards that.