What we mean to say is that we need a mechanism to check if a database exists on our PostgreSQL server or not. Moreover, there are some situations in which we want a database to be created once we run a query if it does not already exist on our database server. In that case, the “Create Database if not Exists” notation comes into play. This guide aims to introduce you to the usage of this notation with a brief discussion on whether PostgreSQL supports this notation or not. After that, we will share with you a PostgreSQL-supported alternative for this notation.
Can we use the “Create Database if not Exists” Notation in PostgreSQL in Windows 10?
The “Create Database if not Exists” notation is supported by some of the programming languages. With the help of this notation, you can check if a specified database exists on your database server or not, and if it does not exist, then this notation will simply create that database on your server. However, let’s specifically talk about PostgreSQL. PostgreSQL does not support this notation, or in other words, you can say that we cannot use this notation directly in PostgreSQL in Windows 10.
Nevertheless, there are still some ways that can allow you to achieve the very same functionality as you can achieve with this particular notation. To explore more about these workarounds, you will have to continue reading this article.
If not, then which Workaround can we use to achieve the Same Goal?
Since the “Create Database if not Exists” notation cannot be used as it is within the PostgreSQL environment, therefore, we decided to share with you a workaround with which you can achieve the very same functionality. For this workaround, you will need to execute a slightly different variation of this notation in the form of a PostgreSQL query in Windows 10. To understand this variation, you will have to follow through the steps explained below:
Note: Do not forget to log into your PostgreSQL server before following these steps.
Step # 1: Viewing the Existing PostgreSQL Databases in Windows 10:
We all know that we only wish to create a specific database in PostgreSQL if it already exists on our server. The database that we want to be created in this particular case is “myNewDB”. Therefore, we will first try to find out the names of all of our existing PostgreSQL databases to know if such a database already exists on our server or not. To display the names of all the existing PostgreSQL databases, you need to execute the following PostgreSQL query in your psql console:
This query will extract the “datname” attribute from the pg_database of our PostgreSQL server. This attribute contains the names of all the existing databases on the PostgreSQL server. The “SELECT” statement of PostgreSQL will just display the extracted database names on the console as shown in the image below:
You can see from the output shown in the image above that no database with the name “myNewDB” exists on our PostgreSQL server; therefore, we can attempt to create a database with this name on our server in Windows 10.
Step # 2: Creating the PostgreSQL Database if not Exists in Windows 10:
Now, since we have seen that the database that we want to create does not already exist on our PostgreSQL server, therefore, we will have to execute the following query to create that database:
With this query, we will create a database named “myNewDB” that was not already present on our PostgreSQL server on our Windows 10 system. The “SELECT” statement in this query is followed by the “CREATE DATABASE statement”. After that, we have mentioned the name of our new database to be created. You can name it whatever you want. Then, we have written the “WHERE NOT EXISTS” statement that will check whether the specified database exists on the PostgreSQL server or not. All these statements are followed by a sub-query in which we have another “SELECT FROM” statement that will check the pg_database of our PostgreSQL server to confirm the non-existence of the database you are trying to create.
Finally, there is the “\gexec” parameter that completes this query. This parameter is extremely important in this query. This parameter sends your current query buffer to the PostgreSQL server, where each component or attribute of the output of this query is treated as a SQL query instead of a PostgreSQL query. This is, in fact, the main reason behind the working of the “Create Database if not Exists” notation within PostgreSQL. Otherwise, even if you will accidentally omit this parameter, you will not be able to achieve this functionality in PostgreSQL.
If you follow the whole syntax of this query correctly, then a PostgreSQL database with the specified name will be successfully created on the PostgreSQL server that you can confirm from the output response shown in the image below:
Step # 3: Verifying if the New PostgreSQL Database has been created in Windows 10 or not:
If you are still not sure that your attempt of the creation of the new PostgreSQL database in the above-mentioned step was successful or not, then you can still verify it by taking a look at all the existing PostgreSQL databases again. This time, you will also be able to find your new database over there. You just need to run the following query to see this:
A total of nine databases exist on our PostgreSQL server at the moment, and the most recent one is, in fact, the one that we have just attempted to create, as highlighted in the image shown below:
Conclusion:
This article talked about the “Create Database if not Exists” notation and its usage. Then we discussed if this notation is supported by PostgreSQL or not. After finding out that we cannot use this notation directly in PostgreSQL, we shared with you a method to achieve the very same functionality while staying within the PostgreSQL environment. Once you go through this method, you will understand this extremely useful alternative of the “Create Database if not Exists” notation which PostgreSQL fully supports.
