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

Question

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

我正在寻找一种方法来查找Postgres中所有表的行数。我知道我可以一次做一张桌子：

SELECT count(*) FROM table_name;

但我希望看到所有表格的行数，然后按顺序排列，以了解我所有表格的大小。

Answer 1

有三种方法可以获得这种计数，每种方法都有自己的权衡。如果需要真正的计数，则必须像对每个表使用的那样执行SELECT语句。这是因为PostgreSQL将行可见性信息保留在行本身，而不是其他任何地方，因此任何准确的计数只能与某个事务相关。您将获得该事务在执行时所看到的内容的计数。您可以自动执行此操作以针对数据库中的每个表运行，但您可能不需要那么高的准确度或者想要等待那么久。第二种方法指出，统计信息收集器随时跟踪大约有多少行是“活动的”（未被更新后删除或废弃）。在重度活动下，这个值可能有点偏差，但通常是一个很好的估计：SELECT schemaname,relname,n_live_tup    FROM pg_stat_user_tables    ORDER BY n_live_tup DESC;这也可以显示有多少行已死，这本身就是一个有趣的数字。第三种方法是注意系统ANALYZE命令，它定期执行autovacuum进程，从PostgreSQL 8.3开始更新表统计信息，也可以计算行估计值。你可以像这样抓住那个：SELECT    nspname AS schemaname,relname,reltuplesFROM pg_class CLEFT 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中使用。出于基本的计数目的，只是为了看一般情况有多大，要么都应该足够准确。

Answer 2

这是一个解决方案，不需要函数来获得每个表的准确计数：select&nbsp;table_schema,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;table_name,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(xpath('/row/cnt/text()',&nbsp;xml_count))[1]::text::int&nbsp;as&nbsp;row_countfrom&nbsp;( &nbsp;&nbsp;select&nbsp;table_name,&nbsp;table_schema,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;query_to_xml(format('select&nbsp;count(*)&nbsp;as&nbsp;cnt&nbsp;from&nbsp;%I.%I',&nbsp;table_schema,&nbsp;table_name),&nbsp;false,&nbsp;true,&nbsp;'')&nbsp;as&nbsp;xml_count&nbsp;&nbsp;from&nbsp;information_schema.tables&nbsp;&nbsp;where&nbsp;table_schema&nbsp;=&nbsp;'public'&nbsp;--<<&nbsp;change&nbsp;here&nbsp;for&nbsp;the&nbsp;schema&nbsp;you&nbsp;want)&nbsp;tquery_to_xml将运行传递的SQL查询并返回带有结果的XML（该表的行数）。然后外部xpath()将从该xml中提取计数信息并将其转换为数字派生表并不是必需的，但是xpath()更容易理解 - 否则query_to_xml()需要将整个表传递给xpath()函数。

Answer 3

要获得估算，请参阅Greg Smith的回答。为了得到确切的数字，到目前为止的其他答案都存在一些问题，其中一些是严重的（见下文）。这是一个希望更好的版本：CREATE&nbsp;FUNCTION&nbsp;rowcount_all(schema_name&nbsp;text&nbsp;default&nbsp;'public') &nbsp;&nbsp;RETURNS&nbsp;table(table_name&nbsp;text,&nbsp;cnt&nbsp;bigint)&nbsp;as$$declare &nbsp;table_name&nbsp;text;begin &nbsp;&nbsp;for&nbsp;table_name&nbsp;in&nbsp;SELECT&nbsp;c.relname&nbsp;FROM&nbsp;pg_class&nbsp;c&nbsp;&nbsp;&nbsp;&nbsp;JOIN&nbsp;pg_namespace&nbsp;s&nbsp;ON&nbsp;(c.relnamespace=s.oid) &nbsp;&nbsp;&nbsp;&nbsp;WHERE&nbsp;c.relkind&nbsp;=&nbsp;'r'&nbsp;AND&nbsp;s.nspname=schema_name &nbsp;&nbsp;LOOP&nbsp;&nbsp;&nbsp;&nbsp;RETURN&nbsp;QUERY&nbsp;EXECUTE&nbsp;format('select&nbsp;cast(%L&nbsp;as&nbsp;text),count(*)&nbsp;from&nbsp;%I.%I', &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;table_name,&nbsp;schema_name,&nbsp;table_name); &nbsp;&nbsp;END&nbsp;LOOP;end$$&nbsp;language&nbsp;plpgsql;它将模式名称作为参数，或者public如果没有给出参数。要在不修改函数的情况下使用特定的模式列表或来自查询的列表，可以在查询中调用它，如下所示：WITH&nbsp;rc(schema_name,tbl)&nbsp;AS&nbsp;( &nbsp;&nbsp;select&nbsp;s.n,rowcount_all(s.n)&nbsp;from&nbsp;(values&nbsp;('schema1'),('schema2'))&nbsp;as&nbsp;s(n))SELECT&nbsp;schema_name,(tbl).*&nbsp;FROM&nbsp;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&nbsp;pg_class where relkind='r'应该总能正常工作。COUNT（）的类型bigint不是int。可能存在超过21.5亿行的表（虽然对它们运行计数（*）是个坏主意）。不需要为函数创建永久类型以返回具有多个列的结果集。RETURNS TABLE(definition...)是一个更好的选择。