Getting Fast With psql and Habits That Save Hours a Week

Most people use psql the way they use a calculator's basic keys: they type a query, read the table, scroll off the screen and retype. It is a better tool than that. It has a complete set of commands for looking at a database, a scripting language of its own, formatting options that make wide results readable and shortcuts for nearly every repetitive action. The difference between a beginner and someone fast is not memorizing syntax; it is a few habits and a good configuration file. This guide collects them in the order they pay off: the meta commands, a psqlrc, display settings, scripts, variables, timing, the editor, output and passwords. The spokes linked throughout go into each, and connecting to PostgreSQL from the command line with psql covers getting connected in the first place.

Why bother with psql

A graphical client is good for browsing and drawing diagrams. psql is better for almost everything else, for four reasons.

It is always there. It ships with the server, works over SSH on a machine with nothing else installed and behaves the same on a laptop and a production host. A skill that works everywhere is worth building.

It is scriptable. Anything you can do interactively can go in a file and run unattended: migrations, reports, maintenance, tests. The same commands you typed yesterday become tomorrow's cron job.

It is fast. No window to find, no connection dialog, no waiting for a UI to load a schema tree. A keystroke to a result.

It is honest. psql shows the server's actual messages, the real timing and the true text of what you ran, which a GUI sometimes smooths over. For debugging, that matters.

The cost is a learning curve of an afternoon. After that the speed difference is large, particularly for work that repeats.

Meta commands: the vocabulary

Lines beginning with a backslash are commands to psql itself, not SQL. They need no semicolon and end at the newline. A dozen cover most of the day:

\? lists all meta commands and \h CREATE TABLE shows the syntax help for any SQL command, which is built in and offline. Tab completion works on table names, columns, keywords and meta commands when psql is built with readline support, which it nearly always is: type SELECT * FROM or and press Tab. Learn the \d family first, because looking at structure is what you do most (the psql meta commands worth memorizing for daily work goes through them with examples).

Patterns work in these commands: \dt public.ord* lists tables beginning with "ord" in the public schema. \d+ adds a column of sizes and descriptions, and \dt+ shows the size of every table at a glance, which answers "what is using space?" in one line.

A psqlrc: start every session the way you work

psql reads ~/.psqlrc at start (and psqlrc files in the system directory). Put the settings you would otherwise type each time in there.

\set QUIET 1
\pset null '(null)'
\pset linestyle unicode
\pset border 2
\x auto
\timing on
\set HISTFILE ~/.psql_history- :DBNAME
\set HISTSIZE 10000
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\set ON_ERROR_ROLLBACK interactive
\set PROMPT1 '%[%033[1m%]%n@%m/%/%R%x%#%[%033[0m%] '
\set QUIET 0

Wrapping in QUIET suppresses the chatter of each setting being applied. \pset null makes NULL visible instead of an empty cell, which prevents a class of misreadings. \x auto switches between table and expanded layout depending on whether the result fits the terminal width. \timing on prints the execution time after each statement. A separate history file per database (:DBNAME) keeps queries for different projects apart. ON_ERROR_ROLLBACK interactive saves you from the aborted transaction state after a typo inside a BEGIN block. The prompt shows the user, host and database, and a transaction indicator.

Put -X on scripts to skip this file so their output does not depend on personal preferences. Setting up a psqlrc that makes daily work easier builds the file step by step and explains each line.

Display: wide rows, long values, readable tables

The most common frustration with psql is a result that wraps into an unreadable mess. Three tools fix it.

Expanded output shows each row as a vertical list of column names and values. \x toggles it, \x auto picks per result, and \gx runs the current query in expanded mode once. For a table with 30 columns, expanded is the only way to read a single row.

A pager shows long output one screen at a time. psql uses the PAGER environment variable or less. Setting PAGER='less -S' stops line wrapping and lets you scroll sideways, which suits wide tables. Alternatives such as pspg add frozen header rows and column navigation. Turn the pager off with \pset pager off for short work, or set a threshold with \pset pager 1 (page only when output exceeds a screen).

Formatting options with \pset: border (0, 1 or 2), linestyle (ascii or unicode), format (aligned, wrapped, unaligned, csv, html), null, numericlocale, title, footer and tuples_only. \pset format wrapped wraps long text values within their column, keeping the table intact.

Expanded display and pager settings in psql for wide rows and pretty printing query results in psql for readable output go through the combinations.

Scripts: psql as an automation tool

Run a file with -f, a single command with -c, or feed commands on standard input.

psql -X -q -v ON_ERROR_STOP=1 -h localhost -U shop -d shop -f migrate.sql

