EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL Commands

By Sohel SayyadSohel Sayyad

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL Commands

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 VERSION()’ command by 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 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 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 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 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 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 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 used to list 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 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:

PostgreSQL Commands 13

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:

to get help on psql

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:

get the execution time of the SQL query

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:

execute previous psql

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.

PostgreSQL Commands 17

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.

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 ‘\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

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

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

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

Special Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More