psql Tips and Tricks

These are some aesthetic and functional things I’ve found useful. There’s still a ton of stuff not included in this article, which you can find in the great psql docs.

Note: These are untested on psql version < 12.1. You can see what version of psql you’re using with (when in the psql prompt) \echo :VERSION or SELECT version();

Save Your Config

Save your favorite configuration options in ~/.psqlrc to configure psql upon startup. Here is a link to an example .psqlrc.

Pro tip: Make your first and last commands \set QUIET 1 and \set QUIET 0 to prevent the config from being displayed upon starting psql.

Visual

Prompt

Prompt 1 and 2

PROMPT1: This is the prompt that you see when first writing a query. PROMPT2: This is the prompt that you see in a continuing line of a query.

For example, if PROMPT1 is set to ==> , and PROMPT2 is set to => , a simple multi-line query would look like:

==> SELECT *
=> FROM table
=> WHERE id = 1;

However, if you are commonly writing multi-line queries, I would recommend using \e with a configured editor, as show in a following section of this post.

psql uses the readline lib, so any familiar readline shortcuts you know are usable in psql.

Configuring your prompt

\set PROMPT1 '-> ': Set the layout for the default prompt. Result of this looks like:

-> SELECT * FROM iloveprompts;

Here is a list of options for configuring your prompt.

The default prompt uses %/%R%# . Broken down:

%/: The name of the current database.
%R: In prompt 1 normally =, but ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails). Differs for prompts 2 and 3.
%#: If the session user is a database superuser, then a #, otherwise a >.

Example:
promptsllc-heroku::DATABASE=>
Colors and Fonts

Can you find the colors in the line below?

%[%033[1;33;40m%]%#%[%033[0m%]

Breaking this down:

%[
  %033[1;33;40m
    %]
      %#
    %[
  %033[0m
%]

%[ ... %]: Start of invisible characters. We use this to configure the prompt with "non-printing control" characters"
%033[1;33;40m ... %033[0m: Font formatting and colors!
  (1;): Boldface
  (33;40): Yellow (foreground) on black (background)
%] ... %[: End of invisible chars.
%#: If the session user is a database superuser, then a #, otherwise a >.

Here is a list of colors for ANSI/VT100 formatting and color codes.

Query Results

Expanded view

\x: Expanded view - useful for when you have a row with lots of columns, but not a widescreen monitor. Or, when you want to select, copy, and paste results.

─[ RECORD 1 ]───
last_value │ 269
log_cnt    │ 31
is_called  │ t

To reset to table view from expanded view, use \x or \x off

 last_value │ log_cnt │ is_called
────────────┼─────────┼───────────
        269 │      31 │ t
(1 row)

You can also set \x auto, which will show output in the expanded view when the output exceeds the width of the terminal.

HTML Output

\H: Set output to HTML.

<table border="1">
  <tr>
    <th align="center">last_value</th>
    <th align="center">log_cnt</th>
    <th align="center">is_called</th>
  </tr>
  <tr valign="top">
    <td align="right">269</td>
    <td align="right">31</td>
    <td align="left">t</td>
  </tr>
</table>
<p>(1 row)<br />
</p>

Line style (solid Unicode lines vs ASCII dashes)

\pset linestyle unicode: Set the line style to solid unicode lines like ── and (“Box Drawing” unicode characters).

Nice if you are sharing results and can guarantee you’re on a system that supports unicode.

Set the NULL value to a Character

\pset null ␀: Swap out with a character of your choosing. Useful for distinguishing between empty and null. Here are some fun unicode characters.

Editing Queries

Open your $EDITOR

\e: Open whatever editor you have set $EDITOR to edit the previous SQL query. Crucial for writing long queries.

Note: This depends on the env var EDITOR value. If you want to change the value of this while in psql, you must use \setenv, not \set, e.g. \setenv EDITOR emacs

psql will open a temporary file containing the query and upon exiting, will run what was saved to the temporary file.

For example, if you use VSCode, you can set the EDITOR var to code in your shell env (which psql inherits): Run export EDITOR=code before starting psql to open VSCode upon calling \e.

Case Study: emacs

I use emacs. However, I don’t typically connect to a new emacs process. I start an emacs server daemon upon starting my shell, and connect with an emacs client, so that I can share buffers across my tmux sessions, in psql, etc.

In case you’re curious how I do this, here is the line I run upon starting my shell to connect to the emacs server daemon:

export EDITOR="emacsclient -a '' -ct"

Saving Results

\o ~/myqueryresults.sql.txt: Set output to a file location. Don’t forget to turn it off after you’re done with \o!

Saving Queries

\w ~/filesname.sql: Write the previously run query to a file.

As mentioned below, you can also alias queries using \set 'SELECT * ...'

Optimizing Queries

\timing: Show amount of time in milliseconds queries take to execute.

-> select * from table;
 ...

Time: 88.914 ms

Aliases (Variables)

Just like bash, zsh and most other modern shells, you can set aliases, which are really variables, in psql using \set:

For single line aliases, you can use: \set SELECT * FROM table;

For multiline aliases, you need to use \gset to story the output in a variable, as such:

SELECT 'SELECT
	schemaname AS table_schema,
	relname AS table_name,
	pg_size_pretty(pg_total_relation_size(relid))
		AS total_size,
	pg_size_pretty(pg_relation_size(relid)) AS data_size,
	pg_size_pretty(
		pg_total_relation_size(relid)
		- pg_relation_size(relid)
	)
		AS external_size
FROM
	pg_catalog.pg_statio_user_tables
ORDER BY
	pg_total_relation_size(relid) DESC,
	pg_relation_size(relid) DESC
LIMIT
	10;' biggest_tables \gset

Note: If you have a better way to do this, please let me know at hi@urfav.dev!

This stores the command in a variable

To run the alias, invoke the variable:

-> :biggest_tables <return>

To see the contents of a variable, use: \echo :biggest_tables

Inline Learning

PostgresSQL Syntax

Not sure of the syntax for something? Don’t want to visit the (awesome) postgres manual in the browser for quick reference? No problem, psql has a solution: inline documentation!

\h [create domain]: Get docs on [anything], e.g.

-> \h create domain
Command:     CREATE DOMAIN
Description: define a new domain
Syntax:
CREATE DOMAIN name [ AS ] data_type
    [ COLLATE collation ]
    [ DEFAULT expression ]
    [ constraint [ ... ] ]

where constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

URL: https://www.postgresql.org/docs/12/sql-createdomain.html

Functions

\ef my_function: Get the source code for built-in functions using in $EDITOR. e.g.

\ef big5_to_utf8
...
<opens in emacs>
...
CREATE OR REPLACE FUNCTION pg_catalog.big5_to_utf8(integer, integer, cstring, internal, integer)
 RETURNS void
 LANGUAGE c
 PARALLEL SAFE STRICT
AS '$libdir/utf8_and_big5', $function$big5_to_utf8$function$
Note to self: future improvements for this post
  • Embed asciinemas for visual tips and tricks.
  • Use collapsible code snippets.