Become poweruser with psql
I think we all love Postgres. It's fast, efficient, and free to use. Nowadays, when I'm thinking of creating a SQL database, I choose Postgres as my primary target.
For local development, I pretty much always deploy it in a Docker container, but the question is—how do you access it? Some people use database management interfaces built into IDEs, some use dedicated tools (such as pgAdmin or DBeaver), but for my basic development needs, I stick with psql (and it's in the terminal!).
In this article, I'll show you the common useful things you can do with it.
Prerequisites:
- Basic SQL understanding
- Terminal
This article was not written by AI.
AI was however used in generating a script for a mock database and for spotting typos. You can see the script on GitHub.
Models: Gemini 3 Flash and Claude Haiku 4.5
Tool: Opencode v1.1.35
Setup
Imagine we deploy our Postgres in Docker using Docker Compose, perhaps you'll do it roughly like this:
my-db:
image: postgres:17.5
container_name: my-db
environment:
- POSTGRES_USER=${POSTGRES__DB_USER}
- POSTGRES_PASSWORD=${POSTGRES__DB_PASSWORD}
- POSTGRES_DB=${POSTGRES__DB}
ports:
- "5435:5432"
volumes:
- pgdata:/var/lib/postgresql/data
healthcheck:
test: [ "CMD-SHELL", "pg_isready -d ${POSTGRES__DB} -U ${POSTGRES__DB_USER}" ]
interval: 1s
timeout: 5s
retries: 10
I like to include the healthcheck key because it can be leveraged in the depends_on clause for my web service.
Naturally, I'm going to be particularly original about naming and name my variables like so:
This healthcheck checks if the database server accepts connections only. It does not check if the database exists. For purposes of a web server, this is fine, but perhaps you might want to avoid it and make it see if the database exists in the first place!
POSTGRES__DB_USER=postgres
POSTGRES__DB_PASSWORD=postgres
POSTGRES__DB=mydb
We bring it up with $ docker compose up and voilà! Our database is alive:
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
10b2f0530af0 postgres:17.5 "docker-entrypoint.s…" 20 seconds ago Up 19 seconds (healthy) 0.0.0.0:5435->5432/tcp, [::]:5434->5432/tcp my-db
The database mydb will only be created if the directory for data (that is pgdata:/var/lib/postgresql/data) is empty. In other words, during the first run and not afterwards!
Now, to get our psql running, we can connect to the Docker container like this:
$ docker exec -it my-db psql -U postgres -d mydb
psql (17.5 (Debian 17.5-1.pgdg130+1))
Type "help" for help.
mydb=#
And we are in!
psql and what can we do with it
So what is psql? As the docs define it, it is a frontend terminal-based application for Postgres. It enables you to query the database and also has shell-like behavior you would expect from a normal shell. It has some meta commands on top of it, some of which I'll showcase today!
Let's list available databases for us by running:
mydb=# \l+
List of databa
ses
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |
Access privileges | Size | Tablespace | Description
-----------+----------+----------+-----------------+------------+------------+--------+-----------+-
----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
| 7507 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
=c/postgres +| 7353 kB | pg_default | unmodifiable empty database
| | | | | | | |
postgres=CTc/postgres | | |
template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
=c/postgres +| 7579 kB | pg_default | default template for new databases
| | | | | | | |
postgres=CTc/postgres | | |
mydb | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
| 7795 kB | pg_default |
(4 rows)
(END)
Hey!! That looks terrible! The characters are everywhere and lines don't match. It's unreadable!!
That's because your terminal is too small to show the entire table! You can use \x auto for Expanded auto mode which switches intelligently between horizontal and vertical showing of table, such that it fits in a given terminal.
Let's do just that!
mydb=# \x auto
Expanded display is used automatically.
mydb=# \l+
List of databases
-[ RECORD 1 ]-----+-------------------------------------------
Name | postgres
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | en_US.utf8
Ctype | en_US.utf8
Locale |
ICU Rules |
Access privileges |
Size | 7507 kB
Tablespace | pg_default
Description | default administrative connection database
-[ RECORD 2 ]-----+-------------------------------------------
Name | template0
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | en_US.utf8
Ctype | en_US.utf8
Locale |
ICU Rules |
Access privileges | =c/postgres +
| postgres=CTc/postgres
Size | 7353 kB
Tablespace | pg_default
Description | unmodifiable empty database
-[ RECORD 3 ]-----+-------------------------------------------
Name | template1
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | en_US.utf8
Ctype | en_US.utf8
Locale |
ICU Rules |
Access privileges | =c/postgres +
| postgres=CTc/postgres
Size | 7579 kB
Tablespace | pg_default
Description | default template for new databases
-[ RECORD 4 ]-----+-------------------------------------------
Name | mydb
Owner | postgres
Encoding | UTF8
Locale Provider | libc
Collate | en_US.utf8
Ctype | en_US.utf8
Locale |
ICU Rules |
Access privileges |
Size | 7795 kB
Tablespace | pg_default
Description |
Nice! It fits! And we've learned here two meta commands:
\x [ on | off | auto ]- (or\pset expanded) which outputs stuff in expanded mode. You can also have it be supplied automatically when invokingpsqlitself by adding-xto it.\lor\list[ pattern ]- lists your databases or a specific database that matches[ pattern ]. e.g.\l postgres
But what is that + I added to \l? It displays extra information about database objects. You can think of it as a verbose flag. For \l, it adds Size, Tablespace, and Description columns. More often than not, I'm surprised how little space my databases are taking thanks to the Size column ;).
With that, let's keep going!
\cor\connect [ -reuse-previous=on|off ] [ dbname [ username ] [ host ] [ port ] | conninfo ]- allows you to connect to a different database. As you can see from the arguments, you can specify a different username and host as well, though I've never used it this way. I only use it to swap to another database listed from\lby doing\c postgres.
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# \c postgres
You are now connected to database "postgres" as user "postgres".
Describe
Describe or display is a meta command \d which helps you display database objects.
It's quite comprehensive—it allows you to display everything: sequences, indexes, tables, views, and more.
The docs themselves are pretty readable, though they lack clear examples. That's where this article will help you.
On our mock database, let's see what's in here:
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------------------+----------+----------
public | posts | table | postgres
public | posts_post_id_seq | sequence | postgres
public | user_profiles | table | postgres
public | user_profiles_profile_id_seq | sequence | postgres
public | users | table | postgres
public | users_user_id_seq | sequence | postgres
What about list of just tables?
mydb=# \dt+
List of relations
-[ RECORD 1 ]-+--------------
Schema | public
Name | posts
Type | table
Owner | postgres
Persistence | permanent
Access method | heap
Size | 16 kB
Description |
-[ RECORD 2 ]-+--------------
Schema | public
Name | user_profiles
Type | table
Owner | postgres
Persistence | permanent
Access method | heap
Size | 16 kB
Description |
-[ RECORD 3 ]-+--------------
Schema | public
Name | users
Type | table
Owner | postgres
Persistence | permanent
Access method | heap
Size | 16 kB
Description |
So we have 3 tables, each of which has 16 kB and is stored on heap. Do we have any custom data types?
mydb=# \dT
List of data types
Schema | Name | Description
--------+-----------------+-------------
public | membership_tier |
(1 row)
That... is not that useful.. Just the name of the data type?
You forgot the + for detailed information!
mydb=# \dT+
List of data types
-[ RECORD 1 ]-----+----------------
Schema | public
Name | membership_tier
Internal name | membership_tier
Size | 4
Elements | Basic +
| Premium +
| Elite
Owner | postgres
Access privileges |
Description |
So we have an Enum with 3 elements. But what about the tables themselves? What's on them?
Let's examine users table.
mydb=# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+----------------------------------------
user_id | integer | | not null | nextval('users_user_id_seq'::regclass)
username | text | | not null |
email | text | | not null |
tier | membership_tier | | | 'Basic'::membership_tier
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_username_key" UNIQUE CONSTRAINT, btree (username)
Referenced by:
TABLE "posts" CONSTRAINT "posts_author_id_fkey" FOREIGN KEY (author_id) REFERENCES users(user_id)
TABLE "user_profiles" CONSTRAINT "user_profiles_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
Oh, nice! We actually learned quite a bit. Our table has 5 columns. It uses 2 indexes, one of which must be unique. It's referenced by two tables: posts and user_profiles. Feel free to experiment with + yourself ;).
Let's summarise what we've used:
\dlets us see all entities within the database—sequences, views, tables, materialized views, and so on.\dtlists all the tables only.\dTlists all the data types. For me, this has been extremely handy for ENUMs.\d [table_name]allows me to get a detailed description of a specified entity. For tables, this includes columns, indexes, and references. Try it yourself for sequences and views!\dflists all the functions. (And a corresponding\sf [function_name]for the code of a function)\dslists all the sequences.\dvlists all the views. (And a corresponding\sv [view]for the code of a view)
With multiple references and multiple tables, reading the output will be much harder than seeing an Entity Relationship diagram. Unfortunately, this is a limit of the psql utility, as I was unable to find a way to create one. For that, your best bet is to use something like pgAdmin or DBeaver to see the entire diagram. Fortunately, this is rarely needed for my workflow.
But what exactly is meta command?
Okay, we've seen some pretty cool examples. But what are those? Programs? Scripts? And why bother using them myself if I can always query it directly using postgres native functions such as pg_database_size(oid)?
Those are good questions and show curiosity—a valuable trait for an engineer!
You can run psql with the -E flag, which lets you do internal query peeking. From that point on, if you run a meta command, you'll see the corresponding SQL query executed to fetch the data—thus answering all your questions from above:
mydb=# \dt
/******** QUERY *********/
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND n.nspname <> 'information_schema'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
/************************/
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | posts | table | postgres
public | user_profiles | table | postgres
public | users | table | postgres
(3 rows)
It's quite verbose though, so you might want to skip it. Nevertheless, it's extremely valuable for learning how the internals work.
Becoming a psql power user
By now, you're hopefully hooked on psql. But let me showcase more cool stuff:
mydb=# SELECT * FROM users;
mydb=# \watch 2
Thu 12 Feb 2026 01:29:25 PM UTC (every 2s)
user_id | username | email | tier | created_at
---------+------------+-------------------+---------+-------------------------------
1 | tech_guru | guru@example.com | Elite | 2026-02-12 12:50:53.850788+00
2 | sql_newbie | start@example.com | Basic | 2026-02-12 12:50:53.850788+00
3 | data_viz | chart@example.com | Premium | 2026-02-12 12:50:53.850788+00
(4 rows)
Thu 12 Feb 2026 01:29:27 PM UTC (every 2s)
...
\watch lets you rerun your previous query every specified interval—in this case, 2 seconds.
mydb=# \timing
Timing is on.
mydb=# select * from users;
...
(3 rows)
Time: 0.296 ms
\timing lets you quickly see the execution time of a query. Please don't substitute it for proper query plan analysis; just add it as another tool. See EXPLAIN
\! [command] lets you run shell commands without leaving psql.
mydb=# \! ls
bin dev etc lib media opt root sbin sys usr
boot docker-entrypoint-initdb.d home lib64 mnt proc run srv tmp var
\p prints the current query buffer. Essentially "where am i?". If it's empty though, this will show last executed query.
mydb=# is this my buffer?
mydb-# hello?
mydb-# ah i haven't finished with ';' yet
mydb-# \p
is this my buffer?
hello?
ah i haven't finished with ';' yet
mydb-# ;
ERROR: syntax error at or near "is"
LINE 1: is this my buffer?
^
Time: 0.261 ms
mydb=#
Arguably, when in doubt, consult the docs — or stay in the terminal and consult them another way!
\helpgives you a rough summary of the docs for given SQL syntax. Try it with\help EXPLAIN!\?gives you information about psql itself and all the meta commands it supports.
Now for the hidden gem of psql. The cherry on top. The Pièce de résistance of meta commands.
Behold \gdesc! What does it do? Imagine a long and complex query (my example won't be like that, but just use your imagination!):
SELECT
u.username,
u.tier,
p.title,
(u.created_at::date) AS member_since,
(p.view_count > 1000) AS is_popular
FROM users u
LEFT JOIN posts p ON u.user_id = p.author_id;
What will the result be? What data type is in each column? What if your query is thousands of lines long, does regex filtering, switch case filtering, and is partially building HTML through multiple window functions, joins, and a unholy table structure — and all you need is a simple god damn foreign key ID? What then?
This is unfortunately a real story...
\gdesc to the rescue! It tells you the data types of the query output without executing it—including aliases!
mydb=# \gdesc
Column | Type
--------------+-----------------
username | text
tier | membership_tier
title | text
member_since | date
is_popular | boolean
You are now a psql power user! Spread the word!
Styling
And since I like my terminal to look nice, you can also style your psql. As with everything, there are many options. Here are just a few:
\pset border [x] - changes the style of a border around tables:
mydb=# \pset border 2
Border style is 2.
mydb=# select * from users;
+---------+------------+-------------------+---------+-------------------------------+
| user_id | username | email | tier | created_at |
+---------+------------+-------------------+---------+-------------------------------+
| 1 | tech_guru | guru@example.com | Elite | 2026-02-12 12:50:53.850788+00 |
| 2 | sql_newbie | start@example.com | Basic | 2026-02-12 12:50:53.850788+00 |
| 3 | data_viz | chart@example.com | Premium | 2026-02-12 12:50:53.850788+00 |
| 4 | tech_guru1 | guru1@example.com | Elite | 2026-02-12 13:28:51.802194+00 |
+---------+------------+-------------------+---------+-------------------------------+
By default, psql uses ASCII characters in its output, but you can also instruct it to use Unicode characters:
\pset linestyle unicode
And now:
mydb=# \pset linestyle unicode
Line style is unicode.
mydb=# select * from users;
┌─────────┬────────────┬───────────────────┬─────────┬───────────────────────────────┐
│ user_id │ username │ email │ tier │ created_at │
├─────────┼────────────┼───────────────────┼─────────┼───────────────────────────────┤
│ 1 │ tech_guru │ guru@example.com │ Elite │ 2026-02-12 12:50:53.850788+00 │
│ 2 │ sql_newbie │ start@example.com │ Basic │ 2026-02-12 12:50:53.850788+00 │
│ 3 │ data_viz │ chart@example.com │ Premium │ 2026-02-12 12:50:53.850788+00 │
│ 4 │ tech_guru1 │ guru1@example.com │ Elite │ 2026-02-12 13:28:51.802194+00 │
└─────────┴────────────┴───────────────────┴─────────┴───────────────────────────────┘
(4 rows)
You can even change the prompt itself using \set PROMPT1. In this case, let's set it to:
mydb=# \set PROMPT1 '(%n@%M:%>) %`date +%H:%M:%S` [%/] \n%x%# '
-- That's my new prompt underneath..
(postgres@[local]:5432) 13:57:28 [mydb]
#
This prompt shows time, user, database, and host information. Pretty neat. At this point, you're thinking to yourself, "Okay, that's a bit much for a terminal-based utility." Oh, my sweet summer child, I've barely begun to scratch the surface. Here's code that makes your shell colored: rin-nas/postgresql-patterns-library enjoy!
.psqlrc
By now, I've shown you a lot of commands. Some of these are sane defaults for every psql invocation (such as \x or \timing). But writing them every time you open psql will be tedious at best and unproductive at worst. So what can you do about it?
Introducing the .psqlrc file. Just like .zshrc and .bashrc, it lets you specify your configuration beforehand to be loaded. There are several ways to create and store it, so I won't go over all of them, but you can create one at $HOME/.psqlrc. Alternatively, you can specify the PSQLRC environment variable with a path to a config file.
So far however, we've been running our psql inside the docker, and for that you have two options. Both of these require you to have your .psqlrc reachable from within your project folder.
- You can extend baseline Dockerfile for postgres and copy the file there. Personally I hate that.
- You can mount the file through docker compose:
volumes: ./.psqlrc:/var/lib/postgresql/.psqlrc.
In either case, this is what sample .psqlrc file could look like:
\pset border 2
\pset linestyle unicode
\timing on
\x auto
\set PROMPT1 '%n@%m %~%# '
Storing Queries
Some of you may notice that writing queries in a shell isn't exactly ideal. And that's true. That's why I usually have a database/ directory (added to .git/info/exclude) which stores all the queries I write for a given project. It acts as a scratch pad for me—if I need to run a query that's more complex than just fetching all the columns, I add it there with a small note on what it does. That way, if I need it again, I can always come back to it.
It's quite handy and allows me to use my own editor for writing queries, thus sidestepping the limitations of a shell for query storage and writing.
Restoring DB from dump
This isn't Postgres or psql specific, but I like to always have a quick way to restore my database from a given *.sql database dump. I have a separate script for that, and it looks roughly like this:
#!/usr/bin/sh
DUMP_FILE=$1
docker exec -i my-db psql -U ${POSTGRES__DB_USER} -c "CREATE DATABASE ${POSTGRES__DB};"
# NOTE: This is where you would create additional roles if they were needed.
cat "$DUMP_FILE" | docker exec -i my-db psql -U ${POSTGRES__DB_USER} -d ${POSTGRES__DB}
This runs when all the given .env variables are already exported, so all I need to do is:
# Just make sure it's executable first
$ chmod +x restore.sh
$ ./restore.sh ./01-12-1887db.sql
Conclusion
It goes without saying that this is just a fraction of what's available for psql. For more details (and fun!), look no further than the PostgreSQL docs. It's a fantastic resource and a crown jewel in the beauty that is Postgres itself. In my experience, psql isn't favored by developers in favor of built-in database tools inside their IDEs or heavier dedicated tools like DBeaver. They're good. They do the job. But they're also heavier. Are they always needed? I've found that I rarely need to bring out the big guns and I'm more than capable of writing a few simple commands by hand. Especially when meta commands are shortcuts to most of what I need. Hopefully, I've convinced you to at least try them at some point.