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.