psql cheat sheet: essential PostgreSQL commands

psql is the command-line client that ships with PostgreSQL. Besides plain SQL, it understands short meta-commands that start with a backslash, such as \l to list databases. They do not end with a semicolon. This cheat sheet groups the ones you will use most on a server, with examples you can paste straight in.

Before you start

• A Linux VPS with PostgreSQL installed. To get a current version, see Install PostgreSQL from the PGDG repository.
• SSH access as root or a sudo user.
• The examples use the database appdb and the role appuser. Replace them with your own.

Connect

# As the postgres superuser, through the local socket (no password)
sudo -u postgres psql

# Straight into a database
sudo -u postgres psql -d appdb

# As an application role over TCP, prompting for its password
psql -h 127.0.0.1 -U appuser -d appdb

# A non-default port, for a second cluster
sudo -u postgres psql -p 5433

You can also pass a connection URI, such as psql "postgresql://appuser@127.0.0.1:5432/appdb". Leave the password out of the URI. psql will prompt for it, and it stays out of your shell history.

Once connected, \conninfo shows which database, user, host and port you are on.

List things

\l              list databases
\c appdb        connect to another database
\dn             list schemas
\dt             list tables in the search path
\dt public.*    list tables in one schema
\dt+            tables with their size and description
\d orders       columns, indexes and constraints of a table
\di             list indexes
\dv             list views
\df             list functions
\du             list roles and their attributes
\dp orders      privileges on a table

Add + to most list commands for extra detail, and a pattern to filter, for example \dt order*.

Make output readable

\x              toggle expanded display (one column per line)
\x auto         expanded only when rows are too wide
\timing         show how long each query takes
\pset pager off turn off the pager for long results

When a result opens in the pager, press q to return to the prompt. Turning on \x auto is the single most useful setting for wide tables.

Run SQL and SQL files

Inside psql:

\i /home/deploy/schema.sql     run a file
\e                             edit the last query in your editor
\g                             run the last query again

From the shell, without opening an interactive session:

# Run a single statement
sudo -u postgres psql -d appdb -c "SELECT count(*) FROM orders;"

# Run a file and stop at the first error
psql -h 127.0.0.1 -U appuser -d appdb -v ON_ERROR_STOP=1 -f schema.sql

ON_ERROR_STOP=1 makes psql exit with an error code instead of carrying on after a failed statement. Use it in scripts and deploy jobs.

Export query results

\copy runs on the client side, so it writes files as your Linux user, not as the database server. That avoids permission problems.

\copy (SELECT id, email, created_at FROM customers) TO '/home/deploy/customers.csv' WITH (FORMAT csv, HEADER)

\copy must fit on one line. To import a CSV back into an existing table:

\copy customers FROM '/home/deploy/customers.csv' WITH (FORMAT csv, HEADER)

From the shell, --csv prints a query as CSV, which you can redirect to a file:

psql -h 127.0.0.1 -U appuser -d appdb --csv -c "SELECT * FROM orders LIMIT 100" > orders.csv

To send all output from an interactive session to a file, use \o /home/deploy/out.txt, run your queries, then \o on its own to switch back to the screen.

Transactions and safety

psql commits each statement on its own unless you open a transaction. For risky changes, wrap them so you can back out:

BEGIN;
UPDATE orders SET status = 'cancelled' WHERE created_at < '2024-01-01';
-- check the result, then either:
COMMIT;
-- or:
ROLLBACK;

While a transaction is open, the prompt shows appdb=*#. If a statement fails inside it, the prompt shows appdb=!# and you must ROLLBACK; before you continue.

Help and quit

\?              list all meta-commands
\h              list SQL commands
\h CREATE ROLE  syntax help for one SQL command
\q              quit psql

Ctrl + D also quits. Ctrl + C cancels the query that is running without leaving psql.

Check it works

• \conninfo shows the database and user you expected.
• \l lists your databases and \dt lists the tables in the current one.
• A test export with \copy creates a CSV file you can open with head.

Troubleshooting

• "Peer authentication failed": you connected over the socket as a Linux user that does not match the role. Use sudo -u postgres psql, or add -h 127.0.0.1 for password login.
• "Did not find any relations" from \dt: you are in the wrong database, or the tables live in another schema. Run \c appdb, then \dt *.*.
• "could not open file... Permission denied" with COPY: server-side COPY writes as the postgres user. Use \copy instead.
• Nothing happens after pressing Enter: the statement has no closing semicolon. The prompt shows appdb-#. Type ; and press Enter.
• "database appuser does not exist": psql defaults to a database with the same name as the role. Pass -d appdb.

Next steps

• Set up roles and databases with these commands: Create PostgreSQL users, roles and databases.
• Protect your data: Back up and restore PostgreSQL with pg_dump.