我正在寻找一种方法,为我在Postgres中所有的表找到行数。我知道我可以一次做一张表:
SELECT count(*) FROM table_name;
但我想看看所有表的行数,然后按它排序,以了解所有表的大小。
我正在寻找一种方法,为我在Postgres中所有的表找到行数。我知道我可以一次做一张表:
SELECT count(*) FROM table_name;
但我想看看所有表的行数,然后按它排序,以了解所有表的大小。
当前回答
我不记得我收集这个的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();应该得到所有表的行数。
其他回答
摘自我在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
简单的两步:(注意:不需要改变任何东西-只是复制粘贴) 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;
如果您在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语句。
不确定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_schema, table_name, pg_relation_size('“' | | table_schema | |”“。”“| | table_name | |”“”)/ 1024/1024 size_MB, (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 从information_schema.tables order by size_MB