CODEWITHSUNDEEP
sqlpostgresql
Sara Fuerst
Sara Fuerst

Reputation: 6098

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?

Upvotes: 2

Views: 408

Answers (1)

e4c5
e4c5

Reputation: 53774

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.

Upvotes: 3

Related Questions