PostgreSQL - Database

PostgreSQL

In this tutorial we will learn to work with database in PostgreSQL.

What is a database?

A database is an organized collection of data. In case of RDBMS (Relational Database Management System) the data is stored in tables.

Open terminal, start your local PostgreSQL server and run the psql command to login to your database server.

$ psql

List all databases

To list all the databases created in your PostgreSQL server we have to run the following command \l.

# \l
                                     List of databases
     Name     |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------+--------------+----------+-------------+-------------+-----------------------
 postgres     | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 template1    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 yusufshakeel | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

Connect to a database

If we are already logged in to PostgreSQL database server and want to switch from one database to another. Then we use the \c command.

In the following example we are connecting to postgres database from yusufshakeel.

yusufshakeel=# \c postgres
You are now connected to database "postgres" as user "yusufshakeel".
postgres=# 

Create a database

To create a database we use the following command.

# CREATE DATABASE database_name;

In the following example we are creating a new database by the name sampledb.

# CREATE DATABASE sampledb;

If we now list all the databases using the \l command we will get the following output.

# \l
                                     List of databases
     Name     |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------+--------------+----------+-------------+-------------+-----------------------
 postgres     | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sampledb     | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 template1    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 yusufshakeel | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

Create database using createdb command

We can also create a new database using the createdb command.

The createdb is a shell script wrapper for the SQL command CREATE DATABASE.

Syntax:

$ createdb [connection-option...] [option...] [dbname [description]]

Generally when a logged in user creates a database then that user becomes the owner of the database. But if we want to create database for other users then we can pass options when using the createdb command.

In the following example we are creating a database storydb using the createdb command.

$ createdb storydb;

If we now login to the database server and list all the databases we will see the storydb.

$ createdb storydb

$ psql
psql (12.1)
Type "help" for help.

yusufshakeel=# \l
                                     List of databases
     Name     |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------+--------------+----------+-------------+-------------+-----------------------
 postgres     | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sampledb     | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 storydb      | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 template1    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 yusufshakeel | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(6 rows)

To list the options available for createdb command run the following in terminal $ createdb --help

Options for createdb command

Following are the options we can pass to the createdb command.

OptionDescription
dbnameSpecifies the name of the database.
descriptionSpecifies a comment to be associated with the newly created database.
-e
--echo
Echo the commands that was generated and sent to the server by createdb.
-h host
--host=host
Specifies the host name of the machine on which the server is running.
-p port
--port=port
Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections.
-U username
--username=username
User name to connect as.
-w
--no-password
Never prompt for password.
-W
--password
Force createdb command to prompt for a password before connecting to a database. (This name is processed as a double-quoted identifier.)
-O owner
--owner=owner
Specifies the database user who will own the new database.
-l locale
--locale=locale
Specifies the locale to be used in this database.
-E encoding
--encoding=encoding
Specifies the character encoding scheme to be used in this database. Example UTF8.

Now that we know the different options so, we can connect to a database using psql command with those options.

In the following example we are connecting to sampledb on port 5432 using username yusufshakeel.

$ psql -U yusufshakeel -p 5432 sampledb 
psql (12.1)
Type "help" for help.

sampledb=#

Creating a database for this tutorial

In the following example we will create a sample database that we will use for in this tutorial series.

CREATE DATABASE postgres_project with ENCODING 'UTF8';

Rename a database

To rename a database we use the following command.

# ALTER DATABASE old_dbname RENAME TO new_dbname;

In the following example we are renaming the storydb to story.

# ALTER DATABASE storydb RENAME TO story;

If we now list the databases we will see the new name.

# \l
                                     List of databases
     Name     |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------+--------------+----------+-------------+-------------+-----------------------
 postgres     | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sampledb     | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 story        | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 template1    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 yusufshakeel | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(6 rows)

Drop a database

To drop a database we run the following command.

# DROP DATABASE database_name;

In the following example we are dropping story database.

# DROP DATABASE story;

If we now list all the database we will not see the story database.

# \l
                                     List of databases
     Name     |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   
--------------+--------------+----------+-------------+-------------+-----------------------
 postgres     | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sampledb     | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 template1    | postgres     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |              |          |             |             | postgres=CTc/postgres
 yusufshakeel | yusufshakeel | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)