PostgreSQL offers a couple of ways to create a new database. The very first step to get started with any database is to learn how to create a database. Once a database is created, then you can perform any specific operation on that database like creating tables, schemas, performing crud operations, and so on.
This write-up will explain multiple commands to create a database in PostgreSQL. So, let’s get started!
How to create a database using CREATE DATABASE?
In PostgreSQL, the “CREATE DATABASE” statement is used to generate a new database. Here is the basic syntax for creating a database in PostgreSQL:
CREATE DATABASE databasename;
Let’s have a look at the below-listed points to understand what we have learned from the above snippet:
- The “CREATE DATABASE” is a predefined command to create a database and the “databasename” is a user-defined database name.
- From the above-given snippet, we can observe that the command is written in uppercase while the database name is written in lowercase.
- We can write the command in lowercase as well. However, it is preferred to use the uppercase syntax for reserved keywords/commands and lowercase syntax for the user-defined names/attributes.
Note: To create a PostgreSQL database, we will execute the “CREATE DATABASE” command from the psql(SQL Shell). However, to create a database, you should be a superuser, or you should have a special privilege to create a database.
How to list the databases?
Open the psql shell and execute the “\l” command to see the list of all databases:
\l
From the above-given snippet, we can observe that by default, we have three databases “postgres”, “templete0”, and “template1”.
How does the CREATE DATABASE command work in PostgreSQL?
Type the below-given command in the psql shell to create a user-defined database named “example”:
CREATE DATABASE example;
The response verifies that the database has been created successfully. We can verify database creation by executing the “\l”command:
\l
The output clarified that the database named “example” has been created successfully.
How to create a database using createdb?
In PostgreSQL, the “createdb” command is used to generate a new database. However, the “createdb” command can run directly from the command prompt while “CREATE DATABASE” can’t. The “createdb” command can add some comments/descriptions to the database altogether.
The basic syntax of the createdb command will go like this:
createdb [argument/option...] [databasename [description]]
Let’s consider the below-listed points for a profound understanding of the createdb command:
- createdb: it is a command that creates a new database in PostgreSQL.
- option: it represents a list of command-line arguments that a createdb command can accept.
- databasename: it is a user-defined database name.
- description: it associates an optional comment/description with the newly created database.
Command-line Options/arguments
The details given below will illustrate the command line arguments that can be associated with the createdb command:
–help
It is used to get help regarding createdb arguments from SQL shell.
-D
It is used to specify the tablespace for a new database.
-e
It displays all the commands sent to the server by the createdb command.
-E
It defines the character encoding to be applied in the database.
-h
It shows the server’s hostname.
-I
It determines which locale will be used in the database.
-O
It specifies the user who will own the database.
-p
It specifies the TCP port that a server utilizes to listen for the connections.
-T
It specifies which database will be used as a template to generate a fresh database. If you didn’t specify the -T argument, then by default, a new database will be created using the “template1”.
-U
It specifies which user name to be used for the connection.
-w
Restricts createdb command to skip the password prompt.
-W
Restricts the createdb command to ask for the password before establishing connecting to a database.
Let’s jump into the practical implementation of createdb command.
How does the createdb command work in PostgreSQL?
Firstly, go to the directory where PostgreSQL is installed and copy its bin directory’s path.
Open the command prompt and go to the bin directory of PostgreSQL using the “cd” command:
Once you are in the bin directory, execute the “createdb” command to create a new database:
createdb -U postgres exampledb;
In this example, we utilized the “createdb” command followed by -U argument that will create a database using default user i.e. “postgres”. While “exampledb” is the user-defined name for the database:
When we executed the above-given command, it asked for the password of the “postgres” user. Enter the password and press the enter button to create the database.
Let’s verify whether the database has been created or not. To do that, open SQL Shell and type the following command:
\t
The output verified that the “createdb” command succeeded in creating the database.
Conclusion
PostgreSQL offers a couple of ways to create a new database, such as “CREATE DATABASE” and “createdb” commands. The difference between both these commands is that the “createdb” command can run directly from the command prompt while “CREATE DATABASE” can’t. Moreover, it can add some comments/descriptions to the database altogether. This write-up considered some examples to explain the working of createdb and CREATE DATABASE commands.
