CheatPress

psql PostgreSQL shell

Version 18 · source

psql, Postcard, light preview Light · Postcard 102 × 152 mm · PDF psql, Postcard, dark preview Dark · Postcard 102 × 152 mm · PDF psql, Postcard, ink saver preview Ink saver · Postcard 102 × 152 mm · PDF
psql, A5, light preview Light · A5 148 × 210 mm · PDF psql, A5, dark preview Dark · A5 148 × 210 mm · PDF psql, A5, ink saver preview Ink saver · A5 148 × 210 mm · PDF
psql, A4, light preview Light · A4 210 × 297 mm · PDF psql, A4, dark preview Dark · A4 210 × 297 mm · PDF psql, A4, ink saver preview Ink saver · A4 210 × 297 mm · PDF
psql, A4 landscape, light preview Light · A4 landscape 297 × 210 mm · PDF psql, A4 landscape, dark preview Dark · A4 landscape 297 × 210 mm · PDF psql, A4 landscape, ink saver preview Ink saver · A4 landscape 297 × 210 mm · PDF
psql, A3, light preview Light · A3 297 × 420 mm · PDF psql, A3, dark preview Dark · A3 297 × 420 mm · PDF psql, A3, ink saver preview Ink saver · A3 297 × 420 mm · PDF
psql, A3 landscape, light preview Light · A3 landscape 420 × 297 mm · PDF psql, A3 landscape, dark preview Dark · A3 landscape 420 × 297 mm · PDF psql, A3 landscape, ink saver preview Ink saver · A3 landscape 420 × 297 mm · PDF
psql, Letter, light preview Light · Letter 8.5 × 11 in · PDF psql, Letter, dark preview Dark · Letter 8.5 × 11 in · PDF psql, Letter, ink saver preview Ink saver · Letter 8.5 × 11 in · PDF
psql, Tabloid, light preview Light · Tabloid 11 × 17 in · PDF psql, Tabloid, dark preview Dark · Tabloid 11 × 17 in · PDF psql, Tabloid, ink saver preview Ink saver · Tabloid 11 × 17 in · PDF

Start here

psql -h <host> -U <user> -d <db>
Connect
\l
List databases
\dt
List tables
\d <table>
Describe a table
\x auto
Vertical output if wide
\timing
Time each query
\copy <table> to '<file>' csv header
Export CSV
\q
Quit

Concepts

  1. Connect flags, URI, env
  2. Query buffer SQL ending in ;
  3. Result format with \pset
  4. Output \o, \g, \copy

Connect

psql -h <host> -p <port> -U <user> -d <db>
Connect with explicit options
psql "postgresql://<user>@<host>:<port>/<db>"
Connect with a URI
Add ?sslmode=require for TLS.
psql <db>
Connect to a local database
psql -l
List databases and exit
psql -w ...
Never ask for a password
Fails if no password is found; useful in scripts.
psql "service=<name>"
Use a named connection service
\c <db> [<user>]
Switch database in the session
\conninfo
Show the current connection
\password [<user>]
Change a password safely
Hashed on the client, so it stays out of logs and history.
\q
Quit
Ctrl+D also quits.

Inspect

\l
List databases
\dt
List tables
Only the schemas on your search_path; use \dt *.* for all.
\d <table>
Columns, indexes and constraints
\d+ <table>
Same plus size and storage
Slower on large catalogs.
\dn
List schemas
\du
List roles
\dv
List views
\di
List indexes
\df <pattern>
List functions
\dx
List installed extensions
\dp <table>
Show access privileges
\ds
List sequences
\sf <function>
Show a function's source
\sv <view>
Show a view's definition
\dt <schema>.*
Tables in one schema
Patterns: * and ? are wildcards; quote names to keep case.
\dS
Include system objects

Run SQL

psql -c "<sql>"
Run one statement and exit
psql -f <file.sql>
Run a script file
\i <file>
Run a file inside psql
\ir <file>
Run a file relative to this script
\e
Edit the query in $EDITOR
\g
Run the buffer again
\gx
Run once with vertical output
\p
Print the query buffer
\r
Clear the query buffer
\watch <seconds>
Repeat the query every n seconds
Stop with Ctrl+C.
\gexec
Run each result cell as SQL
Generate statements with format(), then execute them.
psql -1 -f <file.sql>
Run a script in one transaction
Pair with -v ON_ERROR_STOP=1 to roll back on error.
\ef <function>
Edit a function definition
\s
Show command history

Output

\x auto
Expanded mode only when wide
\x
Toggle vertical output
\timing
Toggle query timing
\pset null '<text>'
Show NULL as text
\pset format <fmt>
Choose aligned, csv, html or more
\pset border <0-3>
Table border style
\pset linestyle unicode
Box-drawing table lines
\pset pager off
Disable the pager
\t
Toggle header and footer off
psql -A -t -c "<sql>"
Bare values for scripts
psql --csv -c "<sql>"
CSV output
\o <file>
Send results to a file
\o with no argument switches back to the screen.
\crosstabview
Pivot the result
\echo <text>
Print text to stdout

Import & Export

\copy <table> from '<file>' csv header
Load a CSV file
Runs client-side: reads your local file, no superuser needed.
\copy <table> to '<file>' csv header
Export a table to CSV
\copy (<query>) to '<file>' csv header
Export a query result
\copy <table> (<cols>) from stdin
Load from standard input
\copy ... program '<cmd>'
Pipe through a shell command
\copy ... with (format csv, delimiter ';')
Set CSV options
\lo_import <file>
Import a large object
\g (format=csv) <file>
Write one result as CSV
\copy or COPY?
Form File lives on
\copy your machine (client)
COPY database server

Variables & Scripting

\set <name> <value>
Set a psql variable
\set
List all variables
\unset <name>
Remove a variable
psql -v <name>=<value>
Set a variable from the shell
SELECT :'<name>';
Use a variable as a quoted literal
Use :name raw and :"name" as an identifier.
\set ON_ERROR_STOP on
Abort a script on first error
\set AUTOCOMMIT off
Open transactions implicitly
\set ECHO queries
Print each statement sent
\gset [<prefix>]
Store a one-row result in variables
\if <cond> ... \endif
Conditional script parts
\! <cmd>
Run a shell command
\? / \h <command>
Help for psql or SQL
psql -X
Skip ~/.psqlrc

Recipes

Export a query to CSV
\copy (SELECT * FROM <table> WHERE <cond>) to 'out.csv' csv header
Safe migration script
psql -X -1 -v ON_ERROR_STOP=1 -f migrate.sql <db>
Bare value into a shell variable
n=$(psql -At -c "SELECT count(*) FROM <table>" <db>)
Handy ~/.psqlrc
-- ~/.psqlrc\set QUIET 1\timing on\x auto\pset null '(null)'\unset QUIET