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.
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)
\eEdit 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.
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!
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.
How OpenSauced Is Building a Copilot for Git History With pgvector and Timescale
