如何找到Postgres中所有表格的行数
我正在寻找一种方法来查找Postgres中所有表格的行数。我知道我可以在与时间做一个表:如何找到Postgres中所有表格的行数
SELECT count(*) FROM table_name;
,但我想看到的,对于所有的表行计数,然后为了得到我的所有表有多大的想法。
有三种方式来获得这种计数,每一个都有自己的权衡。
如果你想要一个真正的计数,你必须执行SELECT语句,就像你对每个表使用的那个一样。这是因为PostgreSQL将行可视性信息保存在行本身而不是其他任何地方,所以任何准确的计数都只能与某些事务有关。你会得到该事务在执行时看到的内容的数量。您可以自动执行此操作,以针对数据库中的每个表运行,但您可能不需要那么高的准确性或想等待那么久。
第二种方法指出,统计信息收集器随时大致追踪有多少行是“活的”(未被删除或被以后的更新废弃)。这个值可以通过大量活动下一个位被关闭,但一般是一个很好的估计:
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
这也可以告诉你有多少行是死的,它本身就是一个有趣的数字监控。
第三种方法是注意系统ANALYZE命令(由PostgreSQL 8.3定期执行的自动清理过程来更新表格统计信息)也计算行估计值。你可以像这样抓住那个:
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中使用这些信息。对于基本计数的目的只是为了看一般事情有多大,要么足够准确。
如果您不介意潜在的陈旧数据,您可以access the same statistics used by the query optimizer。
喜欢的东西:
SELECT relname, n_tup_ins - n_tup_del as rowcount FROM pg_stat_all_tables;
我不记得我从哪里收集这个网址。但希望这可以帮助你:
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();
应该让你行所有表的计数。
引用列名(例如'quote_ident(t_name.relname)')以确保正确支持不寻常的名称(例如“column-name”)是个好主意, 。 – gorsky 2010-08-17 11:05:59
之后放置它:DROP FUNCTION count_em_all(); – 2016-02-12 03:50:38
出现错误:select count_em_all();错误:语法错误位于或靠近“group”LINE 1:SELECT COUNT()AS“count”FROM group^QUERY:SELECT COUNT()AS“count”FROM group CONTEXT:PL/pgSQL function count_em_all EXECUTE语句 – 2016-02-12 03:54:18
不知道答案在bash是您可以接受的,但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
就其本质而言,这可以归结为与table_name中的select count(*)相同;在OP中! – 2013-03-14 14:37:09
我通常不依赖于统计数据,特别是在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();
来调用它。
希望你找到这个有用的。 Paul
错误:“r.table_schema”不是已知变量 – slashdottir 2015-07-24 21:00:03
要获得估算值,请参阅Greg Smith's answer。
为了得到确切的数字,到目前为止的其他答案都受到一些问题的困扰,其中一些问题很严重(见下文)。这里有一个版本,这是希望更好地:
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
的风险,那样会失败。 OTOHpg_class where relkind='r'
应该始终正常工作。COUNT()的类型是
bigint
,而不是int
。可能存在超过21.5亿行的表(尽管如此,对它们运行一个计数(*)是一个坏主意)。不需要为一个函数创建一个永久类型来返回包含多列的结果集。
RETURNS TABLE(definition...)
是一个更好的选择。
我喜欢DanielVérité的answer。 但是当你不能使用CREATE语句,你可以使用一个bash solution或者,如果你是一个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"
}
的哈克,实际的解决人们试图评估哪些的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
),然后运行一个正则表达式搜索和替换这样的:
^[^|]*\|\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
并手动添加末尾的分号)
Ru在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()
。
非常聪明。可惜没有'query_to_jsonb()'。 – klin 2016-07-31 13:12:30
我试过使用这个,但数据确实很陈旧。 – mlissner 2012-11-11 23:37:48
@mlissner:如果您的自动清理时间间隔太长,或者您未在表格上运行手动“ANALYZE”,则统计数据可能会失效。它的数据库负载问题以及数据库配置方式(如果统计信息更新频率更高,统计信息会更准确,但可能会降低运行时性能)。最终,获得准确数据的唯一方法是对所有表运行'select count(*)from table'。 – ig0774 2012-11-12 09:19:21
只是试了一下,它不是正确的答案。 – 2014-10-28 20:33:19