Basic Tools
Using psql
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey Beresnev
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss ofincome, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is,” and Postgres Professional company has no obligations to
provide maintenance, support, updates, enhancements, or modifications.
2
Topics
Launching psql and Connecting to the Database
Getting Help
Working with psql
Configuring psql
3
Purpose of psql
Terminal client for working with PostgreSQL
Comes with the DBMS
Used by administrators and developers for interactive work and
script execution
There are other third-party tools available, but they are not considered in the
scope of the course.
The psql terminal client will be used throughout the course. Those who are
used to working with GUI tools may find it uncomfortable at first.
Nevertheless, it is very powerful if you get used to it.
This is the only client supplied with the DBMS. The knowledge of psql will be
useful to both developers and DB administrators, regardless of which tool
they choose to work with at the end of the day.
4
Connection
Launch
$ psql -d database -U user -h host -p port
New connection in psql
=> \c[onnect] database user node port
Information about the current connection
=> \conninfo
The required connection parameters include: database name, user name,
server name, port number. If these parameters are not specified, psql will try
to connect using the default values:
●
database — matches the user name;
●
user — matches the OS user name;
●
node — connection via Unix socket;
●
port — usually 5432.
To make a new connection without leaving psql, run the \connect
command. It uses the current connection's parameters as defaults.
The \conninfo command provides information about the current
connection.
Additional information about connection configuration options:
5
Getting Help
In the OS command line
$ psql --help
$ man psql
In psql
=> \? list of psql commands
=> \? variables psql variables
=> \h[elp] list of SQL commands
=> \h command syntax of the SQL command
=> \q quit
Reference information on psql can be obtained not only from the
documentation, but also from within the system directly.
psql with the --help option displays a startup help message. If the
documentation package is installed with the system, you can view the
manual for psql using the man psql command.
psql can execute SQL commands as well as its own commands. All psql
commands start with a backslash and, as a rule, can be abbreviated to their
first letter.
Inside psql, you can get a list and a brief description of all psql
commands: \?.
The \help command provides a list of SQL commands that the server
supports, as well as the syntax of an SQL command (if specified).
Another command that is useful to know, although it has nothing to do with
the help, is \q — exit psql. Alternatively, you can use the exit and quit
commands to quit.
7
Takeaways
psql is a terminal client for working with PostgreSQL
Connection parameters are required at startup
Executes SQL and psql commands
Includes tools for interactive work, as well as for preparing and
executing scripts
8
Practice
1. Run psql and check the current connection information.
2. Display a detailed list of databases.
3. By default, psql uses less command for page-by-page output.
Replace it with less -XS and display the detailed database list
again.
4. The default prompt shows only the name of the database.
Configure the prompt to display in the following format:
user@database=#.
5. Configure psql to display the execution time for all commands.
Make sure that this setting is saved when you restart.
1. When starting psql, if you omit the connection parameters, the default
values will apply.
2. Use the \l+ command.
3. The pager program is configured using the PSQL_PAGER environment
variable. The setting can be configured in the .psqlrc file using the \setenv
command. This will set the value to 'less -XS' specifically for psql
sessions, while maintaining the OS default settings in all other cases. By
default, less wraps long lines during viewing and clears its output upon exit.
The -XS parameter disables this default behavior.
4. Prompt customization is described in the documentation:
5.The psql command to output the duration of a query execution can be
found in the PostgreSQL documentation or within psql itself with the \?
command.
9
Practice+
1. Open a transaction and execute a command that ends with any
error. Make sure that no other commands can be executed inside
this transaction.
2. Set the ON_ERROR_ROLLBACK parameter to on and make
sure that after the error, you can continue executing commands
inside the transaction.
1. To open a transaction, run the command
BEGIN;
2. Setting the ON_ERROR_ROLLBACK parameter to ON causes psql to
create a SAVEPOINT before each SQL command inside an open
transaction and, in case of an error, roll back to this savepoint.
