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’ 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
- The ‘\s’ command used to view the complete history in the PostgreSQL.
- 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.
Recommended Articles
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 –
