In Databases like MySQL etc., the “IF NOT EXISTS” option is used with the CREATE DATABASE command to create a database only if it doesn’t exist already. However, PostgreSQL doesn’t support the “IF NOT EXISTS” option for the CREATE DATABASE statement. So, the question is how to check if a database already exists or not.

Well! In PostgreSQL, we can use the subqueries to achieve the functionality of the “IF NOT EXISTS” option. So, let’s learn how to create a database that doesn’t exist.

PostgreSQL CREATE DATABASE IF NOT EXISTS

Let’s understand how to use a subquery to create a non-existing database:

SELECT 'CREATE DATABASE <db_name>'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '<db_name>')\gexec

In the above syntax, the NOT EXIST operator is used within the WHERE Clause, which will check the existence of a targeted database. If the specified database doesn’t exist, then the sub-query will retrieve “True”. In such a case, the CREATE DATABASE statement will execute, and the non-existing database will be created.

Note: \gexec parameter runs the just-entered statements and sends each field as a SQL command to the server instead of printing the output.

Let’s understand it via practical examples.

Example: How to Create a Non-existing Database in PostgreSQL?

This example explains how to achieve the functionality of the “CREATE DATABASE IF NOT EXISTS” via subquery. To do so, you need to follow the below-listed stepwise instructions:

Step 1: List Databases

To get the list of available databases, users must run the “\l” command:

\l

The output shows all the available databases.

Step 2: CREATE DATABASE IF NOT EXISTS

Let’s create a non-existing database named “exp_db” via the following command:

SELECT 'CREATE DATABASE exp_db' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'exp_db')\gexec

The output authenticates that the database named exp_db has been created successfully.

Step 3: Verify Databases

You can verify the database’s creation by executing the following command:

\l

The output verifies that the desired database has been created successfully,

Step 4: Create Already Existing Database

Let’s try to create a new database with the same name and see how Postgres deals with such situations:

SELECT 'CREATE DATABASE exp_db' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'exp_db')\gexec

Executing the above statement didn’t perform any action. This proves that the “exp_db” already exists, so it can’t be created again.

That's all from this Postgres blog post.

Conclusion

Postgres doesn’t support the “IF NOT EXISTS” option for the CREATE DATABASE command. To achieve the functionality of the “IF NOT EXISTS” option, a subquery can be used in Postgres. For this purpose, you can specify the NOT EXIST operator in the WHERE clause to check if the desired database already exists. If the given database doesn’t exist, then the sub-query will retrieve “True”. In such a case, the CREATE DATABASE statement will execute, and the non-existing database will be created. This blog post explained how to create a non-existing database in Postgres.