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

I always do:\l+and then you can compare the size:new_one | postgres | UTF8
| en_US.UTF-8 | en_US.UTF-8 | | *7869 kB* | pg_default
| template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+| *7869 kB* | pg_default | | | |
| postgres=CTc/postgres | | | template1 | postgres |
UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| *7869 kB* |
pg_default

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

On 17 Jan 2018, at 6:01 PM, pinker <pinker@onet.eu> wrote:

I always do: \l+ and then you can compare the size: new_one | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7869 kB | pg_default | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7869 kB | pg_default | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7869 kB | pg_default

What I am after is something scriptable and data safe.

The size isn’t something I would be comfortable relying on.

Regards,
Graham
—

On 17 Jan 2018, at 5:47 PM, Graham Leggett <minfrin@sharp.fm> wrote:

I need to test whether a database is empty, in other words “createdb” has been executed but no data of any kind appears in that database.

What is the correct postgresql way to do this?

Is there a pg_isempty command or equivalent somewhere?

Does this query look right?

db=# select count(s.nspname) from pg_class c join pg_namespace s on s.oid = c.relnamespace where s.nspname in ('public');
count
-------
0
(1 row)

It is based on the idea that the database is not empty if there are any class entries in the “public” namespace?

Regards,
Graham
—

On Wed, Jan 17, 2018 at 9:10 AM, Graham Leggett <minfrin@sharp.fm> wrote:

db=# select count(s.nspname) from pg_class c join pg_namespace s on s.oid
= c.relnamespace where s.nspname in ('public');
count
-------
0
(1 row)

It is based on the idea that the database is not empty if there are any
class entries in the “public” namespace?

​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.

David J.
​

On 17 Jan 2018, at 6:34 PM, David G. Johnston <david.g.johnston@gmail.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
--

On Wed, Jan 17, 2018 at 9:39 AM, Graham Leggett <minfrin@sharp.fm> wrote:

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)

​Depends on how you want to define empty. You indicated "after createdb"
and createdb creates the public schema.

David J.
​

Graham Leggett wrote:

Hi all,

I need to test whether a database is empty, in other words “createdb”
has been executed but no data of any kind appears in that database.

Why do you want to know?

Depends on how you define empty. If a few functions exist but no
tables, is the database empty? I'd say no. One possible approach is:
if no relations (pg_class rows) exist in namespaces other than
pg_catalog, pg_toast, information_schema; and no functions (pg_proc
rows) exist in any schema other than those three, then the database is
empty. There are a few object types you could create without any
relation and without any function (such as casts, or schemas, or text
search objects) but you probably don't care.

Maybe make sure no extensions are installed also.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Wed, Jan 17, 2018 at 05:47:16PM +0200, Graham Leggett wrote:

I need to test whether a database is empty, in other words “createdb”
has been executed but no data of any kind appears in that database.
What is the correct postgresql way to do this?

There is none, because it highly depends on how you'll define empty.

Please note that even directly after "create database x;" your db might
not be empty, if you had some things created earlier in template1.

Best regards,

depesz