psqlrc - the psql config file

The configuration file for psql is psqlrc.

There are several options for the location of the system-wide psqlrc:

• somewhere in /etc, i.e., in /etc/postgres
• in the PostgreSQL installation directory or one of its subdirectories.
• in the location denoted by the env var PSQLRC or the env var PGSYSCONFDIR.
• the location returned when running pg_config --sysconfdir. Note that not all PostgreSQL installations include pg_config.

The location of the user-specific psqlrc is by default ~/.psqlrc.

On startup, psql tries to read the system-wide psqlrc, then the user-specific psqlrc, if the user-specific psqlrc exists. Statements in the user-specific psqlrc have priority over statements in the system-wide psqlrc.

a sample psqlrc file

This is the psqlrc I use:

special variables

All variables in psqlrc have sane default values. The PostgreSQL documentation lists some variable that “are treated specially by psql”. For some of these vars, the value can be set, i.e. for the vars:

• HISTCONTROL
• HISTFILE
• PROMPT1, PROMPT2, PROMPT3
• …

For some of these vars, the value can’t be set, or it doesn’t make sense to set the value of these vars:

• HOST
• DBNAME
• PORT
• USER
• …

If the value of the variable is to be used to set the value of another psqlrc var, the var must be prepended with the colon : , i.e.:

configuring the location of psql_history

The default location of this file is ~/.psql_history. Location and name of the psql history file can be configured either with

• the value of HISTFILE in psqlrc. (see example above).
• the env var PSQL_HISTORY.

configuring the psql prompt

psql has three prompts:

• PROMPT1 – normal prompt.
• PROMPT2 – used when more input is expected during command entry. Like when the obligatory semicolon at the end of a SQL command is omitted.
• PROMPT3 – used during an SQL COPY FROM STDIN command, when it is necessary to type in a row value on the terminal.

A colored prompt in psql helps a lot. To set a colored prompt with text color blue, put the following line into psqlrc:

For user bob, connected to database sampledb by a socket, the resulting prompt will look like this:

with the prompt in blue foreground color on the terminals default background color. The prompt display items are:

• %n session username
• %m database server’s hostname up to the first dot –- is local if the connection is over a Unix domain socket
• %> listening port
• %/ current database
• %# whether you’re a superuser (#) or a regular user (>)
• %x transaction status – usually blank unless in a transaction block (*)

configuring display expansion

psql can displaying the result of a psql command in two modes:

• normal mode – the layout is horizontal. I.e., for a row-set, the columns of the rows are laid out horizontally.
• expanded mode – the layout is vertical. I.e., for a row-set, the columns of the rows are laid out vertically.

The expanded mode is useful to display single a single row with lots of columns. I do not find expanded mode to be useful for a result with lots of rows or for the result of explain plan.

configuring the editor

psql picks up the editor to use from the env vars PSQL_EDITOR, EDITOR, and VISUAL. The env vars are evaluated in the order listed here. The first env var which is set is used for editing with the \e command.

The env var for the editor can be set in the psqlrc with \setenv, i.e. \setenv PSQL_EDITOR=nano.

configuring the display of null

By default, null values are not displayed in psql output. This means that in the output, the value NULL can not be distinguished from empty strings. The display of NULL can be changed with \set null, i.e.:

disabling the psql history file to prevent security scrawl

The psql history may contain sensitive information like a user’s password. To prevent the leaking of such sensitive information, the psql history file can be disabled by

• linking the psql history file to /dev/null: ln -s /dev/null <psql history file>.
• setting the env var PSQL_HISTORY to /dev/null: export PSQL_HISTORY=/dev/null.

I have rarely seen this in the wild.

reference