psql advanced settings

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

• Look and feel
• Behavior
• Shortcuts

You 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 user preferences. PROMPT1 and PROMPT2 are issued when you create a new command and a command that expects more input, respectively. The following example shows some of the prompt options; by default, when you connect 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: division by zero
(postgres@[local]:5432) [postgres]=#! > ROLLBACK;
ROLLBACK
(postgres@[local]:5432) [postgres]=# > SELECT
postgres-# 1;

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

• %M: The full hostname. 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 with =;; if the session is disconnected for a certain reason, it's substituted with (!).
• %#: Used to distinguish superusers from normal users. The hash sign (#) indicates that the user is a superuser. 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.

Did you notice how PROMPT2 was issued when the SELECT 1 SQL statement was written over two lines? Finally, pay attention to the * sign, which indicates a transaction block.

In the psql tool, you 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 you 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, you can configure the transaction's execution behavior. psql provides three variables, ON_ERROR_ROLLBACK, ON_ERROR_STOP, and AUTOCOMMIT:

• ON_ERROR_STOP: By default, psql continues executing commands even after it encounters 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 can't be ignored, and it's good to set this variable to ON. This variable is useful with the -f, \i, and \ir options, which are used to specify the file for the psql client, include a file from the psql client, and include the file from relative path in the psql client, respectively:

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

postgres=# \i /tmp/test_rollback.sql
psql:/tmp/test_rollback.sql:1: ERROR: division 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: division 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, the whole transaction is rolled back—this is the default behavior. When the variable value is ON, 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, you 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's 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.