Monday, September 8, 2014

Customize psql prompting format - PostgreSQL

I introduce about how to display line number on psql command, and how to customize them here. Actually this function needs latest version of PostgreSQL (from 9.5).
Here is development version manual.

The manual says,
The line number inside the current statement, starting from 1.
We can set variable settings to psql output style, output format, prompt characters and so on. One of them is showing line number on psql.
You guys may used to it for now, it's really simple as default.
e.g., we can set prompt formatting and sample operation as follows.
postgres=# \set PROMPT1 '[%/][%>](%l)%R%# '
[postgres][55432](1)=# \set PROMPT2 '[%/][%>](%l)%R%# '
[postgres][55432](1)=# SELECT
[postgres][55432](2)=# aaaaaa -- error position
[postgres][55432](3)=# FROM
[postgres][55432](4)=# hoge; 
ERROR:  column "aaaaaa" does not exist
LINE 2: aaaaaa
How is this?
The prompt character means database name(%/), port number,(%>) line number (%l)from left. Please see describe them in manual.

If you feel waste to set prompting characters whenever execution of psql then psql read setting file (~/.psqlrc) before lunching itself. So if we write them to setting file then we don't need to set manually each time. Please try that. The sample .psqlrc file is following.
$ cat ~/.psqlrc
\pset border 2
\set PROMPT2 '[%/][%>](%l)%R%# '
\set PROMPT1 '[%/][%>](%l)%R%# '

No comments:

Post a Comment