Magic Tricks for Postgres psql: Settings, Presets, Echo, and Saved Queries

As I’ve been working with Postgres psql cli, I’ve picked up a few good habits from my Crunchy Data co-workers that make my terminal database environment easier to work with. I wanted to share a couple of my favorite things I’ve found that make getting around Postgres better. If you’re just getting started with psql, or haven’t ventured too far out of the defaults, this is the post for you. I’ll walk you through some of the friendliest psql settings and how to create your own preset settings file.

Some of the most helpful psql commands

Formatting psql output

Postgres has an expanded display mode, which will read your query results as batches of column and data, instead of a huge wide list of columns that expand the display to the right.

A sample expanded display looks like this:

-[ RECORD 1 ]------------------------------
id         | 1
name       | Alice Johnson
position   | Manager
department | Sales
salary     | 75000.00
-[ RECORD 2 ]------------------------------
id         | 2
name       | Bob Smith
position   | Developer
department | Engineering
salary     | 65000.00

--Automatically format expanded display for wide columns
\x auto

I have a tutorial up about using basic psql if you’re just getting started and want to try these commands out.

Table column borders in psql output

If you’re not using the extended display, you can have psql do some fancy column outlines with the \pset linestyle.

--Outline table borders and separators using Unicode characters
\pset linestyle unicode

That will get you query output that looks like this:

┌────┬───────┬─────┐
│ id │ name  │ age │
├────┼───────┼─────┤
│  1 │ Alice │  30 │
│  2 │ Bob   │  25 │
└────┴───────┴─────┘

Show query run times in psql

This will give you a result in milliseconds for the time the query took to run at the bottom:

-- Always show query time
\timing

Create a preset for your null values in psql

This will work with emojis or really anything utf-8 compatible:

-- Set Null char output to differentiate it from empty string
\pset null '☘️'

Your psql history

You can create a history file for your psql command sessions like this:

-- Creates a history file for each database in your config directory CHECK IF THIS IS RIGHT
\set HISTFILE ~/.config/psql/psql_history-:DBNAME

-- Number of commands to save in history
\set HISTSIZE 2000

Echo PSQL commands as SQL

Any psql slash command (such as \d) runs against Postgres’ system tables. You can use the psql echo command to display the queries used for a given command, which can give you insight about Postgres’ internal tables, catalog, and other naming conventions.

-- output any SQL run by psql slash commands
\set ECHO_HIDDEN on

-- short name of ECHO_HIDDEN on
-E

Now let’s have the echo show us something. Do a table lookup with:

\dt+

Now you’ll see that it echos back to you the query it used to get this data, plus at the bottom, the normal results of \dt+.

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  CASE c.relpersistence WHEN 'p' THEN 'permanent' WHEN 't' THEN 'temporary' WHEN 'u' THEN 'unlogged' END as "Persistence",
  am.amname as "Access method",
  pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

**************************

                                    List of relations
 Schema |  Name   | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+---------+-------+----------+-------------+---------------+--------+-------------
 public | weather | table | postgres | permanent   | heap          | 856 kB |
(1 row)

Echo all Postgres psql queries

You can also have psql echo all queries that it runs:

-- Have psql echo back queries
\set ECHO queries

-- Short name of echo queries
-e

This can be useful if you’re running queries from a file, or they are presets in your psqlrc, and want the query output a 2nd time for record keeping.

I have a web based tutorial for ECHO HIDDEN and ECHO queries if you want to dig into either of these more.

Set up your default psql experience with .psqlrc

All of the above things I’ve listed you can set up to happen automatically every time you use your local psql. When psql starts, it looks for a .psqlrc file and if one exists, it will execute the commands within it. This allows you to customize prompts and other psql settings.

You can see if you have a .psqlrc file yet with:

ls -l ~/.psqlrc

If you want to try adding one:

touch ~/.psqlrc

Or edit your current file with:

open -e ~/.psqlrc

If you want to skip the logging of commands when you start psql, you can add these to the beginning and end of your file:

-- Don't log these commands at the beginning of the file
\set QUIET 1

-- Reset command logging at the end of the file
\set QUIET 0

Customizing your prompt line

The default prompt for psql shows your database name and not much else. In your psqlrc file, you can change the psql prompt line to use a different combination of information about the database host and session. I personally like using the date and time in here, since I’m saving sessions to refer back to later.

-- Create a prompt with host, database name, date, and time
\set PROMPT1 '%m@%/ %`date "+%Y-%m-%d %H:%M:%S"` '

For me, this looks like:

[local]@crunchy-dev-db 2024-07-19 15:06:37

Saved queries in your psqlrc file

This .psqlrc file is looking pretty cool, right? But wait … there’s more! You can add queries to this file so that you can just run them with a super simple psql input.

Add these sample queries to psqlrc for long running queries, cache hit ratio, unused_indexes, and table sizes.

\set long_running 'SELECT pid, now() - pg_stat_activity.xact_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > interval ''5 minutes'' ORDER by 2 DESC;'

\set cache_hit 'SELECT ''index hit rate'' AS name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT ''table hit rate'' AS name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables;'

\set unused_indexes 'SELECT schemaname || ''.'' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC;'

\set table_sizes 'SELECT c.relname AS name, pg_size_pretty(pg_table_size(c.oid)) AS size FROM pg_class c LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE n.nspname NOT IN (''pg_catalog'', ''information_schema'') AND n.nspname !~ ''^pg_toast'' AND c.relkind=''r'' ORDER BY pg_table_size(c.oid) DESC;'`,

Then to execute inside psql use a colon and the name of the query to run it, for example :long_running . If you’re using our managed Postgres, Crunchy Bridge, we built in a bunch of this for you with our CLI insights.

Experiment with your psql environment

I hope some of these things give you a few ideas about experimenting with your psql environments. It's pretty easy and fun! My tips for success:

• Prioritize helping yourself with things you use every day that take time. Is there a query you run once a week on your database? Put that in your psqlrc file so its right there next time.
• Don’t go crazy if you remote connect into databases. If you don’t use a local connection to the database and remote in directly, don’t create a lot of special tools because using a different environment might be painful.
• Check out our tutorials for basic psql and ECHO HIDDEN and ECHO queries to experiment with these in a web browser

We have a ton of other handy psql tricks in our Postgres tips page.

Thanks so much to Craig Kerstiens, David Christensen, Greg Mullane, and Reid Thompson for sharing all their psqlrc file samples and ideas with me!