PostgreSQL CLI Cheat Sheet

Written β€” Updated

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 off or unset, each statement not already in a transaction is implicitly prepended with a BEGIN statement and then you must COMMIT or END to commit the changes.
  • ECHO_HIDDEN – Set to on to echo all queries generated by backslash commands. This can also be done with the -E command 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

Run psql service=YOUR_SERVICE_NAME to connect to it.

Full Documentation

Passwords πŸ”—

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).

Full documentation


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.

You can check out a recent issue, or enter your email below to subscribe.