How to to determine if a postgresql database is empty the correct way

Question

EDIT: The answer which turns up in the first search at stackoverflow is potentially dangerous and wrong for postgresql. I was bitten by it and I deleted a database which was actually filled with a lot of schemas: SQL to check if database is empty (no tables) applies only to mysql.

Postgresql has the feature to "SET search_path TO .specified_name".
This way you can create tables in different "name spaces", which is used by people to put more than "one schema" into one physical postgresql database.

The query in the old answer checks the default namespace. If this empty, it assumes the database is empty. But there can be 20 other database schemes in other search pathes. Which is often used by users, which buy "one database", but run 10 applications in different search pathes in order to avoid additional costs.

So I open the question again. What is the correct way to check if a database is empty in postgresql? To be more specific, how to check if it a "virgin" database just created by createdb, with no access to the physical machine?

Answer 1

As others have commented the linked answer is about MySQL where there is no difference between "database" and "schema".

In Postgres however one instance (installation) can have several database, and each database can have several schemas.

A Postgres instance has at least two database that are essential for it to work: template0 and template1.

Your question isn't clear if you want to check if there are no (non-default) databases or if you want to check if a specific database contains no tables.

Case 1 - check if no databases exist

For that you need to connect to the template1 database (as that is the only one that you know is there. Then you can run this statement:

$ psql -X -U postgres template1
psql (9.6.2)
Type "help" for help.

template1=# select count(*) from pg_database where datname not like 'template%';
 count
-------
    33
(1 row)

template1=#

If that returns 0, then you know that there are no additional databases in your system. Typically there is at least a one database named postgres - that is what the default installation does. But that database does not have to be there.

Case 2 - check if a specific database contains no tables

If you want to check if a specific database contains no tables, you need to connect to that database and check the tables - excluding all system tables. The easiest way is to query pg_class as that contains essentially everything that can create in a database with the exception of stored functions.

$ psql -U postgres your_database 
psql (9.6.2)
Type "help" for help.

postgres=# select count(*)
postgres-# from pg_class c
postgres-#   join pg_namespace s on s.oid = c.relnamespace
postgres-# where s.nspname not in ('pg_catalog', 'information_schema')
postgres-#   and s.nspname not like 'pg_temp%'
postgres-# ;
 count
-------
   464
(1 row)

postgres=#

This counts the number of tables that are not "default" Postgres tables.

The above query treats a database that has many schemas but with not tables in them as empty as well. It depends on your requirements if that means "empty" or not.

You might want to also check pg_proc to make sure no stored function exists and possibly pg_extension pg_foreign_server and some other system catalog tables as well.

---

Unrelated:

Postgres has the feature to SET search_path TO specified_name. This way you can create tables in different "name spaces"

You don't need to change the search path in order to create tables in different schemas:

-- create two schemas
create schema one;
create schema two;

-- create a table in schema one
create table one.some_table;
-- create a table in schema two
create table two.other_table;

The search_path is only there so you don't need to always fully qualify the name of a table.