Re: all empty tables

On 15/11/2009 5:04 AM, Merlin Moncure wrote:
> On Sat, Nov 14, 2009 at 6:01 AM, Craig Ringer
> <craig(at)postnewspapers(dot)com(dot)au> wrote:
>> On 14/11/2009 6:12 PM, Garry Saddington wrote:
>>> How could I list all the tables in a database that do not contain any data?
>>> I have looked at reltuples but can't quite work out how to use it, any
>>> pointers would be much apreciated.
>>
>> Define "empty". In a MVCC database, it's harder than you'd think.

> If all you care about is if a table has no rows visible to the
> current transaction, wouldn't:
> select coalesce((select 1 from the_table limit 1), 0)::bool as not_empty;
>
> be an efficient way to do it (hooking into your idea to get the list
> of tables to check above)?

Yep. You could wrap something like that up in a PL/PgSQL function that
looped over the list of table names obtained from pg_class (with schema
names from pg_namespace) and EXECUTEd a query built for each one.

--
Craig Ringer