psql commands: a cheat sheet

The short version

Commands that start with a backslash are run by psql itself, not sent to the server. They end at the end of the line and don’t need a semicolon.

Every example below was run with psql 18.6 against PostgreSQL 18.6, except the one marked psql 17.

Connect, switch and quit

\c (or \connect) opens a new connection and closes the old one. With fewer arguments it keeps the current user, host and port:

inlet=# \c postgres
You are now connected to database "postgres" as user "inlet".

The full form is \c <db> <user> <host> <port>; a - keeps the current value. It also takes a connection string: \c postgresql://<user>@<host>:5432/<db>?sslmode=require. See PostgreSQL connection strings.

\l lists databases (\l+ adds sizes):

                                                 List of databases
    Name    | Owner | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules | Access privileges
------------+-------+----------+-----------------+------------+------------+--------+-----------+-------------------
 inlet      | inlet | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |
 postgres   | inlet | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |
 template0  | inlet | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/inlet         +
            |       |          |                 |            |            |        |           | inlet=CTc/inlet
 …

\conninfo shows where you are. psql 18 prints a table:

      Connection Information
      Parameter       |   Value
----------------------+-----------
 Database             | inlet
 Client User          | inlet
 Host                 | localhost
 Host Address         | ::1
 Server Port          | 54318
 Options              |
 Protocol Version     | 3.0
 Password Used        | true
 GSSAPI Authenticated | false
 Backend PID          | 280184
 SSL Connection       | false
 Superuser            | on
 Hot Standby          | off
(13 rows)

psql 17 and earlier print one line instead (this one is psql 17):

You are connected to database "inlet" as user "inlet" on host "localhost" (address "::1") at port "5432".

\q quits (so does Ctrl-D on an empty line).

Look around: \dt, \d and friends

The \d family describes objects. Each takes an optional pattern: \dt order* lists tables whose name starts with order, \dt sales.* lists every table in schema sales. Add + for more detail (sizes, comments) and S to include system objects (\dfS).

\dt lists tables in the schemas on your search_path:

inlet=# \dt order*
            List of tables
 Schema |    Name     | Type  | Owner
--------+-------------+-------+-------
 public | order_items | table | inlet
 public | orders      | table | inlet
(2 rows)

\d <table> shows columns, indexes and constraints, and which tables point at it:

inlet=# \d orders
                                      Table "public.orders"
   Column    |           Type           | Collation | Nullable |             Default
-------------+--------------------------+-----------+----------+----------------------------------
 id          | bigint                   |           | not null | generated by default as identity
 user_id     | bigint                   |           | not null |
 status      | order_status             |           | not null | 'pending'::order_status
 total       | numeric(12,2)            |           | not null |
 placed_at   | timestamp with time zone |           | not null |
 ship_window | interval                 |           |          |
 notes       | text                     |           |          |
 metadata    | jsonb                    |           |          |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
    "orders_pending_idx" btree (placed_at) WHERE status = 'pending'::order_status
    "orders_placed_at_idx" btree (placed_at DESC)
    "orders_user_id_idx" btree (user_id)
Foreign-key constraints:
    "orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
Referenced by:
    TABLE "sales.invoices" CONSTRAINT "invoices_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id)
    TABLE "order_items" CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE

\d also works on views, indexes, sequences and types. \d+ adds storage, statistics target and column comments; in psql 18 it also lists not-null constraints by name:

inlet=# \d+ users
…
 settings   | jsonb                    |           | not null | '{}'::jsonb                  | extended |             |              | Free-form preferences; validated by the app.
…
Not-null constraints:
    "users_id_not_null" NOT NULL "id"
    "users_uuid_not_null" NOT NULL "uuid"
…
Access method: heap

\dt+ adds the size:

inlet=# \dt+ orders
                                   List of tables
 Schema |  Name  | Type  | Owner | Persistence | Access method | Size  | Description
--------+--------+-------+-------+-------------+---------------+-------+-------------
 public | orders | table | inlet | permanent   | heap          | 17 MB |
