Lesson 03 beginner

psql Essentials

Connect with flags, URIs, service files and a password file; read the psql prompt; explore objects with backslash commands; make output readable with expanded mode and timing; run scripts safely; use variables, \gset and \watch; and set up a .psqlrc you will keep for years.

psql is the command-line client that ships with PostgreSQL. It runs SQL, but it also has its own meta-commands that start with a backslash: they describe tables, change output formats, run files and much more. Fifteen minutes with this lesson will save you hours later.

Connecting

Every connection needs a host, a port, a role and a database. psql takes them as flags, as a URI, or from environment variables:

$ psql -h localhost -p 5432 -U ana -d postgres
$ psql "postgresql://ana@localhost:5432/postgres"
$ PGHOST=localhost PGDATABASE=postgres psql

Leave out a value and psql uses a default: the local socket for the host, 5432 for the port, your OS user name for the role, and the role name for the database.

Saving passwords and profiles

Typing passwords is fine interactively; scripts need something better. ~/.pgpass holds one line per connection, host:port:database:user:password, and must not be readable by others:

$ echo 'db.example.com:5432:orbit:orbit_app:S3cret-Pass' >> ~/.pgpass
$ chmod 600 ~/.pgpass

~/.pg_service.conf gives a whole connection a short name:

[orbit-prod]
host=db.example.com
port=5432
dbname=orbit
user=orbit_app
sslmode=verify-full
$ psql service=orbit-prod

Both files are read by libpq, the client library under psql, so pg_dump, many drivers and GUI tools understand them too.

Reading the prompt

