I use psql on a daily basis for quick queries, reminding myself of a specific table structure, or analyzing performance, and a dozen other things. These are a few of my psql commands to make the experience more powerful, more legible, or get to what I need faster.


\? help with psql commands
myawesomedb=# \?

First, the shortcut to psql help. This will list all of the commands available. I try to pick out one or two useful ones and focus on applying them for a week or two. This helps me remember them and gradually plug them into my workflows.


\x [on|off|auto] toggle expanded output (currently off)
myawesomedb=# \x

Probably my most used command, expanded output lists columns in rows, instead of typical side by side column output. For tables with a large volume of columns, this can make a result set far more legible.


\timing [on|off] toggle timing of commands (currently off)
myawesomedb=# \timing

Especially handy when benchmarking a query, turning on timing will output the runtime of every command run in your psql console. I use this most when I’m toggling indices, or experimenting with fine-tuning (vacuuming, caching, or otherwise).


\c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently "myawesomedb")
myawesomedb=# \c laurens-other-db

Connect to another database. Useful for flipping back and forth between a test database and the development database, for instance. Also saves the extra step of quitting the psql console, and restarting a console session with a new database name.


\l[+] [PATTERN] list databases
\dn[S+] [PATTERN] list schemas
\dt[S+] [PATTERN] list tables
myawesomedb=# \l+
myawesomedb=# \dn+
myawesomedb=# \dt+

List all the things, but also include their size! Note that this is a representation of how much space the table or database is currently taking up, which may be inflated if your database is in need of a vacuum.

There are queries that will use postgres internal tables to report on relation sizes as well, but none are quite so neat as a adding a ‘+’ to any psql list command. In general, ‘+’ adds ‘additional details.’ For databases, the ‘+’ will add size, tablespace, and description. For tables, size and description will be added. And for schemas, access privileges and description are added. The ‘+’ results, though variable by command, are usually a helpful starting point before googling for a specific postgres internals query.

The ‘+’ also generally includes a description column. Numerous things within postgres can be commented upon (see comment documentation for the full list). The comments serve to document the database in place. However, it’s been my experience since these columns aren’t easily visible, they’re rarely checked. Consequently, they usually fall out of sync with the reality of the data underneath.


\q quit psql
myawesomedb=# \q

And that’s it! Enjoy!