In this article, we will be learning how we can manage the PostgreSQL database. I am covering the following topics.

1. Create New database
2. Alter an existing database
3. Drop an existing database

I have installed PostgreSQL on my workstation. You can read the How to install PostgreSQL on Windows to learn the step-by-step installation of PostgreSQL on windows 10. We will learn how to manage the databases using the pgadmin4 management tool and the psql command-line tool.

We can use CREATE DATABASE statement to create a new database. First, let us understand the syntax of the CREATE DATABASE statement.

Create DATABASE statement

The syntax of CREATE DATABASE is following:

In the syntax, specify the parameter values as follows:

1. db_name: Specify the desired name of the database. The db_name must be specified after CREATE DATABASE statement.
2. OWNER: Specify the username which you want to be the owner of the database. If we do not specify the value of the OWNER parameter, the owner of the database will be the role that had executed the statement.
3. TEMPLATE: Specify the name of the template database which is used to create a new database. If you do not specify the value of the parameter, PostgreSQL creates the new database using the Template1 database.
4. TABLESPACE: Specify the name of the tablespace in which you want to create a database. A tablespace is a location where the database is stored. If you do not specify the name of the tablespace, PostgreSQL will create a database in pg_default tablespace.
5. ALLOW_CONNECTION: This parameter allows us to restrict access to the database. When we set the value to FALSE, the users cannot connect to the database.
6. CONNECTION LIMIT: You can specify the number of the concurrent incoming connections to the database. The default value is -1, which is unlimited.
7. IS_TEMPLATE: This parameter is used when you want to clone the database. If the value of the parameter is set to TRUE, then any user can clone the database. If the value of the parameter is set to FALSE, then only the database owner or superuser can clone it.

Now, let us create a new database using pgAdmin4. When we install the PostgreSQL, the pgAdmin4 installs automatically. Launch pgAdmin4 🡪 Specify the master password to connect to the PostgreSQL.

Once successfully connected, you can access the objects and databases in the Browser pane of the pgAdmin4.

To create a database Right-click on Databases, Hover on New and select Database.

A dialog box named Create-Database opens. The dialog box has different tabs in which you can specify the configuration parameters of the database. We are creating a database named EltechSales so in the General tab, enter the EltechSales as a database name. I have created a user named Nisarg. The PostgreSQL user named nisarg will be the database owner, so select nisarg from the user drop-down box.

In the definition tab, you can specify the encoding, tablespace, database collation, character type, and connection limit. For the EltechSales database, I have set the following values of the above parameters.

1. Encoding: UTF8
2. Template: None.
3. Tablespace: pg_admin.
4. Collation: C.
5. Character type: C.
6. Connection limit: -1

Screenshot

In the Security tab, parameters tab, and Advanced tab, we can configure the advanced configuration parameters. We will learn more about them in my upcoming articles.

If you want to see the CREATE DATABASE statement, click on the SQL tab. The pgAdmin4 generates the definition to create a PostgreSQL database.

Click on the Save button to create the database. Once a database is created, you can view it under the databases section of pgAdmin4. See the following image:

Alternatively, you can run the below query to view the list of databases.

postgres=# select datname from pg_database;

Query output

Also, run the following command on the pSQL command-line tool.

postgres=# \l

Command output.

Now, let us create a database using the pSQL tool.

Create the database using pSQL

We can use CREATE DATABASE statement to create a new database. We will create a database named EltechHR using the pSQL command. First, launch pSQL and connect to the PostgreSQL database server.

Run the following command to create a database. I have not specified any configuration, so the PostgreSQL database will use a template database named template1. I have not specified the owner of the database; therefore, the owner of the database will be Postgres.

postgres=# CREATE DATABASE EltechHR;

Output

As you can see, the database has been created successfully.

Now, let us understand the ALTER DATABASE statement.

ALTER DATABASE statement

We can use the ALTER DATABASE statement to perform the following tasks

1. Rename the database.
2. Change owner of the database.
3. Change the default tablespace of the database.
4. Setup the template database.

First, let us rename the database.

Rename database

We can use the ALTER DATABASE RENAME TO statement to rename the database. The syntax is following:

ALTER DATABASE [old_db_name] RENAME TO [new_db_name];

In the syntax,

1. old_db_name: Specify the old database name. Make sure that the database exists on PostgreSQL.
2. new_db_name: Specify the desired new name of the database.

Suppose we want to rename the EltechHR database to EltechHRDB. Run the following command.

postgres=# ALTER DATABASE EltechHR RENAME TO EltechHRDB;

Query Output

Once the database has been renamed, run the following command to verify that the database name has been changed.

postgres=# \l

Output

As you can see, the database has been renamed successfully.

Change the owner of the database

We can use ALTER DATABASE OWNER TO statement to change the owner of the database. The syntax is following:

ALTER DATABASE [db_name] OWNER TO [owner_name];

In the syntax,

1. db_name: Specify the database name whose owner you want to change.
2. owner_name: Specify the username that you want to use as an owner of the database. Make sure that the username you specified in the command must exist on PostgreSQL.

For example, we want to change the owner of the UKLandRegistery database from Postgres to nisarg. Run the following command.

postgres=# ALTER DATABASE “UKLandRegistery” OWNER TO nisarg;

Query output

Once the query is executed successfully, let us verify that the owner has been changed.

Query

postgres=# \l

Output

As you can see, the owner has been changed.

Change the default tablespace of the database.

We can use ALTER DATABASE SET TABLESPACE statement to change the default tablespace of the database. Before moving the database to a new tablespace, make sure that the new tablespace must be empty. Also, when you change the tablespace, the database will be inaccessible. The syntax is following:

ALTER DATABASE [db_name] SET TABLESPACE [tablespace_name];

In the syntax,

1. db_name: Specify the name of the database.
2. Tablespace_name: Specify the tablespace name in which you want to move the database.

For demonstration, I have created a tablespace named tblspaceEltechSales. Suppose you want to move the EltechSales database to the tblspaceEltechSales database. Run the following query.

postgres=#ALTER DATABASE “EltechSales” SET TABLESPACE “tblspaceEltechSales”;

Query output

Once the query is executed successfully, let us verify that the database has been moved to a new tablespace.

Query

postgres=# \l+ “EltechSales”

Output

As you can see, the EltechSales database has been moved to tblspaceEltechSales.

Setup template database.

We can use the ALTER DATABASE WITH statement to set up a template database. Sometimes you want to create a template or demo database with empty tables and use it as a template database, you can use this option. The syntax is following:

ALTER DATABASE [db_name] WITH IS_TEMPLATE = true;

In the syntax,

1. db_name: Specify the database name that you want to make as a template database.

Suppose we want to use EltechHR as a template database. To do that, run the following query.

postgres=# ALTER DATABASE “EltechSales” WITH IS_TEMPLATE=true;

Once the query is executed, run the following query to view that the EltechHR database is set as a template database.

postgres=# select datname, datistemplate from pg_database;

Query outputQuery Output

As you can see, the value of the datistemplate column for EltechHR is true, which indicates that the database is a template database.

Drop the database

We can use the DROP DATABASE statement to drop the database from PostgreSQL. The syntax is following:

DROP DATABASE db_name

In the syntax,

1. db_name: Specify the name of the database that you want to drop.

Suppose you want to drop the database named EltechHRDB. To do that, run the following query.

Once the query is executed, let us verify that the database has been dropped successfully.

Query

postgres=# select datname, datistemplate from pg_database where datname=’Eltechhrdb’;

Output

The query does not return any record which shows that the database has been dropped successfully.

Summary

This article taught us about how we can manage the PostgreSQL database.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022