Get Started With the Postgres Command Line psql

Getting familiar with psql is an essential right of passage for any Postgres fan. psql is the Postgres command line, and it ships with every Postgres install. If you’re using a cloud-managed Postgres, we recommend installing a local version of Postgres too, in order to get psql.

Though psql runs SQL, it isn’t just for writing queries. Once you’re connected, you can run any valid SQL against the database select * from accounts;. psql also equips users with handy meta commands to interact with Postgres, making psql a Postgres superpower and ensuring that users don't need to rely on a user interface for relatively simple tasks.

Here’s a quick review of the most essential commands and some of my favorites.

psql basics: Getting in and out

Before you can query, you have to connect. If you are on a standard local setup, you might type simply psql, or psql postgres can connect you to your local instance. Once you are inside the prompt, which looks like postgres=#, you are ready to go.

For connecting to a remote machine, you’ll want a longer connection string, which looks something like:

Further commands include:

• \q to quit the entire session

• Ctrl+C to cancel the current input and reset things

• \? for help

Terminal pagers

Most terminals have a built-in pager, and psql works with your terminal to display the output according to those rules. On a Mac this pager is usually less, so, for instance, it may look like:  

• G to reach the end of the results

• /search_term lets you search in a results window for a case-sensitive search term

• q takes you back to the psql prompt

\pset pager off turns the pager off. 

Navigating inside a Postgres instance

Once you are logged in, you need to see the databases and move between them. This is really the bread and butter of psql.

• \l lists the databases on the server

• \c database_name connects to a different database, while \c - username connects to the current database with a different name

• \dx lists installed extensions

• \dn lists the schemas

• \du describes roles, users and privileges

psql data inspection

By using psql, you can inspect your schema much faster than by clicking through a GUI tree:

• \d shows objects in the database. By itself, \d lists all relations (tables, views and sequences) in the current database.

• \d table_name describes a table. This is the command you will use the most. It shows the columns, data types, modifiers (such as NOT NULL) and indexes of a specific table.

psql formatting

Out of the box, psql formatting is horizontal and tabular. For wide or long responses, it can be a bit messy because results with columns wider than the screen will wrap. 

\x auto automatically toggles to a vertical instead of tabular display when the columns are too wide. You can manually switch between \x off and \x on to decide whether you want the expanded view on or off, though I never do that; it's \x auto all the time for me. 

This method is almost essential for reading tables, because in most cases Postgres data does not wrap nicely. 

For example, here is a normal tabular view:

And here is an expanded view (\x on):

You can also give Postgres special instructions for linestyle to make boxes around records for formatting, such as \pset linestyle unicode.

Connecting psql to your code editor 

There are several commands that can be used with psql to connect it to your code editor.

\e opens a temporary file to edit in your preferred text editor, such as vi, vim or VS Code. It also populates the file with the last query you ran or whatever you have currently typed. You write/edit your SQL in the editor and save and close the file. On a Mac with VS Code, this is Cmd+S, and I close the tab with Cmd+W. psql loads that text back into the terminal immediately and runs the query.

• \e path/to/file.sql adds a file path after \e, which opens that file and then, upon saving and closing, runs the sql. 

• \i path/to/file.sql executes the SQL commands inside a specific file but, unlike \e,  \i does not open the file beforehand like \e.

Other psql favorites 

Several other psql commands can be very useful and here are a couple that I use all the time. 

• \timing adds a timer that tells you exactly how long every query takes to execute, including round-trip network time

• --csv outputs to CSV either as basic CSV to the terminal or to a local file if you provide a file name

• \set ECHO_HIDDEN shows the SQL underneath the \ meta commands

• \pset null '☘️' sets NULL values in the terminal to any word or emoji

• \set HISTFILE ~/.config/psql/psql_history-:DBNAME creates a history file for each database in your config directory 

In addition, advanced folks love to get psql to start with their presets by altering the psqlrc file for a custom experience.

Summary of commands

Conclusion

The psql command line client might look like “just another CLI” but it is essential for daily work with Postgres. It keeps your hands on the keyboard and helps you understand exactly what is happening in your database without the abstraction of a user interface. And once you get the hang of using psql, you’ll realize you can use it for complex tasks as well.