Essential PostgreSQL Commands Reference

You have a psql prompt open on a database you did not build, and you need to see what is in it before you can write a query. psql answers with backslash commands: \l lists the databases, \dt lists the tables, and \d describes one table. The commands below are the ones you reach for first, on PostgreSQL 18:

Every output below comes from PostgreSQL 18.6. To follow along, create a database called sports_db as the postgres role, and run this in it:

CREATE TABLE teams (
  team_id int CONSTRAINT teams_pkey PRIMARY KEY,
  name    text NOT NULL,
  city    text
);

CREATE TABLE players (
  player_id int CONSTRAINT players_pkey PRIMARY KEY,
  team_id   int NOT NULL CONSTRAINT players_team_fk REFERENCES teams,
  name      text NOT NULL,
  position  text
);

CREATE VIEW team_roster AS
  SELECT t.name AS team, p.name AS player
  FROM teams t JOIN players p USING (team_id);

INSERT INTO teams VALUES (1, 'Lions', 'Leeds'), (2, 'Hawks', 'Hull');
INSERT INTO players VALUES (10, 1, 'Ana', 'C'), (11, 1, 'Ben', 'G'), (12, 2, 'Cy', NULL);

CREATE ROLE reporting LOGIN PASSWORD 'change-me';

Connect to a database

psql -h db.internal -p 5432 -d sports_db -U reporting

psql connects to sports_db on the server db.internal as the role reporting, and asks for a password when the server wants one. If you leave out -h, psql connects to a server on your own machine. If you leave out -d and -U, psql uses your operating-system user name as both the database name and the role.

The prompt shows where you are: the database name, then =# for a superuser or => for any other role. So sports_db=> means that you are in sports_db without superuser rights.

\c warehouse moves the session to the database warehouse without quitting psql. \c warehouse alice moves it to warehouse as the role alice. \conninfo prints a table about the connection: the database, the role, the host or socket, the port, whether SSL is in use, and more. The table format is new in PostgreSQL 18.

List databases, schemas and tables

\l lists every database on the server, with its owner, its encoding and its access privileges. \dn lists the schemas of the current database. \dt lists the tables:

           List of tables
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | players | table | postgres
 public | teams   | table | postgres
(2 rows)

team_roster is missing, because \dt lists tables only. \dv lists the views, \di the indexes, and \df the functions and procedures.

Each of these commands takes a pattern. \dt information_schema.* lists the tables of one schema, and \dt *.* lists the tables of every schema. Without a schema in the pattern, \dt shows only the tables in the schemas of your search path. Add + for more columns: \dt+ adds each table's persistence, access method, size and description.

psql answers \dt with a query on the system catalogs. Start psql with -E to print that query above each result. That query is a good start when you need the table list in a script.

Describe one table

\d players prints the columns with their types, nullability and defaults, then the indexes and the constraints:

                Table "public.players"
  Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 player_id | integer |           | not null |
 team_id   | integer |           | not null |
 name      | text    |           | not null |
 position  | text    |           |          |
Indexes:
    "players_pkey" PRIMARY KEY, btree (player_id)
Foreign-key constraints:
    "players_team_fk" FOREIGN KEY (team_id) REFERENCES teams(team_id)

The primary key is listed under Indexes, because PostgreSQL enforces it with a unique index. \d teams shows the same foreign key from the other side:

Referenced by:
    TABLE "players" CONSTRAINT "players_team_fk" FOREIGN KEY (team_id) REFERENCES teams(team_id)

\d+ team_roster adds the view's query under the heading View definition, so you can read a view without leaving psql. \d with no name lists every table, view, materialized view, sequence and foreign table on your search path. The same column list also comes from a query on information_schema.

Roles and privileges

\du lists the roles with their attributes:

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 reporting |

Since PostgreSQL 16, \du no longer shows which roles each role is a member of. \drg lists each role membership instead, with its options (ADMIN, INHERIT, SET) and the role that granted it.

\dp shows the privileges on tables, views and sequences. Grant reporting read access to one table, then ask for its privileges:

GRANT SELECT ON players TO reporting;

sports_db=# \dp players
                                  Access privileges
 Schema |  Name   | Type  |     Access privileges      | Column privileges | Policies
--------+---------+-------+----------------------------+-------------------+----------
 public | players | table | postgres=arwdDxtm/postgres+|                   |
        |         |       | reporting=r/postgres       |                   |
(1 row)

Each line of the Access privileges column reads grantee=privileges/grantor. The privilege letters for a table are r SELECT, a INSERT, w UPDATE, d DELETE, D TRUNCATE, x REFERENCES, t TRIGGER and m MAINTAIN, so arwdDxtm is every privilege. An empty Access privileges column means that the table still has its default privileges: the owner has every privilege, and no other role is granted anything.

Run a file and export rows

\i setup.sql runs the statements in setup.sql as if you had typed them. \copy writes a table or a query result to a file on your machine:

sports_db=# \copy players TO 'players.csv' WITH (FORMAT csv, HEADER)
COPY 3

