PostgreSQL
In this tutorial we will learn to work with database in PostgreSQL.
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
$ psql
To list all the databases created in your PostgreSQL server we have to run the following command \l.
\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)
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.
\c
In the following example we are connecting to postgres database from yusufshakeel.
postgres
yusufshakeel
yusufshakeel=# \c postgres You are now connected to database "postgres" as user "yusufshakeel". postgres=#
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.
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)
We can also create a new database using the createdb command.
createdb
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.
storydb
$ 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
$ createdb --help
Following are the options we can pass to the createdb command.
dbname
description
-e
--echo
-h host
--host=host
-p port
--port=port
-U username
--username=username
-w
--no-password
-W
--password
-O owner
--owner=owner
-l locale
--locale=locale
-E encoding
--encoding=encoding
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.
5432
$ psql -U yusufshakeel -p 5432 sampledb psql (12.1) Type "help" for help. sampledb=#
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';
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.
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)
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.