psql

created on

January 8, 2022

Configuring psql

psql is configured with the configuration file psqlrc.

location of psqlrc

The location of psqlrc can be defined by the environmant variable PSQLRC

In Debian, the environment variable PSQLRC is defined in the file /etc/profile.d/postgres.sh, containing the following line:

If you can’t find psqlrc, this might do the trick:

configuring the psql prompt

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

the resulting prompt will look like this

with the prompt in blue foreground color on the terminals default background color.

prompt display items

• %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 (*)

ANSI escape codes for prompt colors

foreground color	ANSI escape code
black	%[%033[1;30m%]
red	%[%033[1;31m%]
green	%[%033[0;32m%]
yellow	%[%033[1;33m%]
blue	%[%033[1;34m%]
magenta	%[%033[1;35m%]
cyan	%[%033[1;36m%]
white	%[%033[0;37m%]

background color	ANSI escape code
black	%[%033[1;40m%]
red	%[%033[1;41m%]
green	%[%033[0;42m%]
yellow	%[%033[1;43m%]
blue	%[%033[1;44m%]
magenta	%[%033[1;45m%]
cyan	%[%033[1;46m%]
white	%[%033[0;47m%]

reset	ANSI escape code
reset	%[%033[0m%]

The ANSI escape code ‘reset’ is needed at the end of the prompt config line to set all attributes set by the ANSI escape codes (in this case, the colors) to ‘off’.

configuring the display of null

By default, null values are not displayed in psql output. This behaviour can be changed with

navigation in psql

changing the database

To change to another database within psql, use