psql is the venerable command-line interactive tool that comes bundled with PostgreSQL, that lets you connect to Postgres servers and run SQL queries and more.

Read on for some tips and tricks to make your psql sessions a bit more productive.

You can ask psql to print the time taken to execute every command or query, using \timing.

Print Query Timing

Represent NULL Visibly

By default, it is impossible to distinguish between a NULL and an empty string in psql’s output. Use the \pset null str to use “str” wherever a NULL is shown.

Show NULL Visibly

Repeatedly Execute a Query

You can repeatedly execute a query every N seconds, using the \watch N command. It will execute the last run query repeatedly.

Execute Query Repeatedly

Funky Tables

Bored with plain old tables in psql query results? Try this:

Funky Tables

Save Query Results as CSV

Save the results of any query as a CSV to a local file with the \copy command.

Save Query Results as CSV

Built-in Syntax Reference

psql comes with a built in quick syntax reference for all SQL commands. Use the \h command to access it. To see the syntax for say “create table”, use \h create table.

Built-in Syntax Reference

Edit Command in Editor

Use the \e command to edit the last command you executed in your $EDITOR. You can edit the command, save it, and exit the editor to have psql execute the command.

Edit Last Command in Editor

And More..

  • Readline Support: Use Ctrl+R to recall commands, up/down arrows to scroll through history and Tab for completion of keywords, just like in bash.
  • Command History: View the command history with the \s command. You can also save the entire list to a file with \s path/to/file.
  • Save Your Customizations: You can see your funky borders everytime, by creating a file called .psqlrc in your $HOME. psql will read the contents of this file and execute them as psql commands before presenting the interactive prompt.
  • Internal Queries: psql internally runs SQL queries to fetch the output shown in \dg etc. To see these queries, use \set ECHO_HIDDEN.
  • Change Password: Change your PostgreSQL user password using the \password command. If you’re a superuser, change any user’s password with \password username.

About pgDash

pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics.

Monitoring with pgDash

pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, alerting, teams and more.