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
\set COMP_KEYWORD_CASE upper
If you're more of a lower case person (and it's all matter of personal taste), "
lower" ensures the opposite effect, of course.
COMP_KEYWORD_CASE setting to
.psqlrc to make it permanent.
According to the manual, two further options,
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
\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 \set COMP_KEYWORD_CASE upper \unset QUIET
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 \set COMP_KEYWORD_CASE upper \unset QUIET
This feature is new in
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); CREATE TABLE test=# \set content `cat /tmp/hello.txt` test=# INSERT INTO interpolation VALUES (:'content'); INSERT 0 1 test=# SELECT * FROM interpolation ; value ------------- 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.