PostgreSQL CLI Cheat Sheet
Also see PostgreSQL Cheat Sheet.
Backslash Commands 🔗
\?— Show all the commands
\copy— Similar to SQL copy command but uses local files instead of paths on the server.
\e— Open an editor to write a query. You can append this to the end of a line (without semicolon, see
\;below) to edit the current query.
\gdesc— Show the format of the current query's result, without actually executing it.
\ef— Edit a function
\ev— Edit a view
\timing— Show how long each command takes to run
\i FILE— Execute commands in a file
\df— Function detail
\dT— Type detail
\;— Ends a query like a normal semicolon, but psql will not submit the query until it sees an unescaped semicolon. The queries will be executed as a single transaction.
PSQL Variables 🔗
Set these with
\set VAR VALUE.
- AUTOCOMMIT — Set
on(the default) to commit changes after each statement. When
offor unset, each statement not already in a transaction is implicitly prepended with a
BEGINstatement and then you must
ENDto commit the changes.
- ECHO_HIDDEN — Set to
onto echo all queries generated by backslash commands. This can also be done with the
-Ecommand line flag.
Connection Services 🔗
Adding the file
~/pg_service.conf lets you define connection string presets.
[YOUR_SERVICE_NAME] host=HOST port=PORT dbname=DBNAME user=USER
psql service=YOUR_SERVICE_NAME to connect to it.
You can add the file
~/.pgpass to supply the password. Each line has the format
HOST:PORT:DBNAME:USER:PASSWORD. Any of these fields can be
* to use them as a wildcard.
The obvious caveats about writing your passwords in a plaintext file apply, and as with SSH private keys, the files must be accessible only by your user (i.e.
chmod 600 ~/.pgpass).
Thanks for reading! If you have any questions or comments, please send me a note on Twitter.