Introduction
PostgreSQL is a powerful, open-source object-relational database management system (ORDBMS). It is used to store data securely. Some features of PostgreSQL includes -
- It runs on all major operating systems like Windows, Linux, Unix, Mac OS etc.
- It supports text, image, sound, video and includes interfaces for many languages like C, C++, Java etc.
- It supports many features of SQL like complex SQL queries, foreign keys, triggers, views, transactions, concurrency etc.
- In PostgreSQL tables can be set to inherit their characteristics from a “parent table”.
- We can install several extensions to add additional functionality to PostgreSQL.
We will explore these features later. Let’s first understand how to create a database in PostgreSQL.
Source: www.meme-arsenal.com
How to create a database in PostgreSQL?
For creating a Database, one point to note here is, either we must have the privilege of creating a database or we must be a superuser.
There are two ways to create a database in PostgreSQL. These are-
- Using CREATE DATABASE command on PostgreSQL shell prompt.
- Using createdb command on command prompt.
We will discuss both these ways one by one.
Using CREATE DATABASE command
We can simply create a database using the CREATE DATABASE command. We just need to open the PostgreSQL shell prompt and enter our password.
The syntax of CREATE DATABASE command is-
CREATE DATABASE database_name;
The new database created with this command will be a replica of the standard system database template1. We can also specify a template name by writing: Template = template_name.
Note: You can create a virgin database containing only the standard objects predefined by your PostgreSQL version by using TEMPLATE template0. This is useful if you don't want to copy any installation-local objects that were added to template1.
Now, let’s get into this command with an example.
We will create a new database named employee in PostgreSQL.
postgres=# CREATE DATABASE employee;
postgres=#
This command will create the employee database. We can verify this using the \l (backslash l) command.
Note: \l or \list is a psql tool that lists all the databases present in the current server.
postgres=# \l
The output of the above query will be-

As you can see, the employee database is present.
Now, we will move forward to the next way, i.e., createdb command.
Using createdb command
If you don’t want to open the PostgreSQL shell prompt, you can also create a database using the command prompt present in your system.
For doing this, first, open the command prompt -> Go to the directory where PostgreSQL is installed -> Go to the bin directory.
The syntax of createdb command is-
createdb [options] [database_name [description] ];
The createdb command may contain three parameters defined below:
- options: These contain the command-line arguments which createdb command accepts. These include-
S.No |
Command line argument |
Description |
1. | -D tablespace | Specifies the default tablespace for the database. |
2. | -e | Echo the commands that createdb generates and sends to the server. |
3. | -E encoding | Specifies the character encoding scheme to be used in this database. Example - ‘SQL-ASCII’ |
4. | -h host | Specifies the hostname of the machine that is running the server. |
5. | -l locale | Specifies the locale (date and time format conventions, monetary conventions etc.) to be used in the database. |
6. | -p port | Specifies the TCP port on which the server is listening for connections. |
7. | -T template | The name of the template which must be used to create the new database. |
8. | -U username | Username to connect. |
9. | -w | Never issue a password prompt. |
10. | -W | Asks createdb to prompt for the user’s password before connecting to the database. |
11. | --help | Display help about createdb command line argument and exit. |
2. database_name: The name of the new database to be created.
3. description: It specifies a comment to be affiliated with the newly created database.
Let’s run a query to understand the createdb command.
We will create a new database named student using the following command.
createdb -h localhost -p 5432 -U postgres student
Password: *****
As soon as you hit enter after writing the createdb line, the command prompt will ask you to enter your PostgreSQL admin user password. Just type the password and hit enter.
Once the database is created, you can verify this using the \l command on the PostgreSQL shell prompt.
postgres=# \l
The output of the above query will be-

The difference between the CREATE DATABASE command and createdb command is -
- CREATE DATABASE command is a SQL command. In contrast, createdb is a wrapper around the SQL command CREATE DATABASE.
- CREATE DATABASE command can run from the PostgreSQL shell prompt, while createdb command can run from the command prompt. Moreover, the createdb command allows us to add comments into the database in a single command.