Each flag has a reason. -X ignores your psqlrc. -q suppresses informational output. -v ON_ERROR_STOP=1 stops at the first error instead of continuing with the rest of the file, which is essential for anything that changes data. -1 (--single-transaction) wraps the whole file in one transaction, so an error leaves the database as it was. Together these make a script safe to run unattended.

Inside a file, \i other.sql includes another file and \ir includes relative to the current file's directory, which makes a main script that pulls in several pieces. \echo and \qecho print progress messages. \o file redirects output. \set defines variables.

Exit codes matter in automation: psql returns 0 on success, 1 on a fatal error of its own, 2 on a connection failure and 3 for an error in a script run with ON_ERROR_STOP. Check them in shell scripts. Running SQL files and scripts from psql in automation covers transactions, error handling and idempotent scripts.

Variables and conditionals

psql has its own variables, separate from SQL. Set one with \set name value or -v name=value and substitute it with a colon:

\set min_total 100
SELECT * FROM orders WHERE total > :min_total;

Three forms control quoting, and using the right one prevents injection and syntax errors. :name substitutes the raw text. :'name' substitutes it as a quoted SQL literal. :"name" substitutes it as a quoted identifier. For values that come from outside, always use :'name':

psql -v customer="O'Brien" -c "SELECT * FROM customers WHERE name = :'customer';"

Variables hold the results of queries with \gset: SELECT count(*) AS n FROM orders \gset sets :n. Conditionals with \if, \elif, \else and \endif branch on a variable, so one script can behave differently in development and production, or skip a step that has already run. The special variables ON_ERROR_STOP, AUTOCOMMIT, ECHO, VERBOSITY, HISTSIZE and the prompt variables configure psql's behavior. Using psql variables and conditionals in scripts safely shows patterns and the traps, such as variables not being substituted inside dollar-quoted strings.

Measuring: timing, watch and explain

\timing prints how long each statement took, including the client round trip. It is the first measurement of any slow query and costs nothing. For the server's own view, prefix the query with EXPLAIN (ANALYZE, BUFFERS), which runs it and shows the plan with actual times and rows. psql adds \watch, which re-runs the last query every few seconds:

SELECT count(*) FROM jobs WHERE status = 'pending';
\watch 5

That turns the prompt into a live monitor: watch a queue drain, a table grow or a lock clear. In recent versions \watch accepts options for the interval and a maximum count. Press Ctrl+C to stop. Combined with \x, you can watch a single row of statistics change. Timing queries and watching output in psql while tuning goes through reading the numbers honestly (the first run is often slower because of a cold cache, and a single measurement is noise).

Editing: let a real editor write the query

Long queries are painful to write at a prompt. \e opens the current query buffer (or the last query, if the buffer is empty) in your editor. Set the editor with the PSQL_EDITOR, EDITOR or VISUAL environment variable. When you save and quit, psql loads the text and, if it ends with a semicolon, runs it. \e file.sql edits a file, \ef function_name edits a function's definition and \ev view_name edits a view's. \g runs the buffer, and \r clears it.

Editing in a real editor means syntax highlighting, search and undo, and it makes queries copyable back into files. Editing queries in your own editor from psql sessions covers editor setup, line number jumping and the \ef workflow for changing functions.

Output: files, CSV and the clipboard

Getting results out of psql is a daily need. Four tools.

**\copy** runs COPY through the client, so the file is written on your machine with your permissions, no superuser needed:

\copy (SELECT id, name, total FROM orders WHERE total > 100) TO 'big_orders.csv' WITH (FORMAT csv, HEADER)

The server-side COPY ... TO 'file' writes on the server and needs privileges, which is rarely what you want.

**\o file** redirects all query output to a file until you issue \o again. Pair it with \pset format csv (or --csv on the command line) for CSV from any query.

**\g file and \g | command** send one result to a file or a pipe: SELECT ... \g | pbcopy puts it on the macOS clipboard, \g | less -S pages it.

Format settings such as \pset format html or asciidoc produce output ready to paste into documents.

Saving psql output to a file as CSV, text or HTML pages walks through each with the quirks (headers, delimiters, quoting, encodings).

Passwords and prompts: stop typing, stop mistakes

Typing a password for every connection wastes time and tempts people toward PGPASSWORD in scripts. A ~/.pgpass file with lines host:port:database:user:password (mode 0600) lets psql, pg_dump and most libpq tools find the password themselves (using pgpass to stop typing passwords in psql and tools). Wildcards (*) cover many databases with one line.

A prompt that shows where you are prevents the worst mistake, which is running a destructive command against production. Include the host or an environment label and color it red for production with a prompt per connection (psql can pick settings per host by using a service file or a wrapper script). A habit worth adopting: wrap risky statements in BEGIN; ... check ... COMMIT;, and use ON_ERROR_ROLLBACK and \set AUTOCOMMIT off consciously.

