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
| Option | What it does |
|---|---|
-h host | connect over TCP; without -h — via a Unix socket on this machine |
-p port | server port (default 5432) |
-U name | PostgreSQL role; default — the OS user name |
-d db | database |
-W / -w | always prompt for a password / never prompt (for scripts) |
-l | list databases and exit |
Execution
| Option | What it does |
|---|---|
-c 'SQL' | run a command and exit; can be repeated |
-f file | run SQL from a file (-f - — from stdin) |
-v ON_ERROR_STOP=1 | stop at the first error and return a non-zero code (for -f — 3) |
-1 | run all of -f/-c as one transaction: an error rolls everything back |
-v name=value | set a psql variable; in a file it is substituted as :name or :'name' |
-X | don't read ~/.psqlrc |
-q | don't print status replies like CREATE TABLE |
-E | show the SQL that meta-commands run |
Output format
| Option | What it does |
|---|---|
-A | unaligned |
-t | rows 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 |) |
--csv | CSV output |
-x | expanded output: each column on its own line |
-o file | query results go to a file |
Meta-commands
Structure
| Command | What 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 |
\d | all tables, views, sequences |
\d table / \d+ table | columns, types, defaults, indexes, constraints |
\di, \dv, \ds, \df | indexes, views, sequences, functions |
\dn | schemas |
\du (or \dg) | roles and their attributes |
\dp table (or \z) | table privileges (who can do what) |
\conninfo | which database and role the client is connected as |
Working in the console
| Command | What it does |
|---|---|
\x | toggle expanded output |
\timing on | show query execution time |
\pset format csv | change the output format (aligned, unaligned, csv, html…) |
\i file.sql | run a file |
\o file / \o | write results to a file / back to the screen |
\copy table TO 'file' CSV HEADER | export a table to a file on the client side |
\copy table FROM 'file' CSV HEADER | load a file into a table |
\password role | change a password without it showing up in history |
\h command / \? | help on an SQL command / on meta-commands |
\q | quit |
Roles and privileges (SQL)
| Query | What 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 NOLOGIN | a group role without login |
ALTER ROLE app CREATEDB / SUPERUSER | grant a role attribute |
ALTER ROLE app PASSWORD 'new' | change the password |
GRANT readers TO app | add a role to a group: the group's privileges are inherited |
CREATE DATABASE shop OWNER app | a database owned by a role |
GRANT CONNECT ON DATABASE shop TO app | the right to connect to the database |
GRANT USAGE ON SCHEMA public TO app | the right to access objects in the schema |
GRANT SELECT, INSERT ON orders TO app | table privileges: SELECT, INSERT, UPDATE, DELETE, ALL |
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app | on all existing tables in the schema |
GRANT USAGE ON SEQUENCE orders_id_seq TO app | needed for INSERT into a table with serial |
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app | privileges on tables created in the future |
ALTER TABLE orders OWNER TO app | change the owner |
REVOKE SELECT ON orders FROM app | revoke a privilege |
DROP ROLE app | drop a role (revoke its privileges first) |
Utilities next to psql
| Command | What it does |
|---|---|
createdb db / dropdb db | create / drop a database |
createuser --login --pwprompt name | create a role with a password |
pg_isready [-h host] | does the server accept connections (code 0 — yes) |
pg_dump db > dump.sql | dump a database to SQL; -Fc — to a compressed format for pg_restore |
pg_restore -d db dump.file | load 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-Uthe client takes the OS user name. As root, usesudo -u postgres psqlor pass-U.- The socket and TCP authenticate differently. Without
-hthepeermethod is used: the role name must match the OS user. With-h localhosta password is requested. The rules are inpg_hba.conf(SHOW hba_fileshows the path). After editing, the server configuration must be reloaded. - Without
-d, database = role name.psql -U appfails withdatabase "app" does not existif there is no such database. - Multiple
-cdon't stop on an error, and the exit code is still 0. In scripts add-v ON_ERROR_STOP=1. GRANT … ON ALL TABLEScovers only existing tables. Tables created later stay inaccessible. For them you needALTER DEFAULT PRIVILEGES— it applies to tables later created by the role that ran this command.INSERTinto a table withserialalso needs a privilege on the sequence:GRANT USAGE ON SEQUENCE ….DROP ROLErefuses 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, runREASSIGN OWNED BY role TO postgresandDROP OWNED BY role.- Unquoted names are folded to lower case.
SELECT * FROM Orderslooks fororders, 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.:namevariables are not substituted in-c. They work in files (-f) and in the interactive console.