POSTGRESQL小案例之拼写统计所有表数据

1. 拼写SQL：

postgres=# select 'select ''' || tablename || ''' as tablename, count(*) from ' || tablename || ' union all ' from pg_tables where schemaname='public';
                            ?column?                             
-----------------------------------------------------------------
 select 't_row' as tablename, count(*) from t_row union all 
 select 't_row_a' as tablename, count(*) from t_row_a union all 
(2 rows)

将最后一行最末尾的UNION ALL换成分号即可，语句如下：

 select 't_row' as tablename, count(*) from t_row union all 
 select 't_row_a' as tablename, count(*) from t_row_a;

2. 执行即可查出所有表的总数据：

postgres=# select 't_row' as tablename, count(*) from t_row union all 
postgres-#  select 't_row_a' as tablename, count(*) from t_row_a;
 tablename | count 
-----------+-------
 t_row     |     9
 t_row_a   |     9
(2 rows)