---
title: Improving the Command-Line Postgres Experience
teaser:
tags: web,postgresql
author: Gabe Berke-Williams
published_on: 2013-11-30
---

Understanding the [`~/.psqlrc`][psqlrc] configuration file, its options, and
reading others' `~/.psqlrc` files makes working with command-line Postgres more
enjoyable.

## Changing the prompt

By default, the prompt is a little blah:

    $ psql my_database
    my_database=#

Let's jazz it up.

There are [a lot of options][prompt] for changing the prompt. You can add
colors, domain names, the name of the current database, and more.

    \set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '

* The `%[..%]` sets the default prompt color to a bold black.
* `%M` is "The full host name (with domain name) of the database server, or
  [local] if the connection is over a Unix domain socket".
* `%n` is the database user name.
* `%/` is the database name.
* `%R` is "normally `=`, but `^` if in [single-line mode][single-line mode],
  and `!` if the session is disconnected from the database". It's nice to see
  when you're disconnected.
* The final `%[...%]` resets the color to non-bold black.
* `%#` is `#` if you're a superuser, otherwise `>`.

[prompt]: http://www.postgresql.org/docs/9.3/static/app-psql.html#APP-PSQL-PROMPTING
[single-line mode]: http://www.postgresql.org/docs/9.3/static/app-psql.html#R1-APP-PSQL-3

Here's how it looks on a local database:

![psql prompt](https://images.thoughtbot.com/psql_prompt.png)

## Changing the prompt, again

Being the smart cookie you are, you've probably inferred that a setting called
`PROMPT1` implies that there's a `PROMPT2`. And you're right! You go, Glen Coco.
`PROMPT2` for you:

    \set PROMPT2 '[more] %R > '

`PROMPT2` is used when `psql` is waiting for more input, like when you type
`SELECT * FROM` then hit enter - you haven't typed a semicolon, so `psql`
patiently displays `PROMPT2` and waits.

Here's the rundown:

* `[more]` is the literal string `[more]`.
* `%R` in `PROMPT2` has a different meaning than in `PROMPT1` - "in prompt 2 the
  sequence is replaced by `-`, `\*`, a single quote, a double quote, or a dollar
  sign, depending on whether `psql` expects more input because the command wasn't
  terminated yet, because you are inside a `/\* ... \*/` comment, or because you
  are inside a quoted or dollar-escaped string."

Here's a contrived example:

    [local] gabe@my_database=# SELECT
    [more] - > '
    [more] ' > name
    [more] ' > '
    [more] - > FROM users;

Nice.

## There's more to life than prompts

Now your prompt is spiffed up, but your `~/.psqlrc` can bring still more joy to
your life. Here are some options I set, with comments:

    -- By default, NULL displays as an empty space. Is it actually an empty
    -- string, or is it null? This makes that distinction visible.
    \pset null '[NULL]'
    -- Use table format (with headers across the top) by default, but switch to
    -- expanded table format when there's a lot of data, which makes it much
    -- easier to read.
    \x auto
    -- Verbose error reports.
    \set VERBOSITY verbose
    -- Use a separate history file per-database.
    \set HISTFILE ~/.psql_history- :DBNAME
    -- If a command is run more than once in a row, only store it once in the
    -- history.
    \set HISTCONTROL ignoredups
    -- Autocomplete keywords (like SELECT) in upper-case, even if you started
    -- typing them in lower case.
    \set COMP_KEYWORD_CASE upper

## What's next

If you found this useful, I recommend:

* The [official `~/.psqlrc` documentation][psqlrc]
* Craig Kerstiens' talk, [How I Work with Postgres][kerstiens]
* Harold Giménez's [`~/.psqlrc`][hgmnz]

[psqlrc]: http://www.postgresql.org/docs/9.3/static/app-psql.html
[kerstiens]: http://craigkerstiens.com/2013/02/13/How-I-Work-With-Postgres/
[hgmnz]: https://github.com/hgmnz/dotfiles/blob/master/psqlrc