psql

psql is the canonical interactive SQL client for PostgreSQL. It is far more than a REPL: it includes a small scripting language (\if/\elif/\endif), variable substitution, file inclusion, formatted output for nearly every shape of result set, tab completion that consults the live catalog, and dozens of meta-commands that probe the server's metadata. Source: src/bin/psql/.

Directory layout

src/bin/psql/
├── Makefile, meson.build
├── command.c             # \-meta-command dispatcher (large file)
├── common.c              # query execution + result printing glue
├── conditional.c         # \if / \elif / \else / \endif
├── copy.c                # \copy (client-side COPY)
├── crosstabview.c        # \crosstabview formatter
├── describe.c            # \d, \df, \dt, \dn, etc.
├── help.c                # built-in help text
├── input.c               # readline / libedit integration
├── large_obj.c           # \lo_*
├── mainloop.c            # the line-by-line REPL
├── prompt.c              # %-substitution in PROMPT1/2/3
├── psqlscanslash.l       # flex scanner for backslash commands
├── settings.h            # global state (PsqlSettings) shared across files
├── startup.c             # main(), arg parsing, .psqlrc loading
├── stringutils.c         # quoting helpers
├── tab-complete.in.c     # tab completion via libreadline
├── variables.c           # :var substitution
└── ...

What it does

The main loop is in mainloop.c::MainLoop. Each pass:

1. Read a line from stdin (or via libreadline).
2. If it starts with \, dispatch to the meta-command handler in command.c.
3. Otherwise accumulate into a query buffer until a semicolon (respecting quoting and dollar-quoting).
4. When a complete statement is in the buffer, send it via libpq and stream results to the configured output.
5. Update the variables that record the last result (ROW_COUNT, LAST_ERROR_MESSAGE, etc.).

Meta-commands

The hundreds of \ commands fall into a few groups:

Group	Examples
Connection	\c, \conninfo, \password, \encoding
Query buffer	\e (edit in $EDITOR), \g (run), \r (reset), \p (print), \w (write)
Inspection	\d[Sx], \df, \du, \dn, \dT, \l, \dx, ...
Output formatting	\pset format aligned|wrapped|csv|html|..., \x, \a, \H
Variables	\set, \unset, \echo :var
Control flow	\if, \elif, \else, \endif
Files	\i, \ir, \copy, \o, \qecho
Large objects	\lo_import, \lo_export, \lo_unlink, \lo_list
Misc	\watch, \timing, \errverbose, \sf, \sv

\d — without arguments — lists tables. With a name (or pattern) it shows that object's full schema by issuing a series of catalog queries from describe.c. Supporting all the variants (\dS, \d+, \dn, \dx, etc.) makes describe.c one of the larger files in psql.

Backslash command parsing

The scanner for backslash command arguments is psqlscanslash.l (flex). It handles quoting rules different from SQL — backticks for shell command substitution, :'var' for SQL-quoted variable substitution, etc. The dispatcher in command.c looks up the command name in a big switch and consumes additional arguments via psql_scan_slash_option.

Variable substitution

\set foo 42
SELECT * FROM t WHERE id = :foo;            -- numeric substitution (no quoting)
SELECT * FROM t WHERE name = :'foo';        -- quoted as a SQL literal
\set table mytable
SELECT * FROM :"table";                     -- quoted as a SQL identifier

Implemented in variables.c with hooks called from psqlscan.l (the SQL scanner, in src/fe_utils/). Some variables are special: setting AUTOCOMMIT, ON_ERROR_STOP, FETCH_COUNT, etc., reconfigures psql's behavior.

Tab completion

tab-complete.in.c is generated into tab-complete.c and registers with libreadline. The completer parses what's been typed so far and issues catalog queries to suggest object names: tables in scope, column names, function names, GUC names, even pg_locale_* for SET-related contexts. Adding completions for a new SQL feature is a frequent first patch from new contributors.

.psqlrc and PSQLRC

At startup, psql reads ~/.psqlrc (and ~/.psqlrc-<version> if it exists) as if its contents were typed at the prompt. Useful for setting \timing on, custom prompts, common views.

Output formatting

pset format toggles between aligned (default; pretty box-drawing borders), unaligned, wrapped, html, latex, troff-ms, csv, and asciidoc. The renderer (src/fe_utils/print.c, shared with pg_dump for \d-style output) is one of the more polished pieces of frontend code. CSV output supports proper RFC 4180 quoting; HTML output is XHTML-clean.

\x [auto] flips into expanded mode (one column per line). \a toggles aligned. \H switches to HTML.

Watch mode

\watch [interval] re-runs the last query every interval seconds (default 2). Useful for monitoring pg_stat_activity, pg_stat_replication, or any "live" view. Cancel with Ctrl-C.

Scripting

\if :{?some_var}
  \echo "set"
\elif true
  \echo "fallback"
\endif

conditional.c implements an evaluation stack of if-true, if-false, if-skip. Combined with variables and the \gset command (which captures a query result into psql variables), it enables small reproducible setup scripts.

Large objects

\lo_* works on PostgreSQL's pg_largeobject storage — a legacy mechanism for blobs that predates TOAST. Most users no longer touch large objects directly but the commands remain.

Important psql-specific details

• autocommit. \set AUTOCOMMIT off keeps every statement inside a transaction until you COMMIT. Default is on.
• ON_ERROR_STOP. Critical for non-interactive use. Without it, psql -f script.sql continues past errors.
• FETCH_COUNT. When set to a positive integer, psql fetches result sets in chunks via cursors instead of loading the entire result into memory. Vital for huge SELECTs.
• SHOW_CONTEXT verbose. Prints the full server CONTEXT for messages.

Testing

src/bin/psql/t/ carries TAP tests covering meta-command behavior, variable substitution, scripting, and edge cases. Run with make check -C src/bin/psql.

Entry points for modification

• New meta-command: add a case to the dispatcher in command.c. Most existing commands are good templates — pick one with similar argument shape.
• New \d query: extend describe.c. Watch out for backwards compatibility — \d output is informally part of psql's "API."
• New tab-completion case: edit tab-complete.in.c and rebuild.
• New output format: extend src/fe_utils/print.c and add the format name to psql's pset format parser.

For libpq itself — the wire-level client psql uses — see libpq. For the broader pile of frontend tools, see Apps.