Psql commands

In this section, we will see the list of most commonly used psql commands, which help us to query data from the PostgreSQL database server faster and more effectively.

In SQL shell(psql), we have n-numbers of commands, but here we will talk about some of the most commonly used commands.

Connect to PostgreSQL database If we want to connect the PostgreSQL database under a particular user, we will follow the below process:

Firstly, we will open the command prompt into our local system and copy the path where we install the PostgreSQL.

Then we will use the below command:

psql -d database -U user -W  

Once we write the above command and press Enter key, the PostgreSQL will request the user’s password.

For example

To connect to javatpoint database under Postgres user, we use the below command:

C:\Program Files\PostgreSQL\12\bin>psql -d javatpoint -U postgres -W  
Password:  

Output

As we can see in the below screenshot:

If we want to connect to a database that exists in on another host, we add the -h option in the above command:

C:\Program Files\PostgreSQL\12\bin>psql -d javatpoint -U postgres -W  

Password:  

In case, we need to use SSL mode for the connection, define it as shown in the below command:

psql -U user -h host "dbname=db sslmode=require"  

• Switch connection to a new database

Once we are connected to a database, we can switch the connection to a new database under a user-specified by the user.

\c database_name   

The above statement will connect to javatpoint database under postgres user:

postgres=# \c javatpoint  

• List available databases

To list all databases in the current PostgreSQL database server, we will use the below command:

\l  

For example

If we execute the above command, we will get the below output to see the existing database present in the PostgreSQL.

• List available tables

To list all tables in the current database, we will use the below command:

\dt  

For example

If we execute the above command, we will get the below output where we can see the existing database present in the PostgreSQL.

Note: The above command specifies only those tables which are presently linked to the database.

• Describe a table

The below command is used to describe a table such as a type, column, modifiers of columns, etc.

\d table_name  

For example, suppose we have one existing table called Station1, and we want to describe it with the help of the above command:

\d Station1  

Output

After executing the above command, we will get the below output:

• List available functions

We can use the following command to see the available list of functions in the existing database.

\df  

Output

If we execute the above command, we will get the below output window:

• List available schema

The below command is used to see the available list of all schemas of the currently connected database.

\dn  

Output

After executing the above command, we will get the below output window:

• List users and their roles

In psql, we will use the following command to list all users and their assigned roles.

\du  

Output

After executing the above command, we will get the below output window:

• List available views

We will use the following command to get a list of available views in the existing database.

\dv  

Output

We will get the below output once we implement the above command:

• Perform the previous command

We will use the version() function in the following command to recover the existing version of the PostgreSQL server.

SELECT version();  

Output

Once we execute the above command, we will get the below output:

We can use the below command to implement the previous command because the following command will help us save our time while we are typing the previous command again.

\g  

Output

As we can see in the below output, we will get the same result compared to the previous command:

Note: In psql, we can execute the previous command again, which is the SELECT command.

• Execute psql commands from a file

In case, if we want to implement psql commands from a file, we can use the following command:

\i filename  

• Get help on psql commands

We will use the following command to know all available psql commands.

\?  

Output

After executing the above command, we will get the below output window:

And, we can use the below statement to get help on specific PostgreSQL command.

\h  

Output

After executing the above command, we will get the below output window:

For example, if we want to know the thorough information on ALTER TABLE command, we will type the below command:

\h ALTER TABLE  

Output

Once we execute the above command, we will get the below result:

Turn on query execution time

We will use the following command to turn on query execution time.

\timing  

Output

Once we execute the above command, we will get the below result:

And if we want to see the count from the Station1 table, we will use the below command:

Select count(*) from station1;   

to turn it off, we can use the same command as above:

timing 

Output

As we can see in the below output that the timing is off.

• Edit command in editor

If we want to write the command in any editor, we will use the following command:

\e  

Output

After executing the above command, psql will open the text editor specified by our editor environment variable and where we can write any command as we can see in the below screenshot:

After entering the following command in the editor, first, we will save it and then close the editor.

Select * from Station1;   

Once we close the editor, then psql will execute the command and return the output as we can see in the below image:

• Quit psql

We will use the below command to quit the psql, and then press any key to exit psql.

\q  

Output

Once we execute the above command, we will get the below output: