skip to content

psql

psql is PostgreSQL's own terminal client: backslash meta-commands, \copy, variables and scripts that stop on error, plus 18's \parse and pipeline commands. Interviewers probe the 3 a.m. toolkit.

on this pageshow

explore

questions

6

In psql, which backslash meta-commands list and describe a database's objects, and what do the + and S modifiers and \x change?

level: juniorimportance: must knowfreq 72%

answer

  1. psql itself, not the server
  2. d for describe
  3. plus adds size and comments
  4. S brings back system objects
  5. x turns rows sideways

basics

~20 s

The \d family: \dt lists tables, \d name describes one relation, \l databases, \dn schemas, \du roles, \df functions. A + suffix adds detail such as size and comments, S includes system objects, and \x prints rows as field/value lines.

solid answer

~40 s

The describe meta-commands are interpreted by psql, which turns them into catalog queries. `\d` on its own lists the visible tables, views, materialized views, sequences and foreign tables, and `\d tracking.parcels` describes one relation: columns, types, defaults, `NOT NULL`, indexes, constraints and triggers. Narrower lists are `\dt`, `\di`, `\dv`, `\dn` (schemas), `\df` (functions), `\du` (roles), `\dx` (extensions) and `\l` (databases). A `+` suffix adds detail: `\dt+` shows persistence, size on disk and description, and `\d+` adds column comments, a view's definition and the access method. `S` includes system objects, which are hidden by default. `\x` toggles expanded output, one `column | value` line per field, and `\x auto` switches to it only when a row is wider than the screen. `\?` lists every meta-command and `\q` quits.

go deeper

for a junior

Recall the everyday commands by name: \dt to list tables, \d table to describe one, \l, \dn, \du, \x and \q. Interviewers want to see you have actually used psql.

for a middle

Explain that meta-commands are psql's own catalog queries, what + and S add, how patterns and search_path decide what appears, and when \x auto beats a wrapped grid.

for a senior

Show the habits of someone who works on unfamiliar production databases: confirm the connection first, size tables with \dt+, read a relation with \d+, and use -E to turn a meta-command into a reusable catalog query.

for a principal

Frame psql fluency as an operational baseline: a team that cannot inspect a database without a GUI is slow during incidents. Push for shared .psqlrc conventions and runbooks written as psql commands.

