I am using PostgreSQL in which there are 1000 tables but there is only 50 table which contain data so I want to filter that table and select only those table which has data in it.
I am not getting idea how to do that.
Add a comment
|
2 Answers
You can use the information schema for that but be sure to VACUUM the database first (in the example I created indexes to force update);
SELECT t.tablename, c.reltuples from pg_tables t
JOIN pg_class c
ON c.relname = t.tablename
WHERE SCHEMANAME = 'public'
AND reltuples > 0;
2 Comments
Frank Heikens
ANALYZE is more important, VACUUM only touches dead tuples.
Craig Ringer
and note that this isn't guaranteed to return non-zero for a non-empty table. ANALYZE might sample pages that contain only dead rows; that doesn't mean there are no live rows.
This is the SQL query to find all table names:
SELECT tablename FROM pg_tables WHERE schemaname = 'public'
and this is the SQL to return the number of rows in a table:
SELECT COUNT(*) FROM table
where table is the name of the table. You want to combine these. There are several ways:
1) Write a stored procedure in PL/pgSQL. Basically you would have to loop over the results of the SELECT tablename... query, and for each table name construct the corresponding second query using the EXECUTE statement (details are here).
2) Use a PostgreSQL client library provided by your favourite programming language (e.g. Psycopg2 for Python) and implement the same logic described above.
I hope you don't mind if I do not provide code, this should get you started.
