我正在寻找一种方法,为我在Postgres中所有的表找到行数。我知道我可以一次做一张表:
SELECT count(*) FROM table_name;
但我想看看所有表的行数,然后按它排序,以了解所有表的大小。
我正在寻找一种方法,为我在Postgres中所有的表找到行数。我知道我可以一次做一张表:
SELECT count(*) FROM table_name;
但我想看看所有表的行数,然后按它排序,以了解所有表的大小。
当前回答
这对我很有效
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;
其他回答
如果您在psql shell中,使用\gexec允许您执行syed的答案和Aur的答案中描述的语法,而无需在外部文本编辑器中手动编辑。
with x (y) as (
select
'select count(*), '''||
tablename||
''' as "tablename" from '||
tablename||' '
from pg_tables
where schemaname='public'
)
select
string_agg(y,' union all '||chr(10)) || ' order by tablename'
from x \gexec
注意,string_agg()既用于分隔所有语句之间的联合,也用于将分隔的数据箭头粉碎为一个单元,以便传递到缓冲区。
\ gexec 将当前查询缓冲区发送到服务器,然后将查询输出的每一行的每一列(如果有的话)视为要执行的SQL语句。
对于那些试图评估他们需要哪一个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
[..]
我喜欢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"
}
摘自我在GregSmith的回答中的评论,使其更具可读性:
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 3 DESC;
感谢@ a_horis_with_no_name
下面是一个解决方案,它不需要函数来获得每个表的精确计数:
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()函数。