Did you know that despite all the fancy GUI tools available, the humble command-line psql remains the most popular way to interact with PostgreSQL databases?
But let's be honest - psql has a bit of a learning curve. The real magic lies in its meta-commands (or slash commands), which start with a backslash (\). These commands can dramatically improve your workflow, but only if you know they exist!
Let me walk you through my top 10 most-used psql meta-commands that will make your database interactions much smoother.
π Jump to the full article
π§ Install psql on Mac, Ubuntu, Debian, Windows
1. \d - Describe Relations
This command shows you all the relations (tables, views, sequences) your current session can access:
tsdb=> \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+-----------
public | power | table | tsdbadmin
public | small | table | tsdbadmin
(2 rows)
π₯ Pro tip: There are many variants of this command:
As an added bonus, try \d+ to also see the size of each table, or \l+ for database sizes.
2. \d table - Describe a Specific Relation
Need the details of a specific table? Use \d followed by the table name:
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)
This gives you columns, data types, constraints, and indexes all in one view.
3. \e - Edit Query Buffer
Working on a complex query? Every time you run a query in psql, it goes into your query buffer. The \e command opens your default editor with that buffer loaded so you can edit it properly.
This is a lifesaver for complex queries where one-line edits in the terminal become tedious!
4. \ef function - Edit Function
Similar to \e, but this opens your editor with an existing function definition loaded. When you save and exit, it runs the SQL automatically.
Remember to include CREATE OR REPLACE if you're updating an existing function!
5. \x - Toggle Expanded Output
This command changes how results are displayed. Instead of columns stretching across your screen, each row becomes a vertical block:
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
...
This is invaluable when dealing with wide tables or columns containing JSON, geospatial data, or long text.
6. \timing - Toggle Command Timing
Enable timing mode to see how long each query takes:
tsdb=> \timing
-- Timing is on.
tsdb=> select max(v01) from power;
-[ RECORD 1 ]ββ
βmax | 9999.99927364142
Time: 30.635 ms
Just be aware that this includes network round-trip time, which can be significant when connecting to remote databases.
7. \c database - Connect to Database
Need to switch databases without disconnecting? Use \c followed by the database name:
tsdb=> \c another_database
You are now connected to database "another_database" as user "tsdbadmin".
8. \copy - Perform SQL Copy
PostgreSQL's COPY command is great for bulk loading data, but it runs server-side. The \copy meta-command performs the same function but runs client-side, meaning it can access files on your local machine:
tsdb=> \copy my_table FROM '/path/to/local/file.csv' WITH CSV HEADER;
This is especially useful when working with cloud databases where you can't easily upload files to the server.
9. \i file - Read SQL from File
Have a SQL script you want to run? Use \i to execute commands from a file:
tsdb=> \i ./my_script.sql
All commands will run in sequence, and errors will be visible but won't stop execution.
10. \? - The PSQL Meta-Command Cheat Sheet
Forgot how a meta-command works? Type \? to see all available commands:
tsdb=> \?
Wrapping Up
While there are many great GUI tools out there (like pgAdmin, DataGrip, or PopSQL), mastering psql meta-commands can make you significantly more productive when working with PostgreSQL.
If you need more quick tips, check our cheat sheet for interacting with your PostgreSQL database.
What are your favorite psql tricks? Share them in the comments below!
Want more PostgreSQL tips? Follow for more database insights and practical guidesβsave for later!
More...
But let's be honest - psql has a bit of a learning curve. The real magic lies in its meta-commands (or slash commands), which start with a backslash (\). These commands can dramatically improve your workflow, but only if you know they exist!
Let me walk you through my top 10 most-used psql meta-commands that will make your database interactions much smoother.
π Jump to the full article
π§ Install psql on Mac, Ubuntu, Debian, Windows
1. \d - Describe Relations
This command shows you all the relations (tables, views, sequences) your current session can access:
tsdb=> \d
List of relations
Schema | Name | Type | Owner
--------+-------+-------+-----------
public | power | table | tsdbadmin
public | small | table | tsdbadmin
(2 rows)
π₯ Pro tip: There are many variants of this command:
- \dt - Shows only tables
- \di - Shows only indexes
- \du - Shows roles
- \dn - Shows schemas
- \l - Shows databases (not \db as you might expect!)
As an added bonus, try \d+ to also see the size of each table, or \l+ for database sizes.
2. \d table - Describe a Specific Relation
Need the details of a specific table? Use \d followed by the table name:
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)
This gives you columns, data types, constraints, and indexes all in one view.
3. \e - Edit Query Buffer
Working on a complex query? Every time you run a query in psql, it goes into your query buffer. The \e command opens your default editor with that buffer loaded so you can edit it properly.
This is a lifesaver for complex queries where one-line edits in the terminal become tedious!
4. \ef function - Edit Function
Similar to \e, but this opens your editor with an existing function definition loaded. When you save and exit, it runs the SQL automatically.
Remember to include CREATE OR REPLACE if you're updating an existing function!
5. \x - Toggle Expanded Output
This command changes how results are displayed. Instead of columns stretching across your screen, each row becomes a vertical block:
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
...
This is invaluable when dealing with wide tables or columns containing JSON, geospatial data, or long text.
6. \timing - Toggle Command Timing
Enable timing mode to see how long each query takes:
tsdb=> \timing
-- Timing is on.
tsdb=> select max(v01) from power;
-[ RECORD 1 ]ββ
βmax | 9999.99927364142
Time: 30.635 ms
Just be aware that this includes network round-trip time, which can be significant when connecting to remote databases.
7. \c database - Connect to Database
Need to switch databases without disconnecting? Use \c followed by the database name:
tsdb=> \c another_database
You are now connected to database "another_database" as user "tsdbadmin".
8. \copy - Perform SQL Copy
PostgreSQL's COPY command is great for bulk loading data, but it runs server-side. The \copy meta-command performs the same function but runs client-side, meaning it can access files on your local machine:
tsdb=> \copy my_table FROM '/path/to/local/file.csv' WITH CSV HEADER;
This is especially useful when working with cloud databases where you can't easily upload files to the server.
9. \i file - Read SQL from File
Have a SQL script you want to run? Use \i to execute commands from a file:
tsdb=> \i ./my_script.sql
All commands will run in sequence, and errors will be visible but won't stop execution.
10. \? - The PSQL Meta-Command Cheat Sheet
Forgot how a meta-command works? Type \? to see all available commands:
tsdb=> \?
Wrapping Up
While there are many great GUI tools out there (like pgAdmin, DataGrip, or PopSQL), mastering psql meta-commands can make you significantly more productive when working with PostgreSQL.
If you need more quick tips, check our cheat sheet for interacting with your PostgreSQL database.
What are your favorite psql tricks? Share them in the comments below!
Want more PostgreSQL tips? Follow for more database insights and practical guidesβsave for later!
More...