Command-Line and GUI Clients

The tool you use to talk to the database shapes how fast you work and how safely. People who only know a GUI get stuck on a server with nothing but a terminal. People who only know the terminal miss what a good result grid and a schema browser give them.

Learn both, and know which job suits which.

psql: the tool that is always there#

psql ships with PostgreSQL, runs over SSH, scripts cleanly and starts in milliseconds. It is worth an hour of practice.

Connecting#

psql -h localhost -p 5432 -U postgres -d shop
psql "postgresql://postgres:postgres@localhost:5432/shop"

You can skip the flags with environment variables (PGHOST, PGPORT, PGUSER, PGDATABASE, PGPASSWORD) or, better for passwords, a ~/.pgpass file with the format host:port:database:user:password. PostgreSQL ignores that file unless its permissions are 0600.

The meta-commands#

Commands starting with a backslash are handled by psql, not sent to the server.

\d is the one to master:

shop=# \d order_items
                 Table "public.order_items"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_id   | integer       |           | not null |
 product_id | integer       |           | not null |
 quantity   | integer       |           | not null |
 unit_price | numeric(10,2) |           | not null |
Indexes:
    "order_items_pkey" PRIMARY KEY, btree (order_id, product_id)
Check constraints:
    "order_items_quantity_check" CHECK (quantity > 0)
Foreign-key constraints:
    "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(order_id)
    "order_items_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(product_id)

Columns, types, nullability, keys, indexes, checks and relationships on one screen.

Running SQL non-interactively#

psql -d shop -c "SELECT COUNT(*) FROM orders;"              # one statement
psql -d shop -f report.sql                                   # a file
psql -d shop -At -c "SELECT email FROM customers" > emails.txt   # -A unaligned, -t tuples only

For scripts, always add the flag that makes psql stop at the first error:

psql -d shop -v ON_ERROR_STOP=1 -f migration.sql

By default psql prints an error and keeps going with the next statement. That means a migration can fail halfway, carry on, and leave the database in a half-applied state with an exit code of 0. ON_ERROR_STOP=1 also makes the exit code nonzero, which is what your deploy pipeline needs to notice.

Add --single-transaction (-1) to wrap the whole file in one transaction, so a failure leaves nothing behind.

Your own defaults: ~/.psqlrc#

\set ON_ERROR_ROLLBACK interactive
\set HISTSIZE 5000
\x auto
\timing on
\pset null '(null)'
\set QUIET off

\pset null '(null)' makes NULL visible instead of showing as a blank, which settles the "empty string or NULL?" question at a glance.

A trick worth knowing#

\gexec runs each value in the result as a SQL statement. It generates repetitive DDL without scripting:

SELECT format('CREATE INDEX ON %I (%I)', table_name, column_name)
FROM information_schema.columns
WHERE column_name = 'customer_id' AND table_schema = 'public'
\gexec

Read the generated statements first (run without \gexec) before you let it execute them.

Backup and restore#

The tool for logical backups is pg_dump.

pg_dump -Fc -d shop -f shop.dump           # custom format: compressed, selective restore
pg_dump -d shop -s -f schema.sql           # schema only, as plain SQL
pg_dump -d shop -t orders -f orders.sql    # one table
pg_restore -d shop_copy --clean --if-exists shop.dump

Restore into a fresh database to prove the backup works. A backup you have never restored is a hope, not a backup. For large production systems, also use continuous archiving (WAL archiving and base backups, usually through a tool like pgBackRest or a managed service), which gives you point-in-time recovery.

GUI clients#

What a GUI does better: browsing an unfamiliar schema, reading wide results, sorting and filtering a result grid, drawing ER diagrams, exporting to CSV or Excel, and visual plan viewers for EXPLAIN.

What the terminal does better: anything on a server, anything repeatable, anything you want in version control, and anything that must work the same on your laptop and in CI.

Habits that prevent accidents#

Know whether auto-commit is on. Some GUIs run in auto-commit by default, so an UPDATE is permanent the instant you run it. Others open a transaction and keep it open until you press commit, which silently holds locks. Check the setting for each connection.

Mark production connections. Use a colored connection (red) and a read-only role by default. Switch to a write role deliberately, for one task.

Set a row limit. A SELECT * on a 200-million-row table in a GUI can freeze the application and hammer the server.

Run the statement you highlighted. Many clients run the entire editor buffer unless you select a statement. Double-check what is about to run, especially with DELETE.

Use the safe-edit routine. Write the SELECT, then BEGIN, then the UPDATE, check the count, then COMMIT.

Keeping SQL as code#

Treat SQL files like source code. Keep them in version control, review them, and name them clearly (report_monthly_revenue.sql, not query3_final_v2.sql). A formatter such as pgFormatter and a linter such as SQLFluff keep style consistent, and a team argument about comma placement ends once a tool decides.

Useful conventions: lowercase or uppercase keywords, picked once; one clause per line; one selected column per line in long queries; explicit JOIN ... ON always; table aliases that are short but meaningful.

Common mistakes#

Running migration scripts without ON_ERROR_STOP. Half-applied changes and a green exit code.

Putting a password in a script or a command line. It shows up in process lists and shell history. Use .pgpass or a secrets mechanism.

Never restoring a backup. You find out it was broken at the worst time.

Leaving a GUI transaction open. The locks hold, vacuum stalls, and a colleague's migration queues behind you.

Running ad-hoc writes as a superuser against production. Use a role with only the rights you need.

Practice#

1. Create a ~/.psqlrc with \x auto, \timing on and a visible NULL. Reconnect and run SELECT full_name, phone FROM customers LIMIT 4;.
2. Run \d+ customers, \di, \df and \dp. Find the unique index on email and the primary key index.
3. Write a script with one valid statement and one invalid one. Run it with and without -v ON_ERROR_STOP=1 and compare output and exit status (echo $?).
4. Take a pg_dump -Fc backup of shop, restore it into a new database called shop_copy, and compare row counts across all tables.
5. In your GUI of choice, find the auto-commit setting. Run an UPDATE with it off, check from psql that the change is invisible, then commit.