tibsar tibsar - 2 months ago 10x
SQL Question

Find names of all tables with more than x rows

I can easily find all the tables of the form

through the following query:

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

I would like to filter this list so that is is only the tables that have more than 200,000 rows.

I have tried:

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

but this results in an error:

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

How can I accomplish this?



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

There maybe slight discrepancies between the numbers reported here and actual. From the manual section on 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.