postgres=#     superuser, connected to database "postgres"
orbit=>        ordinary role, connected to database "orbit"
orbit->        statement not finished yet (no semicolon)
orbit(>        inside an open parenthesis
orbit=*>       inside a transaction block (after BEGIN)
orbit=!>       inside a failed transaction: ROLLBACK to continue

A SQL statement runs when you type a semicolon. If you press Enter and nothing happens, look at the prompt: you probably forgot it. Meta-commands such as \dt do not need one.

Exploring with backslash commands

CommandShows
\lDatabases (\l+ adds sizes)
\c orbitConnect to another database
\conninfoWho and where you are connected
\dnSchemas
\dt / \dt+Tables (+ adds size and description)
\d notesColumns, indexes, constraints and triggers of one table
\di \dv \ds \dfIndexes, views, sequences, functions
\duRoles and their attributes
\dxInstalled extensions
\dp notesPrivileges on an object
\sf nameSource of a function
\dconfig *mem*Settings matching a pattern

Every list command accepts a pattern: \dt public.*, \df *note*, \dt *.* for all schemas. Two help commands cover everything else:

\?                 list every psql command
\h CREATE TABLE    full syntax of any SQL statement

Making output readable

Wide rows wrap into an unreadable mess. Expanded mode prints one column per line:

\x auto
SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();

With \x auto, psql switches to expanded mode only when a row does not fit your terminal. To expand a single query, end it with \gx instead of a semicolon.

Some more switches you will use often:

\timing on            print how long each statement took
\pset null '(null)'   show NULL visibly instead of as an empty cell
\pset format csv      print CSV (also: unaligned, html, wrapped)

Try the basics with a calculation that needs no tables:

SELECT 1 + 1 AS two, upper('orbit') AS shout, 7 / 2 AS int_div, 7 / 2.0 AS num_div;
 two | shout | int_div |      num_div
-----+-------+---------+--------------------
   2 | ORBIT |       3 | 3.5000000000000000
(1 row)

Note the last two columns: dividing two integers gives an integer. Lesson 5 explains the types behind that.

Running SQL from files and the shell

$ psql -d orbit -c 'SELECT count(*) FROM notes;'
$ psql -d orbit -v ON_ERROR_STOP=1 -f migrations/001_create_tables.sql
$ psql -d orbit -1 -v ON_ERROR_STOP=1 -f migrations/002_add_tags.sql

-c runs one command, -f a file. Two flags make scripts safe:

  • -v ON_ERROR_STOP=1 stops at the first error and makes psql exit with a non-zero status. Without it, psql reports the error, carries on with the next statement and exits with 0, so your deploy script thinks everything worked.
  • -1 (--single-transaction) wraps the whole file in one transaction, so it applies completely or not at all.

Inside psql, \i file.sql runs a file and \o file.txt sends results to a file until you type \o again.

For scripts, -At prints bare values without headers or alignment:

$ count=$(psql -d orbit -At -c 'SELECT count(*) FROM users;')
$ echo "users: $count"

Variables

psql variables let you reuse values and parameterise scripts:

\set uid 1
SELECT title FROM notes WHERE user_id = :uid;
\set email 'ana@orbit.dev'
SELECT id FROM users WHERE email = :'email';

:uid is pasted as is, :'email' is pasted as a properly quoted string literal and :"name" as a quoted identifier. Pass variables from the shell with psql -v uid=1 -f report.sql.

\gset stores the columns of a one-row result into variables named after the columns:

SELECT count(*) AS n FROM pg_database \gset
\echo there are :n databases

Watching a query

\watch re-runs the previous query every few seconds, which is perfect for watching a migration or a queue drain:

SELECT state, count(*) FROM pg_stat_activity GROUP BY state
\watch interval=2 count=10

Press Ctrl+C to stop early.

Editing and history

  • \e opens the current query in $EDITOR; when you save and quit, psql runs it.
  • \ef function_name edits a function the same way.
  • Up and down arrows walk through history, Ctrl+R searches it, and history is saved to ~/.psql_history.
  • \! runs a shell command without leaving psql, for example \! ls migrations.

A .psqlrc worth keeping

psql runs ~/.psqlrc every time it starts interactively. A good starting point:

\set QUIET 1
\pset null '(null)'
\x auto
\timing on
\set ON_ERROR_ROLLBACK interactive
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\set PROMPT1 '%n@%/%R%x%# '
\unset QUIET

ON_ERROR_ROLLBACK interactive deserves a mention: inside a transaction, a typo normally aborts everything you did so far. With this setting psql quietly sets a savepoint before each statement, so only the failed statement is undone.

Note: The results shown in the rest of this course use this .psqlrc, so a missing value appears as (null) rather than as an empty cell.

Quick reference

TaskCommand
Connectpsql -h host -U role -d db or psql "postgresql://..."
Switch database\c orbit
Describe a table\d notes
List tables everywhere\dt *.*
Readable wide rows\x auto or end with \gx
Time statements\timing on
Run a file safelypsql -1 -v ON_ERROR_STOP=1 -f file.sql
Value for scriptspsql -At -c '...'
Store result in variableSELECT ... AS n \gset
Re-run every 2 seconds\watch 2
SQL syntax help\h ALTER TABLE

Exercise

Your prompt shows orbit-> after you press Enter. What does it mean?

  1. You are connected as a read-only role
  2. The previous statement failed
  3. psql lost the connection to the server
  4. The statement is not finished yet, usually because the semicolon is missing
Show answer

Answer: D. The – in the prompt means psql is waiting for more input. Type ; and press Enter to run the statement, or \r to clear the buffer.

Exercise

A deploy script runs psql -f migrate.sql. The second statement fails, yet the CI job is green. What fixes it?

  1. Add -v ON_ERROR_STOP=1 so psql stops and exits with a non-zero status
  2. Add -At so errors are printed without alignment
  3. Use \i migrate.sql instead of -f
  4. Put the password in ~/.pgpass
Show answer

Answer: A. By default psql reports errors and keeps going, then exits with 0. ON_ERROR_STOP makes the first error fatal, and -1 additionally rolls back everything the file did.

Exercise

You want to see one wide row from pg_stat_activity in expanded form, without changing your \x setting. How do you end the query?

  1. With ;;
  2. With \x
  3. With \gx
  4. With \watch
Show answer

Answer: C. \gx runs the query in the buffer once with expanded output. \x toggles expanded mode for every following query.

Key takeaways

  • Connection details come from flags, a URI, environment variables, ~/.pgpass or ~/.pg_service.conf.
  • The prompt tells you who you are (# superuser), whether a statement is unfinished (-) and whether a transaction failed (!).
  • \d and its relatives describe anything; \h gives SQL syntax and \? lists every psql command.
  • \x auto, \timing on and \pset null make output readable.
  • Run scripts with -v ON_ERROR_STOP=1 and, when possible, -1.

FAQ

Why does psql say “password authentication failed” when I am sure of the password?

Check which role you are connecting as: without -U, psql uses your OS user name. Also check ~/.pgpass and the PGPASSWORD variable, which psql uses silently if they are set, and that pg_hba.conf expects a password method for that host.

How do I cancel a long-running query?

Press Ctrl+C once. psql sends a cancel request to the server and returns to the prompt; the connection stays open.

Can I use psql on Windows?

Yes. The Windows installer includes it as SQL Shell (psql). In PowerShell, quote meta-commands carefully: psql -c "\dt" works because double quotes do not treat the backslash specially there.

Is there something friendlier than psql?

pgAdmin, DBeaver and DataGrip are popular graphical clients, and pgcli is a psql alternative with auto-completion and syntax highlighting. They are all fine, but psql is the one you will find on every server.

What is the difference between \copy and COPY?

COPY runs on the server and reads or writes files on the database server’s disk, which needs special privileges. \copy is a psql command that does the file I/O on your machine and streams the data through the connection. Lesson 6 uses it to import CSV files.

↑ ↓ navigate Enter open Esc close