How to Create a Database in PostgreSQL – A Step-By-Step Guide

Top companies like Netflix, Twitch, Spotify, Uber, and even Instagram use PostgreSQL as their primary database system. If you wish to work in these or similar top companies, this is your call to learn PostgreSQL. Wondering where to begin? How about creating a database first? This blog explores what Postgres is and how to create a database using CREATE DATABASE statement, SQL Shell, and pgAdmin.

What is Postgres?

Postgres is an Object-Relational Database Management System (ORDBMS) developed by the PostgreSQL Global Development Group. It utilizes and extends the SQL language combined with many features that safely store and scale complex data workloads. It has a strong reputation for reliability, data integrity, robust feature set, and extensibility. All of this makes PostgreSQL a great choice of database technology for any size of application, from small single-machine databases to large internet-facing applications with complicated models containing hundreds or thousands of tables across multiple servers/geographical locations.

Take an online SQL course to learn more about different data operations, joins, subqueries, and other functions to build a strong foundation in SQL.

How to Create a Database in PostgreSQL?

Learning how to create a database is the first step in knowing any database management system. So how to create a database in PostgreSQL? We can do this with different methods such as a simple CREATE DATABASE statement, SQL Shell, and pgAdmin.

Using CREATE DATABASE Statement

An easy and simple way to create a database is using the CREATE DATABASE statement. This command will prompt you for the name of the database and then will create (or re-create if one already exists with that name) an empty database on your server. You must have superuser privileges or the ability to create databases by executing this statement.

Steps:

The following are the steps to create a database in PostgreSQL using the CREATE DATABASE statement:

• First, provide the name of the desired database following the CREATE DATABASE keywords and ensure that it is unique on the PostgreSQL database server. Otherwise, an error will be raised if you attempt to use a name already taken by another existing database.
• Afterward, specify one or more parameters for configuring how this newly created Database should operate when used with your other applications and data sources.

Syntax:

The syntax to create a database in PostgreSQL is:

CREATE DATABASE database_name
WITH
   [OWNER = role_name]
   [TEMPLATE = template]
   [ENCODING = encoding]
   [LC_COLLATE = collate]
   [LC_CTYPE = ctype]
   [TABLESPACE = tablespace_name]
   [ALLOW_CONNECTIONS = true | false]
   [CONNECTION LIMIT = max_concurrent_connection]
   [IS_TEMPLATE = true | false]

Parameters:

The above code might seem a little complex if you are creating the database in PostgreSQL for the first time. Therefore, understand the parameters used in the above code with their descriptions given below:

1. OWNER: It assigns the owner of the database. If this option is omitted then the role executing CREATE DATABASE statement will become its owner.
2. TEMPLATE: It determines which existing template database to use as a model for creating a new one (use “template1” by default).
3. ENCODING: It specifies the character set encoding in the newly created database.
4. The LC_COLLATE: The parameter determines the sort order of strings in queries that contain an ORDER BY clause and defaults to the LC_COLLATE of the template database. 
5. The LC_CTYPE: The parameter defines how characters are classified (e.g., lower, upper, or digit) for a new database and is set by default to match with the template database’s LC_CTYPE value.
6. CONNECTION LIMIT: It limits the number of concurrent connections allowed into the said database (-1 equals unlimited). 
7. ALLOW_CONNECTIONS: The parameter is used when “false” prevents users with certain privileges from connecting to the database. 
8. TABLESPACE: It sets the tablespace name used when initializing a freshly made database.
9. IS _TEMPLTE: It uses a boolean value to determine whether any user can clone it or only the superusers/database owners.

Examples:

To clarify things further, we have some examples for you to use the CREATE DATABASE statement to create databases with different settings:

• Create a Database With Default Parameters:

To create a database with default parameters in PostgreSQL, log into the system using any client tool and execute the following statement.

CREATE DATABASE employees

A new database named ’employees’ is created with default parameters from the ‘template1’ template in PostgreSQL using the PSQL client tool. To view all of the databases present on a current PostgreSQL server, use the \l command within PSQL.

• Create a Database With Some Parameters

The CREATE DATABASE command is used to create a database called “employees” with certain parameters. The illustration demonstrates this process.

CREATE DATABASE employees
WITH
ENCODING = 'UTF8'
OWNER = team_admin
CONNECTION LIMIT = 60;

In this example, we created the employees’ database with the encoding UTF8, the owner is team_admin and the number of concurrent connections to the database is 60.

Using SQL Shell

Another method to create a database in PostgreSQL is through SQL shell. It is a command line tool to connect and work with databases. It uses dynamic SQL, that is, the queries are prepared and executed at runtime.

Steps:

Let us learn how to create a database in the PostgreSQL command line interface.

• Launch the SQL Shell. 
• Press “Enter” five times to connect with the chosen database.  
• Utilize the following command to create a new database (called “mydb”):

CREATE DATABASE mydb

• Use \l to obtain an inclusive list of all accessible databases.   
• Connect to the recently created database with this PostgreSQL command:

\c mydb

Once you have logged in to the system, the prompt will shift from its default setting to display “mydb”, indicating that you are now connected and able to interact with the database named “mydb”. Now you can create tables, apply triggers, or run SQL commands within this database.

Using pgAdmin

You can use the pgAdmin tool to create a new database with an easy-to-use interface. Here are the steps to do so.

Steps: 

• First log into the PostgreSQL server with pgAdmin. 
• Next, select the Create > Database menu item by right-clicking on the Databases node. This will open up a dialog box allowing you to enter detailed information about the new database.
• Then enter the name of the database in the ‘General’ tab and choose an owner.
• Go to the Definition tab to configure settings for the database.
• In the Definition tab, you can select an encoding scheme, pick a template to use as the basis for your database design, specify which tablespace and collation should be used in addition to choosing a character type and setting how many connections are allowed.
• The Security tab enables you to set up security labels and grant access rights, while the Privileges tab allows for granting privileges to a specific role.
• Next, select the SQL tab to access and view the generated SQL query that will be executed.
• Finally, click the Save button to create your new database. You will be able to view it in the list of databases afterward.

Conclusion

PostgreSQL is a powerful ORDBMS that provides excellent reliability and data integrity. Thanks to its ability to handle complex workloads and scalability, it can be used for various applications. This blog explains how to create a database in Postgresql using various methods. You can choose to create the database using the CREATE DATABASE statement or with the help of SQL Shell or the pgAdmin tool. Overall, PostgreSQL makes an ideal choice due to its robust features suitable across several different types of projects.