May 18th, 2008 by depesz | Tags: , , | 4 comments »
Did it help? If yes - maybe you can help me?

I'm using .psqlrc file to set my preferable environment in psql. But I got lately annoyed by it confirming the changes every time I run psql:

Basically my .psqlrc looks like this:

\pset null '[null]'
\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x%# '
\set PROMPT2 ''
\set HISTFILE ~/.psql_history- :DBNAME
\timing
\pset pager always

It sets display of nulls from empty string to [null], which is (for me) more readable. It also changes prompt, so it's a bit more useful.

Additionally it makes psql store history of commands in separate files for each database I'm connecting to. It's a very cool feature, as I connect to many different databases, which share nothing between them, so there is no point to get mailsystem-db related queries in history when I'm connected to sales database.

Last two lines turn on timing of queries, and forces psql to always use pager (which is less, modified to be more helpful, thanks to Merlin Moncure blogpost).

The problem with this psqlrc, is that whenever I start psql, I get this:

=> psql
Null display is "[null]".
Timing is on.
Pager is always used.
psql (8.4devel)
Type "help" for help.

First three lines are from my .psqlrc, and they were quite annoying. It's nothing wrong when you see them once. Twice. But after 100-th times, you become to hate them.

Today I decided that I'll get rid of them, or at least I'll file a feature request to add some kind of “quiet" switch for psql.

Fortunately, before I sent my request, I checked manual for psql. And in there I found:

QUIET
This variable is equivalent to the command line option -q. It is probably not too useful in interactive mode.

Great. So I changed my psqlrc to:

\set QUIET 1
\pset null '[null]'
\set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x%# '
\set PROMPT2 ''
\set HISTFILE ~/.psql_history- :DBNAME
\timing
\pset pager always
\set QUIET 0

And tested it:

=> psql
psql (8.4devel)
Type "help" for help.

Yes. Finally.

I know it's not a new feature. Most probably you already knew about it, but I'm so happy with these 3 lines being gone, that I decided to write about it – maybe somebody else also has this issue.

  1. 4 comments

  2. # Joey
    May 18, 2008

    Thank you. I’m a beginner-intermedia PostgreSQL user and your blogs have always been helpful. even though certain things seem obvious, there is always something new that I learn.

    I’ve used .psqlrc but never thought og keeping histories in separate files for different db’s. The quiet mode definitely reduces the annoyance everytime!

    Thanks!

  3. # Peter Eisentraut
    May 19, 2008

    I also recently started using \set QUIET 1, but I leave it on for the entire session. It has worked out very well.

  4. # Morus Walter
    May 19, 2008

    hmm. setting quiet to 0 does not work for me (psql 8.2.5).
    \unset QUIET does a better job.
    I also don’t need an argument to set quiet (\set QUIET works).
    Leaving quiet on, implies no response on statements like alter user. I guess I prefer repsonses there.

    Great tip besides that.

  5. # TimRex
    May 10, 2013

    +1 to Morus. I found I had to \unset QUIET also – but this solved my quietly growing frustration 🙂

Sorry, comments for this post are disabled.