In Databases like MySQL, you can use the “IF NOT EXISTS” option 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. But thankfully Postgres supports an alternative to the "IF NOT EXISTS" option.

You 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

While creating a database in Postgres, users often encounter an error "Database already exists" that occurs if a database with the defined name already exists.

A query that appears/comes within another query is referred to as a subquery. You can use a subquery to create a non-existing database without encountering the mentioned error. Let's understand how to do it using the below syntax:

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

In this syntax, the NOT EXISTS 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 an 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

When we executed the above statement, it 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.