In psql, which backslash meta-commands list and describe a database's objects, and what do the + and S modifiers and \x change?
answer
- psql itself, not the server
- d for describe
- plus adds size and comments
- S brings back system objects
- x turns rows sideways
basics
~20 sThe \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 sThe 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
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.
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.
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.
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