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 HISTSIZE 2000

Set timing on and see how long query took:

\timing

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] > 

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 '(%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):

export PAGER=less

Related posts:

1. Howto Install PostgreSQL 8.4 Database Server on CentOS, Fedora, Red Hat This is quick guide howto install PostgreSQL 8.4 (current stable 8.4.4) database server on CentOS, Fedora and Red Hat. Fedora...
2. Create PostgreSQL Table Partitioning (Part 1) PostgreSQL supports partitioning via table inheritance. So the partitioning is made in such a way that every child table inherits...
3. PHP Script to Generate PostgreSQL Table Partitioning (Part 2) As Part 1 (Howto create PostgreSQL table partitioning) shows, making of PostgreSQL partitioning needs a lot of SQL commands. So...
4. PostgreSQL: Partitioned Table vs Non Partitioned Table (Part 3) This article compares the speed and performance of queries between partitioned and non partitioned PostgreSQL tables. However, it is important...
5. PostgreSQL Sleep Function pg_sleep – Postgres Delay Execution One day, I was looking for PostgreSQL database function, which could delay the execution of queries. At first I thought...