Re: Is there a "right" way to test if a database is empty?

On 17 Jan 2018, at 6:34 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> ​That was my original thought - though comparing the size of template1 to the target database should be reasonably safe...
>
> If you do go for object detection you will want to ensure that no schemas other than public exist in addition to ensuring that public is empty. That doesn't prevent people from installing stuff to pg_catalog but normally only extensions would end up there.

What led me here was this, which didn’t work for me, although the idea to not just assume the default namespace is valid:

https://stackoverflow.com/questions/42692674/how-to-to-determine-if-a-postgresql-database-is-empty-the-correct-way

Would it be true to say that if this query returned more than zero rows the database is not empty?

db=# select distinct s.nspname from pg_class c join pg_namespace s on s.oid = c.relnamespace where s.nspname not in ('pg_toast','information_schema','pg_catalog');
nspname
---------
public
(1 row)

Regards,
Graham
--