Habits that add up

None of these takes long, and together they change how the day feels.

• Use \d+ and \dt+ to inspect before you query.

• Use \x auto so wide rows never wrap.

• Use \timing always, and EXPLAIN (ANALYZE, BUFFERS) when the number looks wrong.

• Keep queries worth keeping in files and run them with \i or -f; let the history file hold the rest.

• Use \e for anything longer than three lines.

• Use :'var' for any value that did not come from you.

• Use ON_ERROR_STOP=1 in every script.

• Keep a ~/.psqlrc in version control with your dotfiles.

• Use \watch instead of repeating a command by hand.

• Put the password in pgpass and the environment label in the prompt.

Common problems

Output scrolls off the screen with no pager. The pager is off or PAGER is unset; set it, or set \pset pager always.

Unicode borders show as question marks. The terminal is not UTF-8; set the locale or use linestyle ascii.

A script keeps going after an error. ON_ERROR_STOP is missing.

Variables are not substituted. They are inside a dollar-quoted string or a quoted string; psql does not substitute there. Build the statement with \gset or a format() call.

History shows nothing after restart. HISTFILE points to a path that does not exist or is not writable.

The psqlrc output shows up in script results. Add -X to scripts, or wrap psqlrc settings in \set QUIET 1.

A session, start to finish

Habits are easier to see in motion. Suppose a colleague reports that the orders page is slow, and you have a database to look at.

You connect with a service name or a URI and the password comes from pgpass, so there is no prompt. The prompt shows ops@db-staging/shop=>, so you know which environment this is. \dt+ lists tables with sizes, and orders is 4.2 GB, the largest by far. \d orders shows its columns and indexes: there is an index on customer_id and none on created_at. \x auto is already on from the psqlrc, so a single-row query for one order prints as a readable vertical list.

You paste the slow query into the buffer with \e so you can edit it comfortably, add EXPLAIN (ANALYZE, BUFFERS) and run it. The plan shows a sequential scan over the whole table filtering on created_at, with 4.2 GB read. \timing says 3.8 seconds. You try the query again after creating an index in a transaction on a copy, and the timing falls to 14 milliseconds. You save the final query and the CREATE INDEX statement into a file, fix_orders_created_at.sql, with \o turned off and an \echo at the top that says what the file does. You run it on staging with psql -X -v ON_ERROR_STOP=1 -f and keep the output.

Ten minutes, no mouse, a plan and a file you can hand to a reviewer. The speed came from a handful of commands and a configuration that removed friction at each step. The index choice is the part that needed thought; psql removed the delays around it.

Transactions and safety at the prompt

psql runs each statement in its own transaction by default (autocommit on). That makes DELETE FROM orders; instantly permanent. Habits that protect you:

Wrap risky changes in a transaction. BEGIN; then the statement, then check what it did (SELECT count(*) ...), then COMMIT; or ROLLBACK;. The prompt shows * when a transaction is open (with %x in the prompt), which is a reminder not to leave it hanging: an open transaction holds locks and blocks vacuum.

**Use ON_ERROR_ROLLBACK interactive.** Without it, a typo inside a transaction aborts the whole transaction, and everything after errors with "current transaction is aborted". With it, psql wraps each statement in a savepoint in interactive mode, so a failed statement does not ruin the transaction.

Check before you delete. Write the SELECT with the same WHERE first, look at the count and the rows, then change it to DELETE. Use RETURNING to see what changed: DELETE FROM sessions WHERE expires_at < now() RETURNING id;.

Consider read-only sessions for exploration on important servers: SET default_transaction_read_only = on; makes every statement in the session read-only until you turn it off, or connect with a read-only role. Some people set it in the psqlrc for production hosts.

Turn off autocommit deliberately. \set AUTOCOMMIT off makes every statement part of a transaction until you commit. It is a mode some people like and others find confusing; if you use it, make the prompt show the transaction state.

Looking at what the server is doing

psql is the natural tool for asking the server what is happening, and a few queries are worth keeping in your psqlrc or a snippets file.

SELECT pid, usename, state, now() - query_start AS running, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY running DESC;

shows active queries and how long they have run. Combined with \watch 2, it becomes a live monitor. Another query lists blocked sessions and what blocks them by joining pg_locks to itself, or by using pg_blocking_pids(pid). Table sizes: SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_class WHERE relkind = 'r' ORDER BY pg_total_relation_size(oid) DESC LIMIT 10;. Cache hit ratios, index usage and bloat estimates are all a query away. Save the ones you use in files and call them with \i ~/sql/activity.sql, or define psql variables that hold the query text and run them with :activity.

