OnCallReadyStart learning

psql: PostgreSQL interactive terminal

psql [OPTION]... [DBNAME [USERNAME]]

What it does

psql sends SQL to the server and runs its own backslash meta-commands (\d, \x, \copy...). Connection settings come from options, PG* environment variables (PGHOST, PGPORT, PGUSER, PGDATABASE, PGPASSWORD), a conninfo string ("host=... dbname=...") or a URI (postgresql://user@host:port/db), and ~/.pgpass. Exit status: 0 fine, 1 a fatal psql error, 2 the connection went bad, 3 an error in a script with ON_ERROR_STOP set.

Options you will use

-c, --command=COMMAND
run only a single command (SQL or internal) and exit. Several -c run in order; "a; b" in one -c is one transaction.
-d, --dbname=DBNAME
database name to connect to (or a conninfo string / URI).
-f, --file=FILENAME
execute commands from file, then exit (- = stdin).
-l, --list
list databases, then exit.
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE (e.g. -v ON_ERROR_STOP=1).
-X, --no-psqlrc
do not read the startup file (~/.psqlrc) - what scripts should use.
-1, --single-transaction
execute as a single transaction (if non-interactive).
-a, --echo-all
echo all input from script.
-e, --echo-queries
echo commands sent to server.
-E, --echo-hidden
display queries that internal commands (\d...) generate - how to learn the catalog.
-q, --quiet
run quietly (no messages, only query output).
-A, --no-align
unaligned table output mode.
--csv
CSV (Comma-Separated Values) table output mode.
-F, --field-separator=STRING
field separator for unaligned output (default "|").
-t, --tuples-only
print rows only (no header, no row count).
-x, --expanded
turn on expanded table output (one field per line).
-o, --output=FILENAME
send query results to file (or |pipe).
-h, --host=HOSTNAME
database server host or socket directory (default: the Unix socket in /var/run/postgresql). A value starting with / is a socket directory; anything else is TCP.
-p, --port=PORT
database server port (default 5432).
-U, --username=USERNAME
database user name (default: your OS user name).
-w, --no-password
never prompt for a password (fail instead: what scripts want).
-W, --password
force a password prompt (normally automatic).
-V, --version
output version information, then exit.
-?, --help
show help, then exit.

Examples

$ sudo -u postgres psql

a superuser shell over the socket (peer authentication)

$ psql -h 127.0.0.1 -U orders_app -d orders

over TCP with a password, like the app

$ psql -XAtc "select count(*) from orders" orders

just the number, for a script

$ psql -X -v ON_ERROR_STOP=1 -1 -f migrate.sql orders

a script that stops at the first error, all or nothing

$ psql -c "\copy (select * from t) to stdout with (format csv, header)" > t.csv

export CSV with your own permissions

Gotchas

Taught in

On the blog

Try psql in a real terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.