PostgreSQL database can be accessed and manipulated by using an interactive terminal prompt named psql. When dealing with a database, there are many scenarios in which you want to perform various operations which can be done by executing the commands on the psql terminal.

In this article, we will have a look at some of the most important psql commands that you should be aware of. This will include how to connect from the command line, how to check the access of a user, add/remove access, DB replication state check, how to check schema, tables, describe, take backup, check connections, and many more.

Connect to the database

For connecting to a particular existing database on the postgres database server as an existing user, we can use the command of

psql -d nameOfDatabase -U nameOfUser -W

For example, when our database name is “bitarray_db” and the user name is “payal“, we can connect to the database using the command –

Psql -d bitarray_db -U payal -W

Execution of above command on psql shell gives following output –

Switch to another database connection from existing

When the database to connect resides on the other host, we can make the use of the same psql command to establish the connection but along with that, we will have to specify the -h option for the specification of host details. For example, if my hostname is bitarray2 then the command that can be used to connect to bitarray_db database using “payal” user with the help of psql command will be –

psql -h bitarray2 -d bitarray_db -U payal -W

Execution of above command on psql shell gives below output –

Alternatively, you can keep on entering all the details of the database you want to connect to and you will reach a shell prompt. For example, when wanted to connect to postgres database, we can enter the following details

One more way to connect to a different database from the current one is \c and the name of the database to connect to. For example, if we want to switch to bitarray_db database, then we can execute the following command –

\c bitarray_db

The execution of the above command makes us switch to bitarray_db database

Create a new user in the database

Let us create a new user by using the CREATE USER command. We will create a new user named payal by using the below command –

CREATE USER payal with PASSWORD “payal@123”;

The execution of the above command gives the following output

Create a new database

For creating a new database, you can make use of the syntax

CREATE DATABASE nameOfDB options

For example, for creating a database named bitarray_db, we can execute the following command –

CREATE DATABASE bitarray_db
WITH
OWNER = payal
ENCODING = 'UTF8'
LC_COLLATE = 'English_India.1252'
LC_CTYPE = 'English_India.1252'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;

The execution of the above command gives the following output –

Check the users by listing their details containing their names and corresponding roles

In order to list out all the existing users on the database server of psql along with their corresponding roles, we can execute the below psql command –

\du

The execution of the above command gives the following output –

Add new privilege access to the user

In order to give privileges or access to users, we can run the GRANT command. For example, if we want to assign all the privileges to the newly created user “payal” on postgres database then we can execute the following command

GRANT ALL PRIVILEGES ON DATABASE postgres to payal;

Execution of the above command gives the following output

Remove existing access from the user

In order to remove the existing privileges assigned to a particular user or role, we can go for the execution of the revoke command. For example, to remove the access privileges or all privileges from payal user on postgres database, we can execute the below command on psql shell –

REVOKE ALL PRIVILEGES ON DATABASE postgres FROM payal;

This results in the following output after execution –

List out all the available databases

In order to check the existing databases on the postgres SQL server, there is a provision of the command  \l  which results in a list of available databases. For example, if we execute the command on my terminal the output received will contain the default database named postgres and the newly created one which is bitarray_db along with the template0 and template1 default databases as shown in the below image

Check the existing schemas by listing them

For checking the available existing schemas on the database server, we can make use of postgres command \dn which lists out all the existing schemas. For our database server, the execution of \dn commands results in the following output –

List out the existing tables in the postgres SQL database

For listing out the tables present in the current database, we can execute the command \dt which then lists out all the existing tables of the current database along with the details of the owner of the table and the type of entity in the database which remains to be a table. Executing this command in our existing database results in the following output containing only a single record as we had only one table named articles in our current database postgres.

Create a view in the current database

For this, we will create one view from the articles table by using the below command –

Create view sample as (select * from articles);

The execution results in the following output –

Let us create one more view named sample2 –

Create view sample2 as (select * from articles where Article_id = 1);

This results in the following output –

List out existing views in the database

For listing out all the existing views in the current database, we can execute the command \dv

When we execute this command in our database, we get both the views that we created now listed in it as shown below –

Creating a function in the existing database –

Let us create one function to see how we can list all the functions. In order to create the function in psql we can make use of CREATE FUNCTION command. We will create sample_article_function function using the below command

create function get_article_id()
returns int
language plpgsql
as
$$
declare
articleValue;
begin
select
Article_id
into articleValue
from articles
where topic = ‘p’;
return articleValue;
end;
$$;

The execution of the above function and running it on pgadmin gives the following output –

List out existing functions of the database

In order to check the list of all the functions available in the database, we can execute \df command the results in the following output –

Describe the table to get the details of it

To describe the tables to get the details about the same, we can use the command \d and the name of the table. For example, to describe the articles table, we can execute the below command

\d articles; Execution of above command gives following output –

Take the backup of the database

pg_dump command is used for exporting the specified database and taking the backup in the .sql format file. For example, if we want to take a backup of bitarrya_db database in the file named bit_backup.sql then we can execute the following command

pg_dump bitarray_db > bit_backup.sql

The execution of the above command gives the following output along with the creation of bit_backup.sql file in the existing directory

Check the existing connection

In order to check the details of the existing connections, we can execute the command \conninfo which lists out the name of the host, port address, user, and the name of the database that is being used by the current connection. For example, when we execute the command \conninfo, we get –

Get additional information and help about the psql commands

To get additional help about psql commands, we can execute \? Command which gives following output –

Set the execution time visible during the execution of queries

If you want to have a look at the details of execution time for each and every command that you fire, you can set the execution time on by the command \timing. When we execute this command following output can be seen –

Change the options of output

In psql, there is a provision to change the format of the output. For example, if we make use of the command \a then the alignment of all columns will be non-aligned in the output. When \H  command is used it results in the output being shown in the HTML format.

Creating a new table in PostgreSQL

For creating a new table in the existing current database being used, you can make the use of CREATE TABLE command and list out the details of all the columns and description corresponding to them in it. For example, if we want to create a table named articles, then we can execute the below create table command –

CREATE TABLE articles ( Article_id SERIAL PRIMARY KEY, topic VARCHAR(20) NOT NULL, technology VARCHAR(20) NOT NULL);

The execution of the above command results in the following output –

Fetching the data from the table

In order to get all the existing records in the particular table, we can make use of the select from command. We can select the specific columns or all and apply constraints while retrieving the records if we want to. For example, if we want to retrieve the records of the table articles, we can simply execute the below command –

select * from articles;

This results in the following output after execution as it contained only a single record for now –

Creating the index on a particular table

For creating an index, we can make use of CREATE INDEX name_of_index ON tablename (list of columns) command which needs to be specified the name of the table and column on which index is to be created. For example, we want to create an index named topicIndex on the columns article_id and topic of the table articles, then we can make the use of the below command –

CREATE INDEX topicIndex ON articles (Article_id, topic);

Execution of above command gives following output –

Modify the existing PostgreSQL table

Alter table command is used in case you want to make any changes in the structure of the table and the contained columns. For example, in order to change the type of column topic in the articles table, we can execute the following command –

ALTER TABLE articles
ALTER COLUMN topic varchar2(100);

After execution, we get the following output –

Exit from the psql server

In order to exit from the psql server that we connected to, we can simply type in the command \q which then confirms if we want to terminate from the current batch, and when typed y it closes the terminal or command prompt where the shell was opened.