\set activity 'SELECT pid, state, left(query, 60) FROM pg_stat_activity WHERE state <> ''idle'' ORDER BY query_start;'

Then typing :activity and a semicolon, or :activity \g, runs it. That is a small macro system with no extra tools.

Working with remote and production servers

For remote servers, the connection is the first concern: encrypt it (connection strings and URI formats for PostgreSQL clients covers sslmode), and where the database is not reachable directly, use an SSH tunnel: ssh -L 5433:db.internal:5432 bastion, then psql -h localhost -p 5433. Keep the local port different from your own server's 5432 so a tunnel never masks a local database, and name the tunnel in your prompt.

A service file makes this tidy. ~/.pg_service.conf:

[staging]
host=localhost
port=5433
dbname=shop
user=shop_ro
sslmode=require

Then psql service=staging connects, with the password from pgpass. The same service name works for pg_dump. Use read-only roles for exploration on shared servers, and put a visible marker in the prompt for production. Wrapper scripts that choose the right service and set PSQLRC to a stricter profile for production (read-only by default, timing on, loud prompt) are common and effective.

Be conservative about what you run: long queries on production hold resources, so set statement_timeout for your session (SET statement_timeout = '30s';) and use EXPLAIN without ANALYZE when you only need the plan, since ANALYZE actually runs the statement.

psql in automation and CI

psql is the right tool for database steps in a pipeline: create the test database, apply migrations, load fixtures, run assertions. A few settings make it dependable: -X and -q, ON_ERROR_STOP=1, -v for parameters, credentials from the environment or a pgpass file written at the start of the job and the exit code checked. Assertions can be plain SQL that returns rows when something is wrong, wrapped in a shell check:

bad=$(psql -X -A -t -d "$DATABASE_URL" -c "SELECT count(*) FROM orders WHERE total < 0;")
[ "$bad" = "0" ] || { echo "negative totals: $bad"; exit 1; }

-A (unaligned) and -t (tuples only) strip decoration so that the output is just the value. In Docker-based CI, run psql in the database container with docker exec or from a client image on the same network, and wait for readiness with pg_isready first.

Learning path

If this is all new, learn it in order of payoff. First week: connect, \d, \dt, \x, \timing, \e. Second week: a psqlrc, a pager, -f and ON_ERROR_STOP. Third week: variables with :'name', \copy and \o, a pgpass file. After that, add \watch, \gset, conditionals and service files as needs arise. Read the help in \? and \h once; both are shorter than they look. Keep a note of the commands you look up twice and add them to your psqlrc or a cheat sheet.

Sharing what you learn

Fast people leave a trail that makes everyone else faster. Keep a psql snippets directory in the team repository: the activity query, the table sizes query, the blocking locks query, a template for a migration script with ON_ERROR_STOP and an \echo header. Add a short README that says how to run each one and what it is safe on. Share a team psqlrc as a starting point, with a rule that personal tweaks go in a second file included from the first (\ir ~/.psqlrc.local), so the shared settings stay stable.

When you solve a problem at the prompt, write the solution into a file before you forget it. A query that took twenty minutes to get right is worth a filename and a comment. Over a year, the directory becomes the most useful documentation the team owns, because every entry answers a question that someone really asked. It also makes onboarding quicker: a new developer who can run \i snippets/activity.sql on day one is looking at the database, not at a blank prompt.

Finally, teach by pairing. Spend ten minutes at the keyboard with a colleague who still uses a GUI for everything, working through one of their real problems in psql. The point is to show that the prompt is not harder, only different, and that the shortcuts are worth the afternoon. Most people who see \x auto and \watch in a live session adopt them the same week.

When a graphical client is the better tool

psql is weaker for browsing a large unfamiliar schema visually, for editing rows in a grid, for drawing entity diagrams and for sharing a result with someone who does not use a terminal. A GUI is better there. It is also weaker for long exploratory sessions where results should be kept next to notes; notebook tools and IDE query consoles do that. Use both: psql for speed, scripting and anything over SSH, a GUI for exploration and presentation. The skills transfer, because every client sends the same SQL. The psql reference is long and worth reading in pieces, and the libpq documentation on the password file covers pgpass in full.

Where to go next

Start with the psql meta commands worth memorizing for daily work and setting up a psqlrc that makes daily work easier. Then the display pair, expanded display and pager settings in psql for wide rows and pretty printing query results in psql for readable output. For automation, read running SQL files and scripts from psql in automation and using psql variables and conditionals in scripts safely. For the rest, read timing queries and watching output in psql while tuning, editing queries in your own editor from psql sessions, saving psql output to a file as CSV, text or HTML pages and using pgpass to stop typing passwords in psql and tools. The site's tools convert small samples between CSV and JSON in your browser.