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.
count of records in a column
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: count of records in a column
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:
The JIM schema contains 2 tables:
I created a table to store the table row counts:
Now I can run the following on the command line to populate my row count table:
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)
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)
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)
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: count of records in a column
Hi Jim,
Somehow the last statement is not working at my end,This is what I tried
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
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...
Thanks
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: count of records in a column
The command itself has no syntax errors. This is not supposed to be run inside vsql.
Checkout vertica.tips for more Vertica resources.
Re: count of records in a column
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Fri May 08, 2015 4:38 pm, edited 1 time in total.
Re: count of records in a column
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Fri May 08, 2015 4:37 pm, edited 1 time in total.
Re: count of records in a column
Thanks for that skwa, but I am looking for pure SQL solution towards that.