10 psql Commands That Will Make Your Life Easier

James Blackwood-Sewell -

There are a lot of ways to run interactive SQL queries on PostgreSQL, but I still find myself heavily relying on the built-in command line tool psql when I need to hack on some SQL. And apparently, I’m not alone. In the last State of PostgreSQL survey, psql was voted the most popular tool to connect to PostgreSQL.

But psql isn’t a walk in the park. It comes with many built-in meta-commands that can make your life easier, but only if you know about them. A meta-command is anything you run in psql that starts with a backslash (in fact, they are often called slash commands), generally either running a series of SQL commands behind the scenes or changing how psql displays the output.

So sit back, make sure psql is installed, and let’s cover up my top 10 most used meta-commands.

\d Describe Relations

The \d psql meta-command shows a list of the relations (tables, views, or sequences) your current session can see in the database. 

tsdb=> \d
         List of relations
 Schema | Name  | Type  |   Owner   
--------+-------+-------+-----------
 public | power | table | tsdbadmin
 public | small | table | tsdbadmin
(2 rows)

There are actually many more specific variants of this command, with \dt just showing tables, \di showing indexes, \du showing roles, and \dn showing schemas.  For some strange reason, databases is \l (perhaps as \db was already taken for tablespaces).

As an added bonus, you can even up your game with \d+, which will also show you the size of each table (\l+ is also a great command to show the size of each database).

\d table Describe Relation

Using this command will show you detailed information about a single relation (table, view, sequence, or index). This includes columns, primary keys, and indexes for a table.

tsdb=> \d small
                         Table "public.small"
  Column  |           Type           | Collation | Nullable | Default 
----------+--------------------------+-----------+----------+---------
 ts       | timestamp with time zone |           |          | 
 sensorid | integer                  |           |          | 
 value    | double precision         |           |          | 
Indexes:
    "small_sensorid_ts_idx" btree (sensorid, ts DESC)

\e Edit Query Buffer

Every time you run a query in psql, it goes into your query buffer. \e opens up your default editor ($EDITOR in Linux) with the query buffer loaded. You can edit the query, then save and exit to run it.

This is the only way to go when you’re working on long queries!

\ef function Edit function

Similar to the previous slash command, this will open an editor, but it will load the function definition of the function you pass. When you save and exit, the SQL will be run (remember to include CREATE OR REPLACE if you’re looking to update the function).

\x Toggle Expanded Output

This psql meta-command flips query results from displaying columns across the screen to displaying a block for each row with columns vertically laid out.

tsdb=> \x
-- Expanded display is on.
tsdb=> select * from power limit 1;
-[ RECORD 1 ]------------------------
ts     | 2024-04-11 03:53:05.76672+00
stream | 1
v01    | 8043.284272117919
v02    | 6896.529731366893
v03    | 460.279921892548
v04    | 2576.166718397068
v05    | 2377.9182116360053
v06    | 6837.721851388159
v07    | 6029.75078613321
v08    | 4487.823025710251
v09    | 9859.069998493544
v10    | 9022.558524259352

When you’re getting data back from a wide table or a table with a long column (maybe geospatial data, JSON, or text), this is invaluable. 

\timing Toggle Command Timing

When timing mode is enabled, each query will be followed by the amount of time it took to run. 

tsdb=> \timing 
-- Timing is on.
tsdb=> select max(v01) from power;
-[ RECORD 1 ]——
—max | 9999.99927364142

Time: 30.635 ms

This is incredibly handy, but be careful because it includes the network round trip to the PostgreSQL server. This might not matter so much in a local container, but it can be the lion’s share of the time if connecting to a cloud service from your laptop.

\c database Connect to Database

Short of manually disconnecting and reconnecting, this is the only way to change the database you’re attached to from psql.

\copy Perform SQL Copy

PostgreSQL has the COPY command to bulk load or export data, but one caveat is that it runs on the server side, and you often can’t get CSV files onto your database server (especially in the cloud). \copy is an almost complete clone that runs on the client side, so it has access to the files on the machine from which you ran psql.

Again, beware of network trip time. If you’re trying to load a 2 GB CSV file and you’re on a dial-up connection, you’re going to have a bad time.

\i file Read SQL Commands From a File

Use \i if you’ve got some SQL commands (of any type!) you want to run from psql. You can point it at the file rather than copying and pasting. They will be run one after the other; errors will be visible, but it will not stop all commands being run.

\? The PSQL Meta-Command Cheat Sheet!

\? will show you all the meta-commands available, which is very lucky because there are a lot we haven’t mentioned here. Happy hunting!

Next Steps

So here they are, my top 10 psql meta-commands to make your life easier. If you need more quick tips, check our cheat sheet for interacting with your PostgreSQL database.

As I mentioned at the start, you can run interactive SQL queries on PostgreSQL in many ways, ranging from open-source tools like pgAdmin to commercial ones like DataGrip. While I am a heavy user of psql, Timescale’s recent PopSQL acquisition left me and many of my teammates ecstatic because we already used it internally. IMO, PopSQL is the best graphical tool out there, and the only one I know of that has built-in team collaboration on SQL queries. 

Try these tips out on PopSQL. Start with a free trial.