PostgreSQL Commands

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 same as 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 VERSION()’ command by 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 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 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 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 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 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 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 used to list 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 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 used to list 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 to have detailed information about any statement then 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 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 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 used to open 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 then we will 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 ‘\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.

This is a guide to PostgreSQL Commands. Here we discuss the introduction to PostgreSQL commands with respective list of commands and programming examples. You may also have a look at the following articles to learn more –

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