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.
| Option | Description |
|---|---|
dbname | Specifies the name of the database. |
description | Specifies 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)
ADVERTISEMENT