How To Customize the PostgreSQL Prompt with psqlrc on Ubuntu 14.04

Introduction

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:

1. The system-wide psqlrc file is found in PostgreSQL’s system configuration directory.

The location of this directory depends on your PostgreSQL installation but can be found by using the pg_config tool.

pg_config --sysconfdir

2. The user psqlrc file is found or can be created in the user’s home directory.

touch ~/.psqlrc

3. Version-specific psqlrc files can be created if there are multiple PostgreSQL installations. Just add the version number to the end.

touch ~/.psqlrc-9.1
touch ~/.psqlrc-9.3

Installation

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=#

Editing the prompt

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 '

• %M refers to the database server’s hostname – is “[local]” if the connection is over a Unix domain socket
• %> refers to the listening port
• %n refers to the session username
• %/ refers the current database
• %R refers to whether you’re in single-line mode (^) or disconnected (!) but is normally =
• %# refers to whether you’re a superuser (#) or a regular user (>)
• %x refers to the transaction status – usually blank unless in a transaction block (*)

If logged into a machine with hostname “trident” as user “john” and accessing the database “orange” as a regular user, you would see

[trident]:5432 john@orange=>

You can also edit the secondary psql prompt (PROMPT2).

postgres-#

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 %# '

• %R is represented by ‘-’ instead of ‘=’

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.

Colors

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:

• 32 for green
• 33 for yellow
• 34 for blue
• 35 for magenta
• 36 for cyan
• 37 for white

Display options

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.

\timing

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.

\x auto

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.

Wrap up

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.