January 14, 2013

PostgreSQL | psql and the Joy of RTFM

I need to confess something - I haven't been using PostgreSQL much over the past couple of years. Well, actually I've been using it indirectly pretty much every day, but it's been chugging along quietly in the background, storing and retrieving and otherwise manipulating data in a boringly reliable way while I've been working on Other Stuff. Every now and again I've need to interact with psql but it didn't occur to me for a long time that the output of \? has been expanded quite a bit since I last took a long careful look. In fact I might not have looked at all, but since upgrading to 9.2 I noticed that tab completion has not been converting SQL keywords to upper case like it used to, which annoys me immensely for reasons of personal aesthetics and I would like it to stop.

That's reason enough to take a long-ovedue look at the psql documentation, and discover not only the solution to that problem but also a couple of other useful new features I've somehow missed. (Apologies if this is old news).

Tab completion of keywords in UPPER CASE

It turns out it's simple enough to restore the old UPPER CASE tab completion behaviour, which is controlled by the new COMP_KEYWORD_CASE variable:


If you're more of a lower case person (and it's all matter of personal taste), "lower" ensures the opposite effect, of course.

Add the COMP_KEYWORD_CASE setting to .psqlrc to make it permanent.

According to the manual, two further options, preserve-lower or preserve-upper (the default setting) will cause the completed keyword to be in the case they were started as, but further automatic completions will be in the specified case.

However I'm at a bit of a loss to understand how they should work; for instance preserve-upper does the following:

test# vacuum an^I
test# vacuum ANALYZE


test# create ta^I
test# create table

Version-specific .psqlrc files

COMP_KEYWORD_CASE was introduced with Pg 9.2, while it won't cause any lasting damage to older versions of psql, it would be neat if it is set only in psql versions which support it. Enter version-specific .psqlrc files, e.g. .psqlrc-9.2, which will only be read by Pg 9.2 versions. The version number can specify a major or minor PostgreSQL release number (e.g. .psqlrc-9.2.6), however if a release-specific version exists only that file will be read by psql.

\ir - insert relative

This is not some new nepotistic psql feature, but an extension of \i which inserts the specified script relative to the location of the executing script, not psql's current working directory. This comes in handy when combining version-specific .psqlrc files with a generic, global .psqlrc file:

\ir .psqlrc
\set QUIET yes
\unset QUIET


\x auto

One thing I've always missed in psql is a simple way of specifying statement output in expanded mode (analogue to the mysql client's \G command) - useful when a result set will be wider than the console screen. \x\g\x does this but is not exactly easy to type. psql now supports an "auto" setting for \x, which automatically turns expanded mode on if output is wider than the screen, which covers most of my use cases. Another candidate for the version-specific .pgsqlrc file:

\ir .psqlrc
\set QUIET yes
\x auto
\unset QUIET

This feature is new in psql 9.2.

Variable interpolation

Occasionally I need to update a column with a long text value which is otherwise tricky to shoehorn into a manual query. It's possible to populate a psql variable with the contents of a file, then interpolate this value into an SQL statement.

ian@local$ echo "hello world" > /tmp/hello.txt
ian@local$ psql test
psql (9.2.2)
Type "help" for help.

test=# CREATE TABLE interpolation (value TEXT);
test=# \set content `cat /tmp/hello.txt`
test=# INSERT INTO interpolation VALUES (:'content');
test=# SELECT * FROM interpolation ;
 hello world
(1 row)

See the psql documentation for further details.

\df - list functions

\df used to list all functions by default, including those in the pg_catalog schema - which made for many dozens of lines of usually superfluous output. Since 8.4 \df no longer lists system functions unless specified using the S modifier or an an explicit schema search path.

However it's taken me a while to shake the habit of doing \df public.* (or whatever is the appropriate schema name).

\ef - edit function source

I don't know how I missed this (it's been around since 8.4) but it's immensely useful.

Posted at 3:34 AM

if you use stored procedures, then interesting statements can be \sf+ function - show source code and \df+ function - with additional info. New functionality is \dt+ and \l+ when size of database object will be calculated.
Posted by: Pavel Stehule | 2013-01-14 07:10