我想从所有表的总数+表的列表与他们的计数。有点像绩效表，显示大部分时间都花在了哪里

WITH results AS ( 
  SELECT nspname AS schemaname,relname,reltuples
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE 
      nspname NOT IN ('pg_catalog', 'information_schema') AND
      relkind='r'
     GROUP BY schemaname, relname, reltuples
)

SELECT * FROM results
UNION
SELECT 'all' AS schemaname, 'all' AS relname, SUM(reltuples) AS "reltuples" FROM results

ORDER BY reltuples DESC

当然，你也可以在这个版本的结果上加上一个LIMIT条款，这样你就可以得到最大的n个违例者以及总数。

需要注意的一点是，在大量进口后，您需要让它静置一段时间。我通过跨几个表向数据库中添加5000行(使用实际导入数据)来测试这一点。它显示了大约一分钟的1800条记录(可能是一个可配置的窗口)

这是基于https://stackoverflow.com/a/2611745/1548557的工作，所以感谢并认可在CTE中使用的查询

我喜欢Daniel Vérité的回答。 但是当你不能使用CREATE语句时，你可以使用bash解决方案，如果你是windows用户，可以使用powershell解决方案:

# You don't need this if you have pgpass.conf
$env:PGPASSWORD = "userpass"

# Get table list
$tables = & 'C:\Program Files\PostgreSQL\9.4\bin\psql.exe' -U user -w -d dbname -At -c "select table_name from information_schema.tables where table_type='BASE TABLE' AND table_schema='schema1'"

foreach ($table in $tables) {
    & 'C:\path_to_postresql\bin\psql.exe' -U root -w -d dbname -At -c "select '$table', count(*) from $table"
}

简单的两步:(注意:不需要改变任何东西-只是复制粘贴) 1. 创建函数

create function 
cnt_rows(schema text, tablename text) returns integer
as
$body$
declare
  result integer;
  query varchar;
begin
  query := 'SELECT count(1) FROM ' || schema || '.' || tablename;
  execute query into result;
  return result;
end;
$body$
language plpgsql;

2. 运行此查询获取所有表的行数

select sum(cnt_rows) as total_no_of_rows from (select 
  cnt_rows(table_schema, table_name)
from information_schema.tables
where 
  table_schema not in ('pg_catalog', 'information_schema') 
  and table_type='BASE TABLE') as subq;

或 按表获取行数

select
  table_schema,
  table_name, 
  cnt_rows(table_schema, table_name)
from information_schema.tables
where 
  table_schema not in ('pg_catalog', 'information_schema') 
  and table_type='BASE TABLE'
order by 3 desc;

我做了一个小的变化，包括所有的表，也是非公共的表。

CREATE TYPE table_count AS (table_schema TEXT,table_name TEXT, num_rows INTEGER); 

CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count  AS '
DECLARE 
    the_count RECORD; 
    t_name RECORD; 
    r table_count%ROWTYPE; 

BEGIN
    FOR t_name IN 
        SELECT table_schema,table_name
        FROM information_schema.tables
        where table_schema !=''pg_catalog''
          and table_schema !=''information_schema''
        ORDER BY 1,2
        LOOP
            FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.table_schema||''.''||t_name.table_name
            LOOP 
            END LOOP; 

            r.table_schema := t_name.table_schema;
            r.table_name := t_name.table_name; 
            r.num_rows := the_count.count; 
            RETURN NEXT r; 
        END LOOP; 
        RETURN; 
END;
' LANGUAGE plpgsql; 

使用select count_em_all();叫它。

希望这对你有用。 保罗

对于那些试图评估他们需要哪一个Heroku计划，又不能等待Heroku的慢行计数器刷新的人来说，一个简单实用的答案是:

基本上你想在psql中运行\dt，将结果复制到你最喜欢的文本编辑器中(它看起来像这样:

 public | auth_group                     | table | axrsosvelhutvw
 public | auth_group_permissions         | table | axrsosvelhutvw
 public | auth_permission                | table | axrsosvelhutvw
 public | auth_user                      | table | axrsosvelhutvw
 public | auth_user_groups               | table | axrsosvelhutvw
 public | auth_user_user_permissions     | table | axrsosvelhutvw
 public | background_task                | table | axrsosvelhutvw
 public | django_admin_log               | table | axrsosvelhutvw
 public | django_content_type            | table | axrsosvelhutvw
 public | django_migrations              | table | axrsosvelhutvw
 public | django_session                 | table | axrsosvelhutvw
 public | exercises_assignment           | table | axrsosvelhutvw

)，然后运行regex搜索并替换，如下所示:

^[^|]*\|\s+([^|]*?)\s+\| table \|.*$

to:

select '\1', count(*) from \1 union/g

这将会给你一个非常类似的结果:

select 'auth_group', count(*) from auth_group union
select 'auth_group_permissions', count(*) from auth_group_permissions union
select 'auth_permission', count(*) from auth_permission union
select 'auth_user', count(*) from auth_user union
select 'auth_user_groups', count(*) from auth_user_groups union
select 'auth_user_user_permissions', count(*) from auth_user_user_permissions union
select 'background_task', count(*) from background_task union
select 'django_admin_log', count(*) from django_admin_log union
select 'django_content_type', count(*) from django_content_type union
select 'django_migrations', count(*) from django_migrations union
select 'django_session', count(*) from django_session
;

(您需要删除最后一个联合，并手动在末尾添加分号)

在psql中运行它，就完成了。

            ?column?            | count
--------------------------------+-------
 auth_group_permissions         |     0
 auth_user_user_permissions     |     0
 django_session                 |  1306
 django_content_type            |    17
 auth_user_groups               |   162
 django_admin_log               |  9106
 django_migrations              |    19
[..]

我通常不依赖于统计数据，尤其是在PostgreSQL中。

SELECT table_name, dsql2('select count(*) from '||table_name) as rownum
FROM information_schema.tables
WHERE table_type='BASE TABLE'
    AND table_schema='livescreen'
ORDER BY 2 DESC;

CREATE OR REPLACE FUNCTION dsql2(i_text text)
  RETURNS int AS
$BODY$
Declare
  v_val int;
BEGIN
  execute i_text into v_val;
  return v_val;
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;