(1 row)

The rest of the family, one example each:

inlet=# \dn
         List of schemas
    Name     |       Owner
-------------+-------------------
 extensions  | inlet
 public      | pg_database_owner
 sales       | inlet
 …

inlet=# \dv
            List of views
 Schema |     Name     | Type | Owner
--------+--------------+------+-------
 public | active_users | view | inlet
(1 row)

inlet=# \di orders*
                    List of indexes
 Schema |         Name         | Type  | Owner | Table
--------+----------------------+-------+-------+--------
 public | orders_pending_idx   | index | inlet | orders
 public | orders_pkey          | index | inlet | orders
 public | orders_placed_at_idx | index | inlet | orders
 public | orders_user_id_idx   | index | inlet | orders
(4 rows)

inlet=# \df pg_size_pretty
                              List of functions
   Schema   |      Name      | Result data type | Argument data types | Type
------------+----------------+------------------+---------------------+------
 pg_catalog | pg_size_pretty | text             | bigint              | func
 pg_catalog | pg_size_pretty | text             | numeric             | func
(2 rows)

inlet=# \du inlet
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 inlet     | Superuser, Create role, Create DB, Replication, Bypass RLS

inlet=# \dx
                                                     List of installed extensions
        Name        | Version | Default version |   Schema   |                              Description
--------------------+---------+-----------------+------------+------------------------------------------------------------------------
 citext             | 1.8     | 1.8             | public     | data type for case-insensitive character strings
 hstore             | 1.8     | 1.8             | public     | data type for storing sets of (key, value) pairs
 pg_stat_statements | 1.12    | 1.12            | extensions | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
(4 rows)

\df without a schema in the pattern searches your search_path and pg_catalog, which is how pg_size_pretty was found. \sf <function> prints a function’s source.

Shape the output: \x, \pset, \o, \timing

\x toggles expanded output: one line per column, which suits wide rows and JSON. \x auto uses it only when a row is wider than the screen.

inlet=# \x
Expanded display is on.
inlet=# SELECT id, email, name, role, settings, created_at FROM users WHERE id = 1;
-[ RECORD 1 ]----------------------------------------------------------------------------------
id         | 1
email      | user1@example.com
name       | Grace Hopper 1
role       | admin
settings   | {"beta": false, "theme": "dark", "notifications": {"push": false, "email": false}}
created_at | 2026-10-09 01:03:37.105842+00

\timing prints how long each statement took, measured by psql (so it includes the network round trip):

inlet=# \timing
Timing is on.
inlet=# SELECT count(*) FROM orders WHERE status = 'pending';
 count
-------
 16666
(1 row)

Time: 7.338 ms

\pset changes how results look. The ones people use most:

inlet=# \pset null '(null)'
Null display is "(null)".
inlet=# \pset border 2
Border style is 2.
inlet=# SELECT 1 AS a, NULL AS b;
+---+--------+
| a |   b    |
+---+--------+
| 1 | (null) |
+---+--------+
(1 row)

inlet=# \pset format csv
Output format is csv.
inlet=# SELECT 1 AS a, NULL AS b;
a,b
1,(null)

Formats include aligned (the default), unaligned, wrapped, csv, html, asciidoc, latex and troff-ms. Note that the null setting applies to CSV output too; reset it with \pset null '' before exporting.

\o <file> sends the results of the following queries to a file; \o on its own switches back. \o | <command> pipes them to a shell command instead.

inlet=# \o recent.txt
inlet=# SELECT id, status, placed_at FROM orders ORDER BY placed_at DESC LIMIT 3;
inlet=# \o
inlet=# \! cat recent.txt
  id   | status  |           placed_at
-------+---------+-------------------------------
 86400 | pending | 2026-10-09 02:03:37.466352+00
   720 | pending | 2026-10-09 01:51:37.466352+00
 87120 | pending | 2026-10-09 01:51:37.466352+00
(3 rows)

(\! <command> runs a shell command without leaving psql.)

Write, run and repeat: \e, \i, \watch, \gset

