Table of contents
1.
Introduction
2.
How to create a database in PostgreSQL?
2.1.
Using CREATE DATABASE command
2.2.
Using createdb command
3.
FAQs
4.
Key Takeaways
Last Updated: Mar 27, 2024

PostgreSQL Create Database

Author Pakhi Garg
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

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-

  1. Using CREATE DATABASE command on PostgreSQL shell prompt.
  2. 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:

  1. 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.

FAQs

  1. What is the precondition for creating a database in PostgreSQL?
    To create a database in PostgreSQL, either the user must be a superuser or have the appropriate privileges of creating a database.
     
  2. What are the ways of creating a database in PostgreSQL?
    There are two ways of creating a database in PostgreSQL. These are-
    Using CREATE DATABASE command on PostgreSQL shell prompt.
    Using createdb command on command prompt.
     
  3. What is the default template in which the new database is created?
    The default template in which the new database is created is template1. It is an empty database that contains some default database objects.
     
  4. How can we see our databases in PostgreSQL?
    We can see our databases in PostgreSQL using the \l (backslash l) command. \l or \list is a psql tool that lists all the databases present in the current server.

Key Takeaways

In this article, we talked about creating a database in PostgreSQL. We saw two ways of doing this: CREATE DATABASE command and the other with createdb command. We understood both ways through their syntax and examples. 

Attention Reader!!!!! Don’t stop here. Start your DBMS journey with the DBMS course on Coding Ninjas. To master SQL, solve our Top 100 SQL Problems asked in various company interviews.

Happy Learning!

Live masterclass