Essential psql Commands for PostgreSQL

psql is the interactive terminal client for PostgreSQL. It lets you connect to databases, run SQL queries, and use powerful meta commands for database management.

---

Connecting to PostgreSQL

(Note : here the user is named as “potatoes“, and DB as “mypotatoesdb“)

• Default connection (current OS user, default database):

    psql
  

• Connect as user potatoes:

    psql -U potatoes
  

• Connect to a specific database:

    psql -U potatoess -d mypotatoesdb
  

• Connect using host and port explicitly:

    psql -h localhost -p 5432 -U potatoes -d mypotatoesdb
  

• Force password prompt:

    psql -U potatoes -W
  

---

Run Commands Without Entering psql

• Run a single SQL command and exit:

    psql -U potatoes -d mypotatoesdb -c "SELECT version();"
  

• Execute SQL from a file (schemas, migrations, data loads):

    psql -U potatoes -d mypotatoesdb -f script.sql
  

---

Inside psql (Meta Commands)

Meta commands start with \ and do not require a semicolon.

Databases

• \l → List databases

• \c mydb → Connect to another database

• \conninfo → Show current connection details

Tables & Schemas

• \dt → List tables in the current schema

• \dt * → List tables in all schemas

• \d table_name → Describe table structure

• \dn → List schemas

Users & Roles

• \du → List users/roles

• \du+ → Show roles with privileges

Query Utilities

• \x → Toggle expanded (vertical) output

• \timing → Show query execution time

• \watch 2 → Repeat last query every 2 seconds

Help & Exit

• \? → Show psql command help

• \h → SQL help

• \h SELECT → Help for a specific SQL command

• \q → Quit psql

---

[Rules to Remember]

• SQL commands end with ;

• psql meta commands start with \

• Default PostgreSQL port is 5432

• psql is a client, not the database itself

---

The One Command You’ll Use Daily

psql -U postgres -d mypotatoesdb

This is the most common way to connect to your database for everyday work.

---

Final Thoughts:

With these commands, you can expect better productive in PostgreSQL . Whether you’re managing schemas, running migrations, or exploring data, psql is your go‑to tool.