\e opens the current query in your editor ($PSQL_EDITOR, then $EDITOR, then $VISUAL). If the query buffer is empty it opens the last query you ran. When you save and close the editor, psql runs what you wrote if it ends with a semicolon. \e <file> edits a file instead; \ef <function> edits a function’s definition.

\i <file> runs the commands in a file as if you’d typed them:

inlet=# \i report.sql
 users
-------
 12000
(1 row)

\ir does the same, but resolves a relative path from the directory of the script that calls it, which is what you want in scripts that include other scripts.

\watch runs the query in the buffer again every 2 seconds (or the interval you give) until you press Ctrl-C:

inlet=# SELECT state, count(*) FROM pg_stat_activity
inlet-#  WHERE datname = current_database() GROUP BY state ORDER BY state \watch i=5 c=2
Fri Oct  9 11:26:38 2026 (every 5s)

 state  | count
--------+-------
 active |     2
 idle   |     2
(2 rows)

Fri Oct  9 11:26:43 2026 (every 5s)

 state  | count
--------+-------
 active |     1
 idle   |     2
(2 rows)

\watch 5 works in every version. The named options need a recent psql: i= (interval) and c= (stop after this many runs) from psql 16, m= (stop once the query returns fewer than this many rows) from psql 17. Older versions don’t recognise c=2 and keep going.

\gset runs the query and stores each column of its one-row result in a psql variable named after the column. Use the variables with :name:

inlet=# SELECT count(*) AS n, pg_size_pretty(pg_total_relation_size('orders')) AS size FROM orders \gset
inlet=# \echo orders has :n rows and takes :size
orders has 100000 rows and takes 29 MB

Inside SQL, :'name' inserts the value as a quoted literal and :"name" as a quoted identifier. A query that returns no rows or more than one row sets nothing and says so (no rows returned for \gset, more than one row returned for \gset). \gset prefix_ prefixes the variable names.

Import and export: \copy

\copy runs COPY with the file on your machine. SQL COPY … TO '/path' writes a file on the database server and needs special privileges; \copy streams the data through psql and reads or writes the file with your own permissions. The whole command must be on one line.

Export a query to CSV:

inlet=# \copy (SELECT id, status, placed_at FROM orders ORDER BY id LIMIT 3) TO 'orders.csv' WITH (FORMAT csv, HEADER)
COPY 3
inlet=# \! cat orders.csv
id,status,placed_at
1,paid,2026-10-08 02:03:36.466352+00
2,shipped,2026-10-07 02:03:35.466352+00
3,delivered,2026-10-06 02:03:34.466352+00

Import a CSV into an existing table (here a scratch table, inside a transaction we rolled back):

inlet=# BEGIN;
BEGIN
inlet=*# CREATE TABLE seo_terms.orders_import (id bigint, status text, placed_at timestamptz);
CREATE TABLE
inlet=*# \copy seo_terms.orders_import FROM 'orders.csv' WITH (FORMAT csv, HEADER)
COPY 3
inlet=*# ROLLBACK;
ROLLBACK

TO STDOUT and FROM STDIN work too, so you can pipe: psql -c "\copy orders TO STDOUT WITH (FORMAT csv)" > orders.csv.

Help: ? and \h

\? lists every backslash command with a one-line description:

inlet=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display result in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [(OPTIONS)] [FILE]  execute query (and send result to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store result in psql variables
  …

\h lists SQL commands; \h <command> shows its syntax and a link to the manual page:

inlet=# \h create index
Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ NULLS [ NOT ] DISTINCT ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

URL: https://www.postgresql.org/docs/18/sql-createindex.html

\errverbose is worth knowing too: after an error, it prints the last error again with every detail, including the SQLSTATE code and where in the server’s source it was raised.

The same things in Inlet

If you’d rather click than type, here’s where Inlet keeps the equivalents:

EXPLAIN ANALYZE output, which psql prints as text, Inlet draws as a tree with the slowest step highlighted. For plans you already have, there’s the free plan visualizer.