Topdog.za.net

PostgreSQL allows you to customize your psql session's using a configuration file "psqlrc". using a psqlrc file you can create shortcuts, change your prompt etc.

The user specific psqlrc file is ~/.psqlrc

Customize your prompt

When you work with several databases on different hosts, you need to be able to differentiate which database you are connected to. A more descriptive prompt then comes in handy.

\set PROMPT1 '%n@%M:%>%x %/# '

That will set your psql prompt to

username@[hostname]:port database#

Set timing of queries.

Unlike mysql postgresql does not automatically display the time it took a query to execute, to enable that you add the following to you psqlrc file

\timing on

Create shortcuts

For queries that you run frequently you can create shortcuts.

\set listusers 'SELECT username, status FROM users'

You can now run the query using the shortcut

username@[hostname]:port database# :listusers

Setup command history

This will allow you to use the up arrow to access commands you previously typed.

\set HISTSIZE 20
\set HISTFILE ~/.psql_history-:HOST-:DBNAME

The above will remember the last 20 commands and will store them in a file ~/.psql_history-hostname-dbname

More reading

• http://www.postgresql.org/docs/current/interactive/app-psql.html
• http://raghavt.blogspot.com/2011/11/psqlrc-file-for-dbas.html