56 psql Meta-commands

psql’s meta-commands (also called backslash commands) are client-side shortcuts that make interactive exploration of a database fast and pleasant. They are interpreted by the client, not the server — and work both interactively and in files read with psql -f. Once they become muscle memory, you stop reaching for GUI tools for most day-to-day work.

What you’ll learn

• The essential exploration commands (\l, \dt, \d, \df, \dn)
• Help shortcuts (\?, \h)
• Output and formatting commands (\x, \timing, \pset)
• Running scripts and saving output (\i, \o, \copy)
• Customizing your session with ~/.psqlrc

Example

psql command reference (backslash commands also work in psql -f scripts):

Exploration
  \l                    list databases
  \c dbname             connect to a database
  \dn                   list schemas
  \dt                   list tables in the current search_path
  \dt schema.*          list tables in a specific schema
  \dt *.*               list every table everywhere
  \d table              describe a table (columns, types, indexes, constraints)
  \d+ table             describe a table verbosely (storage, comments)
  \di                   list indexes
  \dv                   list views
  \dm                   list materialized views
  \df pattern           list functions whose name matches
  \du                   list roles (users/groups)
  \dx                   list installed extensions

Help
  \?                    list all meta-commands
  \h                    list SQL commands
  \h SELECT             help on the SELECT statement

Session/output
  \x                    toggle expanded display (column-per-line)
  \timing               toggle showing query timing
  \pset pager off       disable the less-style pager
  \pset format aligned  default table format; alternatives include csv, html, unaligned
  \a                    toggle "unaligned" mode (use format csv for CSV escaping)
  \o file               redirect query output to a file (\o without arg disables)

Files and scripts
  \i file.sql           run a SQL script from inside psql
  \ir file.sql          run relative to the current script
  \copy table FROM 'file.csv' WITH (FORMAT csv, HEADER true)  client-side import
  \copy (SELECT ...) TO 'file.csv' WITH (FORMAT csv, HEADER true)  client-side export

Editing
  \e                    open the last query in $EDITOR (or write a new one)
  \ef function_name     open a function definition in $EDITOR
  \ev view_name         open a view definition in $EDITOR
  \q                    quit

psql -f script.sql processes both SQL and backslash commands. The SQL file linked below shows catalog-query alternatives, useful when the caller is a database driver rather than psql. Do not paste the descriptive labels in this command reference into a script.

The single most useful command is \d table (and its verbose cousin \d+ table). It shows columns, types, defaults, indexes, constraints, foreign keys, and triggers — everything you need to understand a table’s shape.

The single most useful toggle is \x (expanded display). On a row with 30 columns, the default tabular output is unreadable; expanded display prints column | value one per line. Use \x auto to switch only when the row is too wide for the terminal.

\copy is the client-side equivalent of COPY. It reads/writes files on the client machine; COPY reads/writes files on the server. For loading a CSV into a remote PostgreSQL, \copy is what you want.

Run the example

To run the SQL part of this lesson:

$ psql -f source/psql-meta-commands.sql postgres
 ...
 current_database | current_user | current_schema
------------------+--------------+----------------
 postgres         | your_user    | public
(1 row)
 ...

Common pitfalls

• Sending \dt directly through a database driver fails because it is psql syntax. It works interactively and in a file executed by psql -f.
• \d (no arguments) lists everything in the current search_path — long output. Filter: \dt, \di, \dv, etc.
• \copy and COPY: \copy runs on the client (so relative paths are client paths and CSV files come from your machine); COPY runs on the server. Pick the right one based on where the file lives.
• Your psqlrc runs at every session start. A broken statement there can make psql unusable until you fix the file.

\set PROMPT1 '%n@%/%x%R%# '
\set HISTSIZE 5000
\timing on
\pset null '∅'
\set COMP_KEYWORD_CASE upper

Downloads

SQL file: psql-meta-commands.sql · All lesson files · Setup guide