psql advanced settings

The psql client can be personalized. The .psqlrc file is used to store the user preference for later use. There are several aspects of psql personalization, including the following:

• Look and feel
• Behavior
• Shortcuts

One can change the psql prompt to show the connection string information including the server name, database name, username, and port. The psql variables PROMPT1, PROMPT2, and PROMPT3 can be used to customize the user preference. PROMPT1 and PROMPT2 are issued when creating a new command and a command expecting more input respectively. The following example shows some of the prompt options; by default, when one connects to the database, only the name of the database is shown.

The \set meta command is used to assign a psql variable to a value. In this case, it assigns PROMPT1 to (%n@%M:%>) [%/]%R%#%x >. The percent sign (%) is used as a placeholder for substitution. The substitutions in the example will be as follows:

postgres=# \set PROMPT1 '(%n@%M:%>) [%/]%R%#%x > '
(postgres@[local]:5432) [postgres]=# > BEGIN;
BEGIN
(postgres@[local]:5432) [postgres]=#* > SELECT 1;
 ?column?
----------
        1
(1 row)

(postgres@[local]:5432) [postgres]=#* > SELECT 1/0;
ERROR: pision by zero
(postgres@[local]:5432) [postgres]=#! > ROLLBACK;
ROLLBACK
(postgres@[local]:5432) [postgres]=# > SELECT
postgres-# 1;

The following list of signs are used in the previous example; the meanings of the signs are as follows:

• %M: The full host name. In the example, [local] is displayed, because we use the Linux socket.
• %>: The PostgreSQL port number.
• %n: The database session username.
• %/: The current database name.
• %R: Normally substituted by =;; if the session is disconnected for a certain reason, then it is substituted with (!).
• %#: Used to distinguish super users from normal users. The (#) hash sign indicates that the user is a super user. For a normal user, the sign is (>).
• %x: The transaction status. The * sign is used to indicate the transaction block, and the (!) sign to indicate a failed transaction block.

Notice how PROMPT2 was issued when the SQL statement SELECT 1 was written over two lines. Finally, notice the * sign, which indicates a transaction block.

In the psql tool, one can create shortcuts that can be used for a common query such as showing the current database activities using variables assignment \set meta command. Again, the : symbol is used for substitution. The following example shows how one can add a shortcut for a query:

postgres=# \set activity 'SELECT pid, query, backend_type, state FROM pg_stat_activity';
postgres=# :activity;
  pid | query | backend_type | state
-------+---------------------------------------------------------------+---------------------+--------
  3814 | | background worker |
  3812 | | autovacuum launcher |
 22827 | SELECT pid, query, backend_type, state FROM pg_stat_activity; | client backend | active
  3810 | | background writer |
  3809 | | checkpointer |
  3811 | | WALwriter |
(6 rows)

In psql, one can configure the transaction execution behavior. psql provides three variables, which are ON_ERROR_ROLLBACK , ON_ERROR_STOP, and AUTOCOMMIT:

• ON_ERROR_STOP: By default, psql continues executing commands even after encountering an error. This is useful for some operations, such as dumping and restoring the whole database, where some errors can be ignored, such as missing extensions. However, in developing applications, such as deploying new application, errors cannot be ignored, and it is good to set this variable to ON. This variable is useful with the -f, \i, \ir options.

$ echo -e 'SELECT 1/0;\nSELECT 1;'>/tmp/test_rollback.sql
$ psql
psql (10.0)
Type "help" for help.

postgres=# \i /tmp/test_rollback.sql
psql:/tmp/test_rollback.sql:1: ERROR: pision by zero
 ?column?
----------
        1
(1 row)

postgres=# \set ON_ERROR_STOP true
postgres=# \i /tmp/test_rollback.sql
psql:/tmp/test_rollback.sql:1: ERROR: pision by zero

• ON_ERROR_ROLLBACK: When an error occurs in a transaction block, one of three actions is performed depending on the value of this variable. When the variable value is off, then the whole transaction is rolled back—this is the default behavior. When the variable value is on, then the error is ignored, and the transaction is continued. The interactive mode ignores the errors in the interactive sessions, but not when reading files.
• AUTOCOMMIT: This option causes SQL statements outside an explicit transaction block to be committed implicitly. To reduce human error, one can turn this option off.

Disabling the AUTOCOMMIT setting is quite useful because it allows the developer to rollback the unwanted changes. Note that when deploying or amending the database on life systems, it is recommended to make the changes within a transaction block and also prepare a rollback script.

Finally, the \timing meta command in psql shows the query execution time and is often used to quickly assess performance issues. The \pset meta command can also be used to control the output formatting.