sql - 查找超过 x 行的所有表的名称

Question

我可以通过以下查询轻松找到 _%_jobs 形式的所有表:

select * from pg_tables where tablename like '_%_jobs' 

我想过滤此列表，以便只包含行数超过 200,000 的表。

---

我试过:

select * from pg_tables where tablename like '_%_jobs' having count(*) > 200000

但这会导致错误:

ERROR: column "pg_tables.schemaname" must appear in the GROUP BY clause or be used in an aggregate function

我怎样才能做到这一点？

Answer 1

尝试

SELECT relname, reltuples from pg_class where relname like '_%_jobs' 

此处报告的数字与实际数字之间可能存在细微差异。来自 reltuples 上的手册部分:

Number of rows in the table. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.