CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Mayur
Mayur

Reputation: 1143

how to filter table according to data in postgres?

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.

Upvotes: 1

Views: 5068

Answers (2)

András Aszódi
András Aszódi

Reputation: 9660

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.

Upvotes: 2

Jakub Kania
Jakub Kania

Reputation: 16487

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;

fiddle

Upvotes: 3

Related Questions