如何在Postgres中找到所有表的行数

如果您不介意可能过时的数据，可以使用访问查询优化器使用的相同统计信息。

喜欢的东西:

SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;

有三种方法可以得到这种计数，每种方法都有各自的权衡。

如果您想要一个真实的计数，您必须执行SELECT语句，就像对每个表使用的语句一样。这是因为PostgreSQL将行可见性信息保存在行本身，而不是其他任何地方，因此任何准确的计数只能相对于某些事务。您将获得该事务在执行时所看到的内容的计数。您可以对数据库中的每个表自动执行此操作，但您可能不需要这种级别的准确性，也不希望等待那么长时间。

WITH tbl AS
(SELECT table_schema,
TABLE_NAME
FROM information_schema.tables
WHERE TABLE_NAME not like 'pg_%'
AND table_schema in ('public'))
SELECT table_schema,
TABLE_NAME,
(xpath('/row/c/text()', query_to_xml(format('select count(*) as c from %I.%I', table_schema, TABLE_NAME), FALSE, TRUE, '')))[1]::text::int AS rows_n
FROM tbl
ORDER BY rows_n DESC;

第二种方法指出，统计信息收集器大致跟踪有多少行是“活的”;(不删除或过时的后期更新)在任何时候。这个值在剧烈活动时可能会偏离一点，但通常是一个很好的估计:

SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

这还可以显示有多少行已死，这本身就是一个值得监视的有趣数字。

第三种方法是注意到系统ANALYZE命令，从PostgreSQL 8.3开始由autovacuum进程定期执行以更新表统计信息，它也计算行估计。你可以像这样抓取它:

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'
ORDER BY reltuples DESC;

很难说使用这些查询中哪一个更好。通常，我根据是否有更多有用的信息也想在pg_class中使用，还是在pg_stat_user_tables中使用来做出决定。出于基本的计数目的，只是为了了解事物的总体大小，这两种方法都应该足够准确。

我不记得我收集这个的URL了。但希望这能帮助到你:

CREATE TYPE table_count AS (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
c.relname
FROM
pg_catalog.pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE
c.relkind = ''r''
AND n.nspname = ''public''
ORDER BY 1
LOOP
FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.relname
LOOP
END LOOP;


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

执行select count_em_all();将获得所有表的行数。

不确定答案在bash < em > < / em >对你来说是否可以接受，但是FWIW…

PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
SELECT   table_name
FROM     information_schema.tables
WHERE    table_type='BASE TABLE'
AND      table_schema='public'
\""
TABLENAMES=$(export PGPASSWORD=test; eval "$PGCOMMAND")


for TABLENAME in $TABLENAMES; do
PGCOMMAND=" psql -h localhost -U fred -d mydb -At -c \"
SELECT   '$TABLENAME',
count(*)
FROM     $TABLENAME
\""
eval "$PGCOMMAND"
done

我通常不依赖于统计数据，尤其是在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;

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

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();来调用它。

要获得估计，请参阅Greg Smith的回答。

为了得到确切的数字，到目前为止，其他答案都受到一些问题的困扰，其中一些问题很严重(见下文)。这里有一个版本，希望更好:

CREATE FUNCTION rowcount_all(schema_name text default 'public')
RETURNS table(table_name text, cnt bigint) as
$$
declare
table_name text;
begin
for table_name in SELECT c.relname FROM pg_class c
JOIN pg_namespace s ON (c.relnamespace=s.oid)
WHERE c.relkind = 'r' AND s.nspname=schema_name
LOOP
RETURN QUERY EXECUTE format('select cast(%L as text),count(*) from %I.%I',
table_name, schema_name, table_name);
END LOOP;
end
$$ language plpgsql;

它接受模式名作为参数，如果没有给出参数，则接受public。

要使用特定的模式列表或来自查询的列表而不修改函数，可以从查询中调用它，如下所示:

WITH rc(schema_name,tbl) AS (
select s.n,rowcount_all(s.n) from (values ('schema1'),('schema2')) as s(n)
)
SELECT schema_name,(tbl).* FROM rc;

这将生成一个包含模式、表和行计数的3列输出。

下面是这个函数避免的其他答案中的一些问题:

• 表和模式名不应该不加引号就被注入到可执行SQL中，无论是使用quote_ident还是使用更现代的format()函数和它的%I格式字符串。否则，一些恶意的人可能会将他们的表命名为tablename;DROP TABLE other_table，这是一个完全有效的表名。

• 即使没有SQL注入和有趣的字符问题，表名也可能以大小写不同的变体形式存在。如果一个表名为ABCD，另一个表名为abcd，则SELECT count(*) FROM...必须使用引号，否则它将跳过ABCD并计算abcd两次。格式%I自动完成此操作。

• information_schema.tables列出了除表之外的自定义复合类型，即使table_type为'BASE TABLE'(!)。因此，我们不能在information_schema.tables上进行迭代，否则我们可能会有select count(*) from name_of_composite_type的风险，而这会失败。OTOH pg_class where relkind='r'应该总是工作正常。

• COUNT()的类型是bigint，而不是int。可能存在超过21.5亿行的表(但是对它们运行count(*)是一个坏主意)。

• 不需要为函数创建永久类型以返回带有多列的结果集。RETURNS TABLE(definition...)是一个更好的选择。

# 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"
}

对于那些试图评估他们需要哪一个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 \|.*$

:

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
;

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

在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
[..]

下面是一个解决方案，它不需要函数来获得每个表的精确计数:

select table_schema,
table_name,
(xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
select table_name, table_schema,
query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
from information_schema.tables
where table_schema = 'public' --<< change here for the schema you want
) t

query_to_xml将运行传递的SQL查询并返回带有结果的XML(该表的行数)。外层的xpath()将从该xml中提取计数信息并将其转换为一个数字

派生表并不是真正必要的，但它使xpath()更容易理解——否则整个query_to_xml()将需要传递给xpath()函数。

简单两步:
(注意:不需要改变任何东西-只是复制粘贴)
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;

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;