Summary: In this tutorial, we provide you with a list of common psql commands that help you query data from the PostgreSQL database server more quickly and efficiently.
1) Connect
to PostgreSQL database
The following command connects to a database under a specific user. After pressing Enter PostgreSQL it will ask you for the user’s password.
Code language: SQL (Structured Query Language) (sql)For example, to connect to the
dvdrental database in postgres user, use the following command
:Code language: SQL (Structured Query Language) (sql)
If you want to connect to a database that resides on another host, add the -h option as follows
:Code language: SQL (Structured Query Language) (sql)
In case you want to use SSL mode for connection, simply specify it as shown in the following command
:Code language: SQL (Structured Query Language) (sql)2) Change the connection to a new database Once you are connected to a database, you can change the
connection to a new database
under a user specified by the user. The previous connection will close. If you omit the user parameter, the current user is assumed.
The following command connects to the dvdrental database under the user postgres:
Code language: PHP (php)3) List of available databases To list all databases on the current PostgreSQL database server, use \l command:Code language: SQL (Structured Query Language) (sql)
4) List
of available tables
To list all
tables in the
current database, use \dt
command:Structured Query Language (SQL)
Note that this command displays the only table in the currently connected database.
5) Describe
a table To
describe a table as a column, type, column modifiers, and so on
, use the following command:Code language: SQL (Structured Query Language) (sql)
6) List
of available schemas
To list all schemas in the currently connected database, use the \dn command.
Code language: SQL (Structured Query Language) (sql)7) List available functions
To list the functions available in the current database, use the \df command.
Code language: SQL (Structured Query Language) (sql)
8) List
of available views
To list the views available in the current database, use the \dv command.
Code language: SQL (Structured Query Language) (sql)9) List users and their roles To
list all users and their assigned roles
, use \du command:Code language: SQL (Structured Query Language) (sql)
10) Run the above command
To retrieve the current version of the PostgreSQL server, use the version() function as follows
:Code language: SQL (Structured Query Language) (sql)
Now, you want to save time by typing
the above command again, you can use the \g command to run the previous command:Code language: SQL (Structured Query Language) (sql)
psql executes the above command again, which is the SELECT, statement
.
11) Command History
To display the command history, use the \s command.
Code language: SQL (Structured Query Language) (sql)If you want to save the command history to a file,
you must specify the file name followed by the \s command as follows
:Code language: SQL (Structured Query Language) (sql)12) Execute psql commands from a file In case you want to run
psql commands from a file
, Use
the \i command as follows:Code language: SQL (Structured Query Language) (sql)
13) Get help with
psql commands
To know all the available psql commands, use the \? command.
Code language: SQL (Structured Query Language) (sql)For
help with a specific PostgreSQL
statement, use the \h command.
For example, if you want detailed information about the ALTER TABLE statement, use the following command
: Code language: SQL (Structured Query Language) (sql)
14) Activate
the
query execution time To activate the query execution time, Use the \timing command.
Code language: SQL (Structured Query Language) (sql)
Use the same \timing command to disable it.
Code language: SQL (Structured Query Language) (sql)
15) Edit
command in your own editor
It is very useful if you can type the command in your favorite editor. To do this in psql, \e command. After issuing the command, psql will open the text editor defined by your EDITOR environment variable and place the most recent command you entered in psql in the editor.
src=”https://www.postgresqltutorial.com/wp-content/uploads/2015/07/psql-commands.jpg” alt=”psql commands” />After typing the command in the editor, saving it, and closing the editor, psql will execute the command and return the result.
This is most useful when editing a function in the editor.
Code language: SQL (Structured Query Language) (sql)
=”https://www.postgresqltutorial.com/wp-content/uploads/2015/07/psql-command-ef-edit-function.jpg” alt=”psql ef edit function command” />
16) Change output options
psql supports some types of output format and allows you to customize how output is formatted on the fly
. \
- A command switches from aligned to non-aligned column output.
- H formats the output in HTML format.
17) Quit
psql To exit psql
, use the \q command and press enter to exit psql.
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you learned how to use psql commands to perform various commonly used tasks in PostgreSQL.
