Creating and Managing Databases in PostgreSQL

Introduction

PostgreSQL is one of the most powerful and widely used open-source relational database management systems (RDBMS). Whether you are a developer, database administrator, or IT enthusiast, understanding how to create and manage databases in PostgreSQL is essential. This guide covers the fundamental steps of database creation, management, and best practices to ensure smooth operations.

Prerequisites

Before proceeding, ensure you have:
– PostgreSQL installed on your system
– Access to a PostgreSQL instance with appropriate user privileges
– Basic knowledge of SQL commands

A. Creating a Database in PostgreSQL

After PostgreSQL database engine installation, there have been already three databases exist as shown below.

postgres=# \l
                                                      List of databases
    Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   
-------------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 postgres    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 template0   | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
             |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1   | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
             |          |          |                 |             |             |        |           | postgres=CTc/postgres
(3 rows)

To list database has already crated on PostgreSQL we will use command line :

\l

or

SELECT datname FROM pg_database;

or to display databases along with their owner, encoding, collation, and access privileges, run:

SELECT datname, pg_catalog.pg_get_userbyid(datdba) AS owner, encoding, datcollate, datctype, datacl 
FROM pg_database;

If we are outside psql, we can list databases using:

psql -U postgres -c "\l"

PostgreSQL provides multiple methods to create a database: via SQL commands and using the `psql` command-line tool.

1. Using SQL Command

To create a new database, use the following SQL statement:

CREATE DATABASE name
[ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ STRATEGY [=] strategy ]
[ LOCALE [=] locale ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ BUILTIN_LOCALE [=] builtin_locale ]
[ ICU_LOCALE [=] icu_locale ]
[ ICU_RULES [=] icu_rules ]
[ LOCALE_PROVIDER [=] locale_provider ]
[ COLLATION_VERSION = collation_version ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ]
[ OID [=] oid ]

To create a database, we must be a superuser or have the special CREATEDB privilege.
More comprehensive information about database creation and its all parameters on PostgreSQL can be refers to this article.

To create a new database, use the following SQL statement:

CREATE DATABASE databaseku01;

This command creates a new database named databaseku01 with default configurations.

postgres=# CREATE DATABASE databaseku01;
CREATE DATABASE
postgres=# \l
                                                       List of databases
     Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   
--------------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 databaseku01 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 postgres     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 template0    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
              |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
              |          |          |                 |             |             |        |           | postgres=CTc/postgres
(4 rows)

2. Using the Command-Line Interface (psql)

Alternatively, you can create a database from the `psql` command-line tool:

createdb mydatabase

This command does the same as the SQL statement but is executed in the terminal.

postgres@bckinfo:~$ createdb databaseku02
postgres@bckinfo:~$ psql -U postgres -c "\l"
                                                       List of databases
     Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   
--------------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
 databaseku01 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 databaseku02 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 postgres     | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | 
 template0    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
              |          |          |                 |             |             |        |           | postgres=CTc/postgres
 template1    | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c/postgres          +
              |          |          |                 |             |             |        |           | postgres=CTc/postgres
(5 rows)
 

The discussion above explains how to create a database in PostgreSQL with various methods. For a more detailed understanding, it is recommended that you try it in your own environment.

 B. Managing Databases in PostgreSQL

After creating a database, you need to manage it efficiently. Below are key management tasks, after creating a database, you need to manage it efficiently. Below are key management tasks:

4. Dropping a Database
To remove a database permanently: DROP DATABASE mydatabase; Alternatively, use the command-line tool:
dropdb mydatabase

Conclusion

On this short tutorial, we have just learned how to use reserved words like: connecting database, renaming and drop databases,