# PostgreSQL CREATE DATABASE IF NOT EXISTS

> 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…

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.

---
[View this page online](https://www.commandprompt.com/education/postgresql-create-database-if-not-exists/)