Postgresql (psql) .psqlrc tips and tricks
Table of Contents
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.
1. Tuning psqlrc⌗
1.1 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)
1.2 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.
1.3 Set client encoding:⌗
\encoding unicode
2. Final .psqlrc file⌗
\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