By Scott Lee
The psqlrc file customizes the behavior of the psql interactive command line client. psql enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. The file comes in three flavors:
The location of this directory depends on your PostgreSQL installation but can be found by using the pg_config tool.
touch ~/.psqlrc-9.1 touch ~/.psqlrc-9.3
Before you can use psql, you must have PostgreSQL installed.
sudo apt-get install -y postgresql postgresql-contrib
This will install PostgreSQL 9.3. Now you can switch to the postgres user and start psql.
su - postgres psql
This should display the standard psql prompt.
psql (9.3.4) Type "help" for help. postgres=#
By editing the user psqlrc file you can customize the main psql prompt (PROMPT1) and create useful shortcuts. Edit the .psqlrc file with the editor of your choice to add the following lines (here we’ll use vim).
vi ~/.psqlrc \set PROMPT1 '%M:%> %n@%/%R%#%x '
If logged into a machine with hostname “trident” as user “john” and accessing the database “orange” as a regular user, you would see
You can also edit the secondary psql prompt (PROMPT2).
You’ll run into the secondary prompt when you have an unfinished query.
postgres=# select * from postgres-# peel limit 1;
Editing the secondary psql prompt is mostly similar to editing the primary psql prompt.
\set PROMPT2 '%M %n@%/%R %# '
When in the middle of a transaction on the machine with hostname “trident” as user “john” and accessing the database “orange” as a regular user, you would see
[trident]:5432 john@orange=> select * from [trident] john@orange-> peel limit 1;
Of course, you can add, remove, or rearrange these options to include information that is useful for you.
The prompt color can be edited with the psqlrc. To make the port number red add the following.
\set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x '
There are various colors you can use – change the value 31 to:
When querying a PostgreSQL database null values return a blank. If instead you want it to return the value NULL you can edit the null option.
\pset null '[null]'
To complete SQL keywords such as “SELECT” and “FROM” as either uppercase or lowercase, you can set the COMP_KEYWORD_CASE option with the options upper or lower.
\set COMP_KEYWORD_CASE upper
To have all queries display query times using enable the timing option.
As in the bash prompt, on the psql prompt you can press the up arrow key to access previously executed commands via the history. To set the size of the history you can edit HISTSIZE.
\set HISTSIZE 2000
When querying large tables sometimes the output renders text that is difficult to read. You can switch to expanded table format.
You can also set verbosity of error reports with options “default”, “verbose”, or “terse”.
\set VERBOSITY verbose
You can setup shortcuts with the set command as well. If you want to setup a shortcut for seeing the PostgreSQL version and available extensions add the following:
\set version 'SELECT version();' \set extensions 'select * from pg_available_extensions;'
If you want to display messages when starting the psql prompt you can use the echo command.
\echo 'Welcome to PostgreSQL\n'
Lastly, editing the psqlrc creates outputs when you startup psql. If you want to hide these set the QUIET flag at the top and bottom of the psql file.
The complete file is below.
\set QUIET 1 \set PROMPT1 '%M:%[%033[1;31m%]%>%[%033[0m%] %n@%/%R%#%x ' \set PROMPT2 '%M %n@%/%R %# ' \pset null '[null]' \set COMP_KEYWORD_CASE upper \timing \set HISTSIZE 2000 \x auto \set VERBOSITY verbose \set QUIET 0 \echo 'Welcome to PostgreSQL! \n' \echo 'Type :version to see the PostgreSQL version. \n' \echo 'Type :extensions to see the available extensions. \n' \echo 'Type \\q to exit. \n' \set version 'SELECT version();' \set extensions 'select * from pg_available_extensions;'
Now when you start psql you will see a different prompt.
$ psql Welcome to PostgreSQL! Type :version to see the version. Type :extensions to see the available extensions. Type \q to exit. psql (9.3.4) Type "help" for help. [local]:5432 postgres@postgres=#
There are many more customizations you can make, but these should be a good start to improving your psql experience.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Click below to sign up and get $200 of credit to try our products over 60 days!
Something I’ve noticed is that the introduction of ANSI color codes to the prompt changes cursor positioning and has undesirable affects when paging through past commands - the visible cursor is misaligned with its location in the string - any ideas?