tibsar tibsar - 4 months ago 14
SQL Question

Find names of all tables with more than x rows

I can easily find all the tables of the form

_%_jobs
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?

Answer

Try

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.