players.csv now holds the three players:

player_id,team_id,name,position
10,1,Ana,C
11,1,Ben,G
12,2,Cy,

\copy reads and writes files with your own permissions, on the machine where psql runs. The SQL statement COPY reads and writes files on the server instead. With a file name, COPY needs a superuser or a role such as pg_write_server_files.

Dump a database and restore it

pg_dump, pg_restore and pg_dumpall are programs of their own. Run them in your shell, not at the psql prompt. At the prompt, psql sends them to the server as SQL, which fails:

sports_db=# pg_dump sports_db;
ERROR:  syntax error at or near "pg_dump"
LINE 1: pg_dump sports_db;
        ^

Dump in the custom format, then load the file into a new database:

pg_dump -h db.internal -U postgres -Fc -f sports_db.dump sports_db
createdb -h db.internal -U postgres sports_new
pg_restore -h db.internal -U postgres -d sports_new sports_db.dump

-Fc selects the custom format: a compressed archive that pg_restore reads. Without -F, pg_dump writes a plain SQL script that psql -f replays. pg_restore -l sports_db.dump lists what the file holds.

Dump as a role that can read every table. When reporting runs pg_dump, it can read players but not teams, and the dump fails:

pg_dump: error: query failed: ERROR:  permission denied for table teams
pg_dump: detail: Query was: LOCK TABLE public.teams, public.players IN ACCESS SHARE MODE

On a new server, create the roles before you restore. A database dump holds no roles, because a role belongs to the whole server. Without them, the restore reports an error at every GRANT to a missing role:

pg_restore: error: could not execute query: ERROR:  role "reporting" does not exist
Command was: GRANT SELECT ON TABLE public.players TO reporting;

pg_dumpall -g writes the roles and tablespaces of the server as a SQL script for psql.

To restore over an existing database, add --clean --if-exists. --clean drops each object before it creates it again, and --if-exists skips the objects that are not there yet, which would otherwise report errors.

Find the session that blocks a query

Two sessions update the same player. The first one forgets to commit:

-- session 1
BEGIN;
UPDATE players SET position = 'F' WHERE player_id = 12;

-- session 2
UPDATE players SET position = 'D' WHERE player_id = 12;

Session 2 now waits. pg_stat_activity has one row for each session, and pg_backend_pid() leaves out your own:

SELECT pid, usename, state, wait_event_type, query
FROM pg_stat_activity
WHERE datname = 'sports_db' AND pid <> pg_backend_pid();

 pid | usename  |        state        | wait_event_type |                          query
-----+----------+---------------------+-----------------+---------------------------------------------------------
 120 | postgres | idle in transaction | Client          | UPDATE players SET position = 'F' WHERE player_id = 12;
 127 | postgres | active              | Lock            | UPDATE players SET position = 'D' WHERE player_id = 12;
(2 rows)

Session 120 is idle in transaction. The session is inside a transaction and runs nothing, but it still holds the lock on Cy's row. The query column shows the last statement that session 120 ran. Session 127 is active and waits for that lock. An idle in transaction session usually comes from a BEGIN that nobody committed or rolled back.

pg_blocking_pids returns the sessions that block a given session:

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

 pid | blocked_by |                          query
-----+------------+---------------------------------------------------------
 127 | {120}      | UPDATE players SET position = 'D' WHERE player_id = 12;
(1 row)

End the blocking session, and its transaction rolls back:

SELECT pg_terminate_backend(120);

 pg_terminate_backend
----------------------
 t
(1 row)

Session 127 then gets the lock, and Cy's position becomes D. pg_cancel_backend would not have helped here. It stops a running query and keeps the session open, and session 120 was running nothing.

You can stop a session with either function when it is your own or belongs to a role that you are a member of. A role granted pg_signal_backend can stop any session except a superuser's, which only a superuser can stop.

Help, history and output

\? lists every backslash command, and \h CREATE TABLE prints the syntax of that statement. \s prints the history of your commands, and \s history.sql saves it to a file. \q quits psql.

\x turns on expanded output, which prints each column of a row on its own line. A row with forty columns becomes readable:

sports_db=# \x
Expanded display is on.
sports_db=# SELECT * FROM players WHERE player_id = 10;
-[ RECORD 1 ]--
player_id | 10
team_id   | 1
name      | Ana
position  | C

\timing prints the time of each statement in milliseconds, such as Time: 0.433 ms, under its result. Type the same command again to turn it off.

See the tables as a diagram in DbSchema

\d describes one table at a time, and a foreign key is a line of text. To see how the tables of a larger schema join, you read one output per table and keep the joins in your head. DbSchema connects to the same database and reverse-engineers its schema into a diagram. DbSchema draws the tables of the schemas you import, with a line for each foreign key. Connecting and reverse-engineering only read from the database.

The SQL Editor sits next to the diagram for the queries you would otherwise type at the psql prompt.

Download DbSchema and connect it to the database your psql prompt is on. Connecting, reverse-engineering into a diagram and the SQL Editor are in the free Community Edition.