## Meta-commands belong to psql, not to the server Anything you type in **psql** that starts with an unquoted backslash is a **meta-command** (also called a backslash command). psql handles it itself instead of sending it to the server. The describe commands work by running ordinary queries against the system catalogs (`pg_class`, `pg_attribute`, `pg_namespace` and the rest) and formatting the answer. That has two practical consequences: - They are psql's commands, not SQL. A driver or a GUI that sends text straight to the server does not understand `\dt`; to the server it is just a syntax error. - They only read catalogs. Describing objects changes nothing, which is why they are the first thing to reach for on an unfamiliar database. ## The \d family at a glance | Command | What it shows | |---|---| | `\d` (no pattern) | every visible table, view, materialized view, sequence and foreign table | | `\d tracking.parcels` | one relation: columns, types, defaults, `NOT NULL`, indexes, constraints, triggers | | `\dt`, `\di`, `\dv`, `\dm`, `\ds` | tables, indexes, views, materialized views, sequences | | `\dn` | schemas | | `\df` | functions | | `\du` | roles | | `\dx` | installed extensions | | `\dconfig` | server settings; without a pattern only the non-default ones | | `\l` | databases | The letters combine: `\dti` lists tables and indexes together. ## The modifiers: +, S, x and patterns - **`+`** asks for more. `\dt+` adds each table's persistence (permanent, temporary or unlogged), its **physical size on disk** and its description. `\d+ tracking.parcels` adds column comments, a non-default replica identity and the access method, and for a view its definition. `\l+` adds database sizes and default tablespaces. - **`S`** includes **system objects**. By default only user-created objects are listed; `\dtS` or `\dfS` also show the catalog tables and built-in functions. - **Patterns** use shell-style wildcards: `*` matches any run of characters and `?` one character, and `schema.name` restricts by schema. Unquoted names fold to lower case, so a table created as `"ParcelScan"` needs `\d "ParcelScan"`. - **Visibility**: without a pattern the commands show only objects reachable through the current `search_path`. `\dt tracking.*` lists one schema and `\dt *.*` lists everything. ## Expanded output with \x A wide row, such as one row of `pg_stat_activity`, wraps into an unreadable grid in the default aligned format. `\x` (the same as `\pset expanded`) toggles **expanded mode**: each row is printed as a block of `column | value` lines. Three forms are worth knowing: 1. `\x` on its own toggles between on and off. 2. `\x auto` uses expanded mode only when a result has more than one column and is wider than the screen; it is a common line in `~/.psqlrc`. 3. `\gx` instead of `;` runs one query in expanded mode without changing the setting. ## Seeing the SQL behind a meta-command Start psql with **`-E`** (`--echo-hidden`), or run `\set ECHO_HIDDEN on`, and psql prints the catalog query each backslash command sends before it shows the result. That is the honest answer to "how does `\d` know that?", and the way to lift a catalog query into a monitoring script that cannot run psql. ## Getting help without leaving psql Nobody memorises all of the backslash commands, and interviewers know it. What they look for is that you know where the answers live: - `\?` lists every meta-command with a one-line description; `\? variables` explains psql's special variables and `\? options` the command-line flags. - `\h` on its own lists the SQL commands, and `\h CREATE INDEX` prints that command's full syntax. - `\dconfig work_mem` shows a server setting's current value, and `\dconfig+ work_mem` adds its type and the context in which it can be changed. - `psql --help=commands` prints the meta-command list from the shell, before you connect. ## A first look at an unfamiliar database An on-call engineer dropped onto a production database usually runs something like this before touching anything: 1. `\conninfo` to confirm the database, host and user. 2. `\l+` to see the databases and their sizes. 3. `\dn`, then `\dt+ tracking.*`, to find the schemas and the biggest tables. 4. `\d+ tracking.parcels` to see the hot table's columns, indexes and constraints. 5. `\dx` to see which extensions are installed. `\?` lists every meta-command, `\h CREATE INDEX` prints SQL syntax help, and `\q` quits.

  • How can you find out which catalog query psql runs for \d so you can reuse it elsewhere?
    Start psql with `-E` (`--echo-hidden`) or run `\set ECHO_HIDDEN on`. psql then prints each query it sends for `\d` and the other backslash commands before showing the result. You can copy those `pg_catalog` queries into a script or a monitoring tool, because the meta-command itself only works inside psql.
  • You know a table exists, but \dt does not list it. What are the usual reasons?
    Without a pattern, `\dt` lists only objects visible through the current `search_path`, so a table in another schema needs `\dt tracking.*` or `\dt *.*`. If you created it with a quoted mixed-case name, an unquoted pattern is folded to lower case and misses it: use `\dt "ParcelScan"`. And if the object is really a view or a materialized view, `\dt` skips it; `\dv` and `\dm` list those.

  • Saying \dt works the same way when sent through any database driver
  • Believing \dt lists the system catalog tables by default
  • Claiming \dt+ shows an exact row count for each table
  • Reaching for SHOW TABLES or DESCRIBE to inspect tables in PostgreSQL
  • Thinking \x changes what the server sends rather than how psql prints it
open as a page

When psql runs a migration file with -f and a statement in the middle fails, what happens by default, and how do ON_ERROR_STOP and --single-transaction change it?

level: middleimportance: must knowfreq 60%

basics

~20 s

By default psql prints the error, runs the rest of the file and exits 0. ON_ERROR_STOP=1 stops at the first error with exit status 3; -1 wraps the file in BEGIN/COMMIT, sending ROLLBACK instead when ON_ERROR_STOP catches a failure.

open as a page

In psql, how does the \copy meta-command differ from the server-side COPY command, and when do you need \copy instead?

level: middleimportance: should knowfreq 56%

basics

~20 s

psql's \copy runs COPY ... FROM STDIN or TO STDOUT, and psql reads or writes the file on the client host with the client user's permissions. Server-side COPY uses the server's filesystem and needs superuser or pg_read_server_files / pg_write_server_files.

open as a page

During an incident, how do psql's \watch, \timing and \conninfo help you, and what exactly does each one show or measure?

level: middleimportance: should knowfreq 42%

basics

~20 s

\watch reruns the current or last query every 2 seconds until cancelled or a count or row floor stops it. \timing prints each statement's elapsed time measured by psql, network included. \conninfo shows the database, host, user and backend you are on.

open as a page

In a psql deployment script, how do you pass parameters safely with -v and :'var', and generate DDL from a catalog query with \gexec?

level: seniorimportance: should knowfreq 30%

basics

~20 s

psql substitutes variables set with -v or \set before sending: :'name' as an escaped literal, :"name" as a quoted identifier. \gexec executes each cell of a query's result as its own statement, so build those statements with format() and %I.

open as a page

In psql 18, what do \parse, \bind_named and the \startpipeline ... \endpipeline commands add, and when would you reach for them?

level: seniorimportance: nice to knowfreq 14%

basics

~20 s

psql normally uses the simple query protocol. psql 18's \parse creates a named protocol-level prepared statement, \bind_named executes it with parameters and \close_prepared closes it; \startpipeline ... \endpipeline queue queries without waiting for each result, as drivers do.

open as a page