psql Basics — The PostgreSQL Terminal

Prerequisites: PostgreSQL 16 installed locally (any recent version works — psql has been stable for decades). We'll practice against the same tiny web-shop database used across this whole PostgreSQL series, so what you learn here carries straight into Create Database & Tables and SELECT Queries.

1Why Learn psql When GUIs Exist?

Fair question. pgAdmin, DBeaver, DataGrip, TablePlus — they're all good tools, and I use them too. So why bother with a terminal client from the 90s? Three reasons na natutunan ko the hard way:

• It's always there. psql ships with every PostgreSQL installation. Docker container? It's there. Bare Ubuntu server with no desktop? There. Emergency SSH session into a box you've never touched? Still there. Your GUI is not.
• It's scriptable. Anything you type interactively can become a shell script, a cron job, or a CI step. psql -f migrate.sql in a deploy pipeline is a real production pattern; "click the run button in pgAdmin" is not.
• It's faster. Once your fingers know \dt and \d customers, you'll answer "what columns does this table have?" before a GUI finishes loading its sidebar tree.

Ang deal natin: 10 minutes here, and the terminal stops being scary. Tara na.

2Connecting: Flags, URIs, and Passwords

psql --version

psql (PostgreSQL) 16.3

The four flags you'll use daily: -U (user), -d (database), -h (host), and -p (port). Locally, host and port usually have sane defaults, so this is the typical form:

psql -U postgres -d postgres

psql (16.3)
Type "help" for help.

postgres=#

Connecting to a remote server just means spelling everything out:

psql -h db.example.com -p 5432 -U app_user -d shop

Everything above can be packed into a single URI — the same format your app frameworks (FastAPI + SQLAlchemy, Django, Prisma) use in DATABASE_URL. Learning it here pays off everywhere:

psql postgres://postgres:mypassword@localhost:5432/shop

psql (16.3)
Type "help" for help.

shop=#

For one-off scripts you can pass the password through the PGPASSWORD environment variable:

PGPASSWORD=mypassword psql -h localhost -U postgres -d shop

# host:port:database:user:password
localhost:5432:shop:postgres:mypassword

Lost? \conninfo tells you exactly which database, user, host, and port you're on. Make this a reflex before running anything destructive:

\conninfo

You are connected to database "shop" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".

One more thing before we explore — create the web-shop database this series uses. Full walkthrough with explanations lives in Create Database & Tables; here's the compact version so this post is runnable on its own:

CREATE DATABASE shop;
\c shop

CREATE TABLE customers (
  customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   TEXT NOT NULL,
  email       TEXT NOT NULL UNIQUE,
  city        TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  product_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  category   TEXT NOT NULL,
  price      NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  stock      INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  order_id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  status      TEXT NOT NULL DEFAULT 'pending',
  ordered_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_item_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id      INTEGER NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
  product_id    INTEGER NOT NULL REFERENCES products (product_id),
  quantity      INTEGER NOT NULL CHECK (quantity > 0),
  unit_price    NUMERIC(10,2) NOT NULL
);

INSERT INTO customers (full_name, email, city) VALUES
  ('Maria Santos',   '[email protected]',   'Manila'),
  ('Juan dela Cruz', '[email protected]',  'Cebu'),
  ('Ana Reyes',      '[email protected]',      'Davao'),
  ('Carlo Garcia',   '[email protected]',   'Quezon City'),
  ('Liza Mendoza',   '[email protected]',   'Makati');

INSERT INTO products (name, category, price, stock) VALUES
  ('Mechanical Keyboard', 'Accessories', 2499.00, 15),
  ('USB-C Hub',           'Accessories', 1299.00, 30),
  ('27-inch Monitor',     'Displays',   10995.00,  8),
  ('HD Webcam',           'Accessories', 1899.00, 20),
  ('Laptop Stand',        'Accessories',  899.00, 25),
  ('Wireless Mouse',      'Accessories',  749.00, 40);

INSERT INTO orders (customer_id, status) VALUES
  (1, 'paid'),
  (2, 'pending'),
  (3, 'shipped');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1,  2499.00),
  (1, 2, 2,  1299.00),
  (2, 3, 1, 10995.00),
  (3, 5, 1,   899.00);

