psql vs SQL*Plus: Your New Command Line

The First-Week Friction Is All Command Line

Your SQL knowledge transfers to PostgreSQL almost intact. Your SQL*Plus muscle memory does not. Every reflex — describe a table, run a script, spool output, set a variable — has a different keystroke in psql. Here’s the translation table that removes most of the friction, plus the things psql does that SQL*Plus never could.

The translation table

SQL*Plus                        psql
────────────────────────────    ──────────────────────────────
DESCRIBE emp                →    \d emp        (\d+ for detail)
                                 \dt  list tables   \df functions
                                 \dn  schemas       \di indexes
@script.sql                 →    \i script.sql
SPOOL out.txt / SPOOL OFF   →    \o out.txt  /  \o
SET LINESIZE / PAGESIZE     →    \pset  (and \x for expanded rows)
DEFINE v = 42  /  &v        →    \set v 42   /   :v   (:'v' quoted)
SHOW parameter              →    SHOW work_mem;
ALTER SESSION SET ...       →    SET ...   (session GUCs)
EXIT / QUIT                 →    \q
/ (re-run last)             →    \g   (\gx = run + expanded)

The one that saves your eyes: \x

Wide tables wrap into unreadable mush in any terminal. \x (expanded display) pivots each row to a field-per-line block — the equivalent of endlessly tuning LINESIZE/COLUMN formatting in SQL*Plus, except it just works. Toggle it, or use \gx to run a single query expanded.

=> \x
Expanded display is on.
=> SELECT * FROM employees WHERE id = 7;
-[ RECORD 1 ]-------------
id         | 7
first_name | Ada
salary     | 92000.00
hired_at   | 2021-03-01 09:14:00+00

What psql adds that SQL*Plus never had

• Tab completion of keywords, table and column names. Alone worth the switch.
• \timing — per-statement execution time without wrapping anything.
• \e — open the last query in $EDITOR, edit, save, run. \ef edits a function.
• \watch 5 — re-run the current query every 5 seconds (a live monitor, e.g. on pg_stat_activity).
• ~/.psqlrc — persist your defaults (\set, \pset, \timing on).

One trap: autocommit is ON

SQL*Plus holds your DML open until you COMMIT. psql is autocommit by default — every statement commits on its own. If you want a transaction, you say so: BEGIN; … COMMIT; (or ROLLBACK;). Muscle memory that assumes an implicit open transaction will surprise you the first time a bad UPDATE is already permanent.