PostgreSQL Commands

Updated May 19, 2023

Introduction to PostgreSQL Commands

The PostgreSQL supports various commands which we can execute from the psql prompt. The psql prompt is an interactive interface front-end to the PostgreSQL, the same as the terminal provided with its default installation. We can use the psql prompt for writing various commands and queries interactively and execute them to the PostgreSQL for having results. It provides various meta-commands. We can give input as command-line arguments or commands from a file. If the shell command finishes without any error, then the psql returns 0. If the shell command fails because of errors like file not found or out of memory, the psql returns 1. The psql returns 2 if the server connection fails.

List of PostgreSQL Commands with Examples

The PostgreSQL supports various commands which we can execute from the psql prompt.

First, we will open the psql prompt, which looks like the following snapshot.

Given below are the various meta-commands:

1. Command to list all commands available in PostgreSQL

The ‘\?’ command returns all of the commands available in PostgreSQL.

Illustrate the result of the ‘\?’ command by using the following snapshot.

Code:

\?

Output:

2. Command to get the version of the PostgreSQL

The ‘SELECT VERSION()’ statement returns the version of PostgreSQL.

Illustrate the result of the ‘SELECT’ command using the following snapshot.

Code:

SELECT VERSION();

Output:

3. Command to list all of the available databases

The ‘\l’ command returns all of the databases available in PostgreSQL.

Illustrate the result of the ‘\l’ command by using the following snapshot.

Code:

\l

Output:

4. Command to PostgreSQL database connect

This command is used to connect to the database.

Illustrate the result of the above command by using the following snapshot.

Code:

-d NewEduCBADB -U postgres –W

Output:

5. Command to switch database connection to the new database

The ‘\c db_name [username]’ command is used to switch the connection to the new database.

Illustrate the result of the above command by using the following snapshot.

Code:

\c NewEduCBADB

Output:

6. Command for listing all of the tables from the current database

The ‘\ dt’ command returns the tables from the current database.

Illustrate the result of the ‘\ dt’ command by using the following snapshot.

Code:

\dt

Output:

7. Command to describe the table

The ‘\d table_name’ command is used to describe the table.

Illustrate the result of the above command by using the following snapshot.

Code:

\d furniture

Output:

8. Command to list available schema

The ‘\ dn’ command is used to list all of the schemas of the current database.

Illustrate the result of the above command by using the following snapshot.

Code:

\dn

Output:

9. Command to list available functions

The ‘\ df’ command is used to list all of the functions of the current database.

Illustrate the result of the above command by using the following snapshot.

Code:

\df

Output:

10. Command to list available views

The ‘\ dv’ command is used to list available views in the current database.

Illustrate the result of the above command by using the following snapshot.

Code:

\dv

Output:

11. Command to list users and their roles

The ‘\ du’ command lists all the users with their roles.

Illustrate the result of the above command by using the following snapshot.

Code:

\du

Output:

12. Command to view complete history

1. The ‘\ s’ command is used to view the complete history in the PostgreSQL.
2. We can save the history in the file by using the ‘\s filename’ command.

13. Command to list all SQL commands

The ‘\ h’ command lists all SQL commands in the PostgreSQL.

Illustrate the result of the above command by using the following snapshot.

Code:

\h

Output:

14. Command to get help on psql commands

If we want detailed information about any statement, we can use this command.

Illustrate the result of the command by using the following snapshot.

Code:

\h DROP TABLE

Output:

15. Command to get the execution time of SQL query

The ‘\timing’ command is used to get the execution time of the SQL query in the PostgreSQL.

Illustrate the result of the above command by using the following snapshot.

Code:

\timing

Output:

16. Command to execute previous psql command

The ‘\g’ command is used to execute the previous command.

Illustrate the result of the above command by using the following snapshot.
Code:

\g

Output:

17. Command to use the psql with the text editor

The ‘\e’ command opens the recently executed SQL query in a text editor. We can edit that in the query in a text editor and can run it again.

Illustrate the result of the above command by using the following snapshot.

Now we will modify the SQL query in a text editor and close the text editor.

Illustrate the result of the step by using the following snapshot.

Step 1: Modify the SQL query.

Step 2: Close the text editor.

Code:

\e

Output:

18. Command to switch output format from aligned to the non-aligned column and vice-versa

The command’ \ a’ is used to switch the output format from aligned to the non-aligned column and vice-versa.

We will switch the output format by using the ‘\ a’ command.

Illustrate the result of the above command by using the following snapshot.

Code:

\a

Output:

In order to illustrate the effect of ‘\ a’ command for the output format change, we will execute the ‘select * from employee’ command and will have a look at the output.

Code:

select * from employee;

Output:

Now we will again switch the output format by using ‘\ a’ command.

Illustrate the result of the above command by using the following snapshot.

Code:

\a

Output:

In order to illustrate the effect of ‘\ a’ command for the output format change, we will execute the select * from employee’ command and will have a look at the output.

Code:

select * from employee;

Output:

19. Command to switch output format to HTML format

The command’ \H’ is used to format the output in the HTML format.

Illustrate the result of the above command by using the following snapshot.

Code:

\H

Output:

In order to illustrate the effect of ‘\ H’ command for the output format change, we will execute the ‘select * from employee’ command and will have a look at the output.

Code:

select * from employee;

Output:

20. Command to quit the psql in PostgreSQL

The ‘\q’ command is used to quit the psql in the PostgreSQL.

Illustrate the result of the above command by using the following snapshot.

Code:

\q

Output:

Conclusion

In this article, we saw how to use the PostgreSQL commands and how the PostgreSQL commands works. Also, we saw several examples of the PostgreSQL commands.

We hope that this EDUCBA information on “PostgreSQL Commands” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL CTE
2. Variables PostgreSQL
3. PostgreSQL FULL OUTER JOIN
4. PostgreSQL GRANT