EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Commands
 

PostgreSQL Commands

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 19, 2023

 

 

PostgreSQL Commands

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

PostgreSQL Commands 1

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:

PostgreSQL Commands 2

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:

PostgreSQL Commands 3

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:

list all of the available databases

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:

database connect

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:

switch database connection

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:

PostgreSQL Commands 7

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:

PostgreSQL Commands 8

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:

PostgreSQL Commands 9

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:

PostgreSQL Commands 10

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:

PostgreSQL Commands 11

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:

PostgreSQL Commands 12

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:

PostgreSQL Commands 13

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:

to get help on psql

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:

get the execution time of the SQL query

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:

execute previous psql

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.

PostgreSQL Commands 17

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.

PostgreSQL Commands 18

Step 2: Close the text editor.

Code:

\e

Output:

PostgreSQL Commands 19

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:

PostgreSQL Commands 20

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:

PostgreSQL Commands 21 JPG

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:

PostgreSQL Commands 22 JPG

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:

PostgreSQL Commands 23 JPG

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:

to format the output in the HTML format

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:

PostgreSQL Commands 25 JPG

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:

quit the psql

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

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW