我正在寻找一种方法,为我在Postgres中所有的表找到行数。我知道我可以一次做一张表:
SELECT count(*) FROM table_name;
但我想看看所有表的行数,然后按它排序,以了解所有表的大小。
我正在寻找一种方法,为我在Postgres中所有的表找到行数。我知道我可以一次做一张表:
SELECT count(*) FROM table_name;
但我想看看所有表的行数,然后按它排序,以了解所有表的大小。
当前回答
如果您在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语句。
其他回答
这里有一个更简单的方法。
tables="$(echo '\dt' | psql -U "${PGUSER}" | tail -n +4 | head -n-2 | tr -d ' ' | cut -d '|' -f2)"
for table in $tables; do
printf "%s: %s\n" "$table" "$(echo "SELECT COUNT(*) FROM $table;" | psql -U "${PGUSER}" | tail -n +3 | head -n-2 | tr -d ' ')"
done
输出应该如下所示
auth_group: 0
auth_group_permissions: 0
auth_permission: 36
auth_user: 2
auth_user_groups: 0
auth_user_user_permissions: 0
authtoken_token: 2
django_admin_log: 0
django_content_type: 9
django_migrations: 22
django_session: 0
mydata_table1: 9011
mydata_table2: 3499
你可以根据需要更新psql -U "${PGUSER}"部分来访问你的数据库
注意,head -n-2语法可能在macOS中不起作用,你可以使用不同的实现
在CentOS 7下的psql (PostgreSQL) 11.2上测试
如果你想按表排序,那就用sort来包装它
for table in $tables; do
printf "%s: %s\n" "$table" "$(echo "SELECT COUNT(*) FROM $table;" | psql -U "${PGUSER}" | tail -n +3 | head -n-2 | tr -d ' ')"
done | sort -k 2,2nr
输出;
mydata_table1: 9011
mydata_table2: 3499
auth_permission: 36
django_migrations: 22
django_content_type: 9
authtoken_token: 2
auth_user: 2
auth_group: 0
auth_group_permissions: 0
auth_user_groups: 0
auth_user_user_permissions: 0
django_admin_log: 0
django_session: 0
我不记得我收集这个的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中的答案对您来说是否可以接受,但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
我做了一个小的变化,包括所有的表,也是非公共的表。
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();叫它。
希望这对你有用。 保罗
要获得估计,请参阅格雷格·史密斯的答案。
为了得到确切的数字,到目前为止,其他答案都受到一些问题的困扰,其中一些问题很严重(见下文)。这里有一个版本,希望更好:
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列输出。
下面是这个函数避免的其他答案中的一些问题:
Table and schema names shouldn't be injected into executable SQL without being quoted, either with quote_ident or with the more modern format() function with its %I format string. Otherwise some malicious person may name their table tablename;DROP TABLE other_table which is perfectly valid as a table name. Even without the SQL injection and funny characters problems, table name may exist in variants differing by case. If a table is named ABCD and another one abcd, the SELECT count(*) FROM... must use a quoted name otherwise it will skip ABCD and count abcd twice. The %I of format does this automatically. information_schema.tables lists custom composite types in addition to tables, even when table_type is 'BASE TABLE' (!). As a consequence, we can't iterate oninformation_schema.tables, otherwise we risk having select count(*) from name_of_composite_type and that would fail. OTOH pg_class where relkind='r' should always work fine. The type of COUNT() is bigint, not int. Tables with more than 2.15 billion rows may exist (running a count(*) on them is a bad idea, though). A permanent type need not to be created for a function to return a resultset with several columns. RETURNS TABLE(definition...) is a better alternative.