Postgresql (psql) .psqlrc tips and tricks

Psql is a good tool for the PostgreSQL database management and use. Psql client functioning can easily be improved by adding a few lines ~/.psqlrc file.

Set all null fields to NULL:

\pset null 'NULL'

After this, the query results look like this:

select 'test' as test_text, null as test_null;
 test_text | test_null
-----------+-----------
 test      | NULL
(1 row)

Set the command history file names for each host and database:

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

After this, the history file naming look like this:

.psql_history-alpha-testdb
.psql_history-localhost-test
.psql_history-10.20.10.101-production
...

Set the number of commands to store in the command history:

\set HISTSIZE 2000

Set timing on and see how long query took:

\timing

psql prompt can be customized to your preference:

\set PROMPT1 '(%[email protected]%M:%>) [%/] > '
\set PROMPT2 ''

After this, the PROMPT1 look like this:

([email protected]:5432) [webdb] > 

And PROMPT2 is empty.
Complete information can be found here, under prompting.

Set client encoding:

\encoding unicode

Final .psqlrc file look like this:

\pset null 'NULL'
\set HISTFILE ~/.psql_history- :HOST - :DBNAME
\set HISTSIZE 2000
\timing
\set PROMPT1 '(%[email protected]%M:%>) [%/] > '
\set PROMPT2 ''
\encoding unicode

One additional tip is to add less to PAGER environment variable if you want to use less rather than more. So simply add following row to profile (/etc/profile or ~/.profile):

export PAGER=less