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. And if you enjoyed this, I also have a newsletter where I send out interesting things I read and the occasional nature photo.