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 psqla superuser shell over the socket (peer authentication)
$ psql -h 127.0.0.1 -U orders_app -d ordersover TCP with a password, like the app
$ psql -XAtc "select count(*) from orders" ordersjust the number, for a script
$ psql -X -v ON_ERROR_STOP=1 -1 -f migrate.sql ordersa script that stops at the first error, all or nothing
$ psql -c "\copy (select * from t) to stdout with (format csv, header)" > t.csvexport CSV with your own permissions
Gotchas
- psql alone connects over the Unix socket as a role named like your OS user: on a fresh Ubuntu server that is "role does not exist" - use sudo -u postgres psql.
- Without -v ON_ERROR_STOP=1 a script carries on after an error and psql exits 0. Scripts: psql -X -v ON_ERROR_STOP=1 -1 -f file.sql.
- -At together give just the value: n=$(psql -XAtc "select count(*) from t").
- sudo -u postgres psql -f ~/x.sql fails on Ubuntu (homes are 0750): feed it on stdin with < ~/x.sql.
- \copy reads and writes files on the client side with your permissions; COPY ... TO '/file' runs on the server as postgres.
- In a transaction the prompt shows *, after an error in a transaction !: end it before you look away, or you block others (idle in transaction).
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.