3The Prompt, Decoded (and the Trap Inside It)

That little string before your cursor is a status display, hindi lang decoration. It tells you which database you're in, who you are, and — most importantly — whether psql thinks your statement is finished.

The one that traps every beginner is shop-#. You type a query, press Enter, and… nothing happens. Walang error, walang result. Ganito ang itsura:

SELECT full_name, city FROM customers

shop=# SELECT full_name, city FROM customers
shop-#

Three clean ways out of the continuation prompt:

• Finish it: just type ; and press Enter — the buffered statement runs.
• Abandon it: press Ctrl+C — psql cancels the input and gives you a fresh shop=#.
• Clear the buffer: type \r (reset) — same effect, and it works even when you're stuck inside an unclosed quote.

shop-# ;
   full_name    |    city
----------------+-------------
 Maria Santos   | Manila
 Juan dela Cruz | Cebu
 Ana Reyes      | Davao
 Carlo Garcia   | Quezon City
 Liza Mendoza   | Makati
(5 rows)

4Exploring: The Meta-Command Family

This is where psql outruns every GUI. A handful of two-keystroke commands answer the questions you ask a hundred times a day. Let's tour them against our shop database.

\l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 shop      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

(template0 and template1 are the blueprints Postgres clones when you run CREATE DATABASE — leave them alone.)

\c shop

You are now connected to database "shop" as user "postgres".

\dt

          List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | customers   | table | postgres
 public | order_items | table | postgres
 public | orders      | table | postgres
 public | products    | table | postgres
(4 rows)

This is the meta-command I use most. Columns, types, nullability, defaults, indexes, and every constraint — one screen:

\d customers

                                        Table "public.customers"
   Column    |           Type           | Collation | Nullable |           Default
-------------+--------------------------+-----------+----------+------------------------------
 customer_id | integer                  |           | not null | generated always as identity
 full_name   | text                     |           | not null |
 email       | text                     |           | not null |
 city        | text                     |           |          |
 created_at  | timestamp with time zone |           | not null | now()
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customer_id)
    "customers_email_key" UNIQUE CONSTRAINT, btree (email)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

Add a + for the verbose version — \d+ also shows storage details and, at the end, check constraints, comments, and the table's access method:

\d+ products

                                                   Table "public.products"
   Column   |     Type      | Collation | Nullable |           Default            | Storage  | Stats target | Description
------------+---------------+-----------+----------+------------------------------+----------+--------------+-------------
 product_id | integer       |           | not null | generated always as identity | plain    |              |
 name       | text          |           | not null |                              | extended |              |
 category   | text          |           | not null |                              | extended |              |
 price      | numeric(10,2) |           | not null |                              | main     |              |
 stock      | integer       |           | not null | 0                            | plain    |              |
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
Check constraints:
    "products_price_check" CHECK (price >= 0::numeric)
Referenced by:
    TABLE "order_items" CONSTRAINT "order_items_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(product_id)
Access method: heap

\du

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 thirdy    | Create DB

\dn

      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

\df

                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

Empty for now — walang user-defined functions pa tayo. After you work through PostgreSQL Functions, this list starts filling up, and \df becomes how you find them again.

5Running SQL: Multi-Line, \e, and \g

psql doesn't care about line breaks — only the ; ends a statement. That means you can format queries across multiple lines exactly like you'd write them in a file. The prompt keeps showing -# until you close the statement:

