Thread: Is there a "right" way to test if a database is empty?
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 inthat database. What is the correct postgresql way to do this? Is there a pg_isempty command or equivalent somewhere? Regards, Graham —
Attachment
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 the PostgreSQL - hackers mailing list archive at Nabble.com.
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
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
—
Attachment
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
—
Attachment
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:
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
--
Attachment
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