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:
psql prompt can be customized to your preference:
\set PROMPT1 '(%n@%M:%>) [%/] > ' \set PROMPT2 ''
After this, the PROMPT1 look like this:
(wwwuser@alpha:5432) [webdb] >
And PROMPT2 is empty.
Complete information can be found here, under prompting.
Set client encoding:
Final .psqlrc file look like this:
\pset null 'NULL' \set HISTFILE ~/.psql_history- :HOST - :DBNAME \set HISTSIZE 2000 \timing \set PROMPT1 '(%n@%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):