SELECT c.full_name,
       count(o.order_id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.full_name
ORDER BY total_orders DESC, c.full_name;

shop=# SELECT c.full_name,
shop-#        count(o.order_id) AS total_orders
shop-# FROM customers AS c
shop-# LEFT JOIN orders AS o ON o.customer_id = c.customer_id
shop-# GROUP BY c.full_name
shop-# ORDER BY total_orders DESC, c.full_name;
   full_name    | total_orders
----------------+--------------
 Ana Reyes      |            1
 Juan dela Cruz |            1
 Maria Santos   |            1
 Carlo Garcia   |            0
 Liza Mendoza   |            0
(5 rows)

Typo in a five-line query? Don't retype it. \e opens the current query buffer (or the last executed statement) in your $EDITOR. Fix it, save, quit — psql runs the edited statement the moment it ends with ;.

# pick your editor once, in your shell profile
export EDITOR=nano

\e

Kung vi ang bumukas at hindi ka makalabas: press Esc, type :wq, Enter. (Everyone has been there. Walang judgment.)

\g executes whatever is in the query buffer again — perfect for "did my UPDATE change the count?" loops. Bonus: \g filename re-runs it and writes the result to a file instead of the screen.

SELECT count(*) AS pending_orders FROM orders WHERE status = 'pending';
\g

 pending_orders
----------------
              1
(1 row)

 pending_orders
----------------
              1
(1 row)

6Wide Rows: Expanded Display and the Pager

Select a whole row from a table with many columns and the output wraps into unreadable soup. psql's fix is expanded display: \x flips the table 90 degrees so each column becomes its own line.

\x
SELECT * FROM customers WHERE customer_id = 1;

Expanded display is on.
-[ RECORD 1 ]---------------------------------
customer_id | 1
full_name   | Maria Santos
email       | [email protected]
city        | Manila
created_at  | 2026-07-18 09:14:22.583127+08

\x is a toggle — run it again to go back to normal tables. Even better for daily work: \x auto lets psql decide, using normal tables when they fit your terminal and flipping to expanded only when a row is too wide. Set it and forget it.

\x auto

Expanded display is used automatically.

The other surprise for beginners is the pager. When a result has more rows than your terminal, psql pipes it through less: your screen is "taken over," arrow keys scroll, and q brings you back. That's a feature, not a hang — pero if it annoys you during a demo or when copy-pasting results:

\pset pager off

Pager usage is off.

7Running .sql Files

Real work lives in files — schema definitions, seeds, migrations, reports. There are three ways to run them, and you'll use all three.

\i reads a file and executes every statement in it, echoing each result. Paths are relative to the directory where you started psql:

\i setup_shop.sql

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 5
INSERT 0 6
INSERT 0 3
INSERT 0 4

Same effect, no interactive session — this is the form that belongs in deploy scripts and CI pipelines:

psql -U postgres -d shop -f setup_shop.sql

For a single statement, skip the file entirely. -c runs one command and exits — perfect for shell scripts and health checks:

psql -U postgres -d shop -c "SELECT count(*) FROM products;"

 count
-------
     6
(1 row)

8Import & Export CSV with \copy

"Pa-export naman ng CSV" is a request you will get for the rest of your career. psql handles it natively — walang Excel plugin, walang GUI export wizard.

\copy (SELECT full_name, email, city FROM customers ORDER BY customer_id) TO 'customers.csv' WITH (FORMAT csv, HEADER)

COPY 5

Check the file without leaving psql (that's the \! shell escape — more in Section 9):

\! cat customers.csv

full_name,email,city
Maria Santos,[email protected],Manila
Juan dela Cruz,[email protected],Cebu
Ana Reyes,[email protected],Davao
Carlo Garcia,[email protected],Quezon City
Liza Mendoza,[email protected],Makati

Say marketing hands you two new products in a CSV:

name,category,price,stock
Ring Light,Accessories,1499.00,12
Ergonomic Chair,Furniture,7995.00,5

\copy products (name, category, price, stock) FROM 'new_products.csv' WITH (FORMAT csv, HEADER)

COPY 2

Verify — naming the columns instead of SELECT *, dahil alam na natin kung ano'ng hinahanap natin:

SELECT product_id, name, price FROM products ORDER BY product_id DESC LIMIT 2;

 product_id |      name       |  price
------------+-----------------+---------
          8 | Ergonomic Chair | 7995.00
          7 | Ring Light      | 1499.00
(2 rows)

9Quality of Life: The Tricks That Make psql Fast

These five habits are the difference between "I tolerate psql" and "psql is faster than my GUI."

• Tab completion. psql completes keywords, table names, and column names. Type SELECT * FROM cus then press Tab — it becomes customers. It even completes meta-commands and file paths after \i.
• History. Up/Down arrows walk through previous commands, exactly like your shell. Ctrl+R searches history incrementally, and \s prints the whole session history.

\timing
SELECT count(*) FROM order_items;

Timing is on.
 count
-------
     4
(1 row)

Time: 0.412 ms

It's a toggle, and it stays on for the session. Turn it on before you compare two versions of a query — gut feeling is not a benchmark.

Run a query, then \watch N re-executes it every N seconds until you press Ctrl+C. Monitoring a batch job's progress, watching orders come in during a launch — this is the tool:

SELECT status, count(*) FROM orders GROUP BY status;
\watch 5

          Fri 18 Jul 2026 10:15:04 (every 5s)

 status  | count
---------+-------
 paid    |     1
 pending |     1
 shipped |     1
(3 rows)

          Fri 18 Jul 2026 10:15:09 (every 5s)

 status  | count
---------+-------
 paid    |     2
 pending |     1
 shipped |     1
(3 rows)

Need to peek at a file, check disk space, or clear the screen without losing your session? \! runs any shell command and drops you right back at the prompt:

\! ls -lh *.csv

-rw-r--r--  1 thirdy  staff   231B Jul 18 10:12 customers.csv
-rw-r--r--  1 thirdy  staff    89B Jul 18 10:14 new_products.csv

10Common Mistakes (Everyone Makes These)

Mistake #1: single vs double quotes. In PostgreSQL, 'single quotes' are for string values, and "double quotes" are for identifiers (table and column names). Coming from JavaScript or Python, where both quote strings, this bites hard:

SELECT * FROM customers WHERE full_name = "Maria Santos";

ERROR:  column "Maria Santos" does not exist
LINE 1: SELECT * FROM customers WHERE full_name = "Maria Santos";
                                                  ^

SELECT customer_id, full_name, city FROM customers WHERE full_name = 'Maria Santos';

 customer_id |  full_name   |  city
-------------+--------------+--------
           1 | Maria Santos | Manila
(1 row)

Mistake #2: running commands in the wrong database. You connect, type \dt, and psql claims your tables don't exist. Kinabahan ka na — but look at the prompt:

postgres=# \dt
Did not find any relations.

postgres=# \c shop
You are now connected to database "shop" as user "postgres".
shop=# \dt
          List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | customers   | table | postgres
 public | order_items | table | postgres
 public | orders      | table | postgres
 public | products    | table | postgres
(4 rows)

Mistake #3: not knowing how to leave. Classic. The canonical exit is \q, but modern psql (11+) is friendly — exit and quit also work at a fresh prompt, and Ctrl+D works everywhere. The catch: at a continuation prompt, exit is just more text added to your unfinished statement. psql even coaches you:

shop-# exit
Use \q to quit or press control-D (^D) to exit, or terminate your current statement with a semicolon first.

11Best Practices + Meta-Command Cheat Sheet

Print this table, tape it to your monitor — or just come back here. The full list is in \?, but these cover 95% of real sessions:

12Practice Exercises

Five tasks, all against the shop database from Section 2. Bawal mag-GUI — psql only. Expected output is under each task so you can self-check.

Connect to shop as postgres using flags, then prove where you are with a single meta-command.

You are connected to database "shop" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".

Using only meta-commands (no SQL), find which products constraint stops negative prices. Hint: describe the table.

Check constraints:
    "products_price_check" CHECK (price >= 0::numeric)

Write a multi-line query counting customers per city, ordered by city (watch the -# prompts appear), then immediately run it a second time without retyping anything.

    city     | count
-------------+-------
 Cebu        |     1
 Davao       |     1
 Makati      |     1
 Manila      |     1
 Quezon City |     1
(5 rows)

Turn on expanded display and show the full row for order 1, then switch to \x auto.

Expanded display is on.
-[ RECORD 1 ]--------------------------------
order_id    | 1
customer_id | 1
status      | paid
ordered_at  | 2026-07-18 09:20:41.117530+08

Export every product with price above 1400 — columns name and price, ordered by price descending — to expensive.csv with a header row, then print the file without leaving psql.

COPY 5
name,price
27-inch Monitor,10995.00
Ergonomic Chair,7995.00
Mechanical Keyboard,2499.00
HD Webcam,1899.00
Ring Light,1499.00

13What's Next

Recap: meta-commands talk to the psql client and run instantly; SQL talks to the server and needs a ;. Read the prompt, describe before you query, \x auto for wide rows, \copy for CSV, and \q to go home. Kabisado mo na ang terminal — time to put real SQL through it.