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