Summary: in this tutorial, we give you a list of common psql commands that helps you query data from PostgreSQL database server faster and more effective.
Connect to PostgreSQL database
The following command connects to a database under a specific user. After pressing
Enter PostgreSQL will ask for the password of the user.
psql -d database -U user -W
For example, to connect to
dvdrental database under
postgres user, you use the following command:
C:\Program Files\PostgreSQL\9.5\bin>psql -d dvdrental -U postgres -W
Password for user postgres:
If you want to connect to a database that resides on another host, you add the -h option as follows:
psql -h host -d database -U user -W
In case you want to use SSL mode for the connection, just specify it in the command as the following command:
psql -U user -h host "dbname=db sslmode=require"
Switch connection to a new database
Once you are connected to a database, you can switch the connection to a new database under a user specified by
user. The previous connection will be closed. If you omit the
user parameter, the current
user is assumed.
\c dbname username
The following command connects to
dvdrental database under
postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".
List available databases
To list all databases in the current PostgreSQL database server, you use
List available tables
To list all tables in the current database, you use
Describe a table
To describe a table such as a column, type, modifiers of columns, etc., you use the following command:
List available schema
To list all schema of the currently connected database, you use the
List available functions
To list available functions in the current database, you use the
List available views
To list available views in the current database, you use the
List users and their roles
To list all users and their assign roles, you use
Execute the previous command
To retrieve the current version of PostgreSQL server, you use the
version() function as follows:
Now, you want to save time typing the previous command again, you can use
\g command to execute the previous command:
psql executes the previous command again, which is the SELECT statement,.
To display command history, you use the
If you want to save the command history to a file, you need to specify the file name followed the
\s command as follows:
Execute psql commands from a file
In case you want to execute psql commands from a file, you use
\i command as follows:
Get help on psql commands
To know all available psql commands, you use the
To get help on specific PostgreSQL statement, you use the
For example, if you want to know detailed information on ALTER TABLE statement, you use the following command:
\h ALTER TABLE
Turn on query execution time
To turn on query execution time, you use the
Timing is on.
dvdrental=# select count(*) from film;
Time: 1.495 ms
You use the same command
\timing to turn it off.
Timing is off.
Edit command in your own editor
It is very handy if you can type the command in your favorite editor. To do this in psql, you
\e command. After issuing the command, psql will open the text editor defined by your EDITOR environment variable and place the most recent command that you entered in psql into the editor.
After you type the command in the editor, save it, and close the editor, psql will execute the command and return the result.
It is more useful when you edit a function in the editor.
\ef [function name]
Switch output options
psql supports some types of output format and allows you to customize how the output is formatted on fly.
\acommand switches from aligned to non-aligned column output.
\Hcommand formats the output to HTML format.
To quit psql, you use
\q command and press e
nter to exit psql.
In this tutorial, we have shown you how to use psql commands to perform various commonly used tasks.