Basic tools
Using psql
13
Copyright
© Postgres Professional, 2015–2022
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Alexander Meleshko
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:
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, 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
Configuration
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 node -p port
New connection in psql
=>\c[onnect] database user node port
Information about the current connection
=> \conninfo
When starting psql, you need to specify the connection parameters.
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 — local connection,
●
port — usually 5432.
To make a new connection without leaving psql, run the \connect
command.
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 key 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.
Inside psql, you can get a list and a brief description of all psql commands.
All psql commands start with a backslash.
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, used to exit psql. Alternatively, you can also 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. Output all rows of the pg_tables table.
3. Set the parameter less -XS to display output page by page,
then display pg_tables contents again.
4. The default prompt shows the name of the database. Configure
the prompt to display additional information about the user:
role@base=#
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.
3. You can set the PSQL_PAGER environment variable in the .psqlrc file.
Use the \setenv command to set it. This will make the less -XS
parameter apply to only psql output. For all other OS commands, the OS
settings will be used (for example, from the .profile file).
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.
