← Full reference

psql

PostgreSQL command-line client — connecting, meta-commands \l \dt \d \du, queries from scripts, roles and privileges

psql connects to a PostgreSQL server and runs SQL. Besides SQL it understands backslash meta-commands: \dt, \d, \du show the database structure without querying system tables.

Syntax

psql [-h host] [-p port] [-U user] [-d db] [-c 'SQL'] [-f file.sql]
psql 'postgresql://user:password@host:port/db'

Without -c and -f an interactive console opens (exit — \q). If -d isn't given, psql connects to the database with the same name as the user.

Options

Connection

OptionWhat it does
-h hostconnect over TCP; without -h — via a Unix socket on this machine
-p portserver port (default 5432)
-U namePostgreSQL role; default — the OS user name
-d dbdatabase
-W / -walways prompt for a password / never prompt (for scripts)
-llist databases and exit

Execution

OptionWhat it does
-c 'SQL'run a command and exit; can be repeated
-f filerun SQL from a file (-f - — from stdin)
-v ON_ERROR_STOP=1stop at the first error and return a non-zero code (for -f — 3)
-1run all of -f/-c as one transaction: an error rolls everything back
-v name=valueset a psql variable; in a file it is substituted as :name or :'name'
-Xdon't read ~/.psqlrc
-qdon't print status replies like CREATE TABLE
-Eshow the SQL that meta-commands run

Output format

OptionWhat it does
-Aunaligned
-trows only: no headers and no (N rows)
-Atc 'SQL'a common combo for scripts: a bare value without borders
-F ','field separator in -A mode (default |)
--csvCSV output
-xexpanded output: each column on its own line
-o filequery results go to a file

Meta-commands

Structure

CommandWhat it shows
\l / \l+list of databases (with sizes)
\c db [role]switch to another database
\dt / \dt+tables in the current database (with sizes); \dt schema.* — in a schema
\dall tables, views, sequences
\d table / \d+ tablecolumns, types, defaults, indexes, constraints
\di, \dv, \ds, \dfindexes, views, sequences, functions
\dnschemas
\du (or \dg)roles and their attributes
\dp table (or \z)table privileges (who can do what)
\conninfowhich database and role the client is connected as

Working in the console

CommandWhat it does
\xtoggle expanded output
\timing onshow query execution time
\pset format csvchange the output format (aligned, unaligned, csv, html…)
\i file.sqlrun a file
\o file / \owrite results to a file / back to the screen
\copy table TO 'file' CSV HEADERexport a table to a file on the client side
\copy table FROM 'file' CSV HEADERload a file into a table
\password rolechange a password without it showing up in history
\h command / \?help on an SQL command / on meta-commands
\qquit

Roles and privileges (SQL)

QueryWhat it does
CREATE ROLE app LOGIN PASSWORD 'pw'a role that can log in (CREATE USER — the same with LOGIN by default)
CREATE ROLE readers NOLOGINa group role without login
ALTER ROLE app CREATEDB / SUPERUSERgrant a role attribute
ALTER ROLE app PASSWORD 'new'change the password
GRANT readers TO appadd a role to a group: the group's privileges are inherited
CREATE DATABASE shop OWNER appa database owned by a role
GRANT CONNECT ON DATABASE shop TO appthe right to connect to the database
GRANT USAGE ON SCHEMA public TO appthe right to access objects in the schema
GRANT SELECT, INSERT ON orders TO apptable privileges: SELECT, INSERT, UPDATE, DELETE, ALL
GRANT SELECT ON ALL TABLES IN SCHEMA public TO appon all existing tables in the schema
GRANT USAGE ON SEQUENCE orders_id_seq TO appneeded for INSERT into a table with serial
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO appprivileges on tables created in the future
ALTER TABLE orders OWNER TO appchange the owner
REVOKE SELECT ON orders FROM apprevoke a privilege
DROP ROLE appdrop a role (revoke its privileges first)

Utilities next to psql

CommandWhat it does
createdb db / dropdb dbcreate / drop a database
createuser --login --pwprompt namecreate a role with a password
pg_isready [-h host]does the server accept connections (code 0 — yes)
pg_dump db > dump.sqldump a database to SQL; -Fc — to a compressed format for pg_restore
pg_restore -d db dump.fileload from the -Fc format

Environment variables

PGHOST, PGPORT, PGUSER, PGDATABASE, PGPASSWORD replace the corresponding flags. The password can be kept in ~/.pgpass as a line host:port:db:role:password; the file must have mode 600, otherwise psql ignores it.

Examples

# start the server in the sandbox and check that it accepts connections
service postgresql start
pg_isready

# root is not a PostgreSQL role — work as the postgres system user
sudo -u postgres psql -c '\l'

# create a database and look at its tables
sudo -u postgres createdb shop
sudo -u postgres psql -d shop -c '\dt'

# table structure
sudo -u postgres psql -d shop -c '\d orders'

# a single value without borders — handy to assign to a variable
count=$(sudo -u postgres psql -d shop -Atc 'SELECT count(*) FROM orders')

# run a file as one transaction and stop at the first error
sudo -u postgres psql -d shop -1 -v ON_ERROR_STOP=1 -f schema.sql

# connect over TCP as another role; the password comes from an environment variable
PGPASSWORD='secret' psql -h localhost -U app -d shop -c 'SELECT current_user'

# the same via a URI
psql 'postgresql://app:secret@localhost:5432/shop' -c '\conninfo'

# export a result to a CSV file on the client side
sudo -u postgres psql -d shop -c "\copy (SELECT * FROM orders) TO '/tmp/orders.csv' CSV HEADER"

# backup and restore into another database
sudo -u postgres pg_dump -Fc shop > /tmp/shop.dump
sudo -u postgres createdb shop_copy
sudo -u postgres pg_restore -d shop_copy /tmp/shop.dump

Granting a role read access:

CREATE ROLE app LOGIN PASSWORD 'secret';
GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app;

Pitfalls

  • role "root" does not exist. Without -U the client takes the OS user name. As root, use sudo -u postgres psql or pass -U.
  • The socket and TCP authenticate differently. Without -h the peer method is used: the role name must match the OS user. With -h localhost a password is requested. The rules are in pg_hba.conf (SHOW hba_file shows the path). After editing, the server configuration must be reloaded.
  • Without -d, database = role name. psql -U app fails with database "app" does not exist if there is no such database.
  • Multiple -c don't stop on an error, and the exit code is still 0. In scripts add -v ON_ERROR_STOP=1.
  • GRANT … ON ALL TABLES covers only existing tables. Tables created later stay inaccessible. For them you need ALTER DEFAULT PRIVILEGES — it applies to tables later created by the role that ran this command.
  • INSERT into a table with serial also needs a privilege on the sequence: GRANT USAGE ON SEQUENCE ….
  • DROP ROLE refuses while the role has privileges or objects: role … cannot be dropped because some objects depend on it. Revoke the privileges first (REVOKE) or, in each database, run REASSIGN OWNED BY role TO postgres and DROP OWNED BY role.
  • Unquoted names are folded to lower case. SELECT * FROM Orders looks for orders, while "Orders" means exactly with a capital letter. Write strings in single quotes.
  • COPY … FROM '/path' reads the file on the server as the PostgreSQL process and requires special privileges. Load a file from your side with \copy.
  • :name variables are not substituted in -c. They work in files (-f) and in the interactive console.

See also

sqlite3 · sudo · service · variables