psql Cheat Sheet

psql -h localhost -p 5432 -U appuser -d appdb

# Connect with explicit connection flags.

psql -w -h localhost -U appuser -d appdb

# Fail instead of prompting for a password.

psql -W -h localhost -U appuser -d appdb

# Always prompt for the password interactively.

\set schema_name public

# Create or update a psql variable.

SELECT * FROM :schema_name.users LIMIT 5;

# Interpolate a psql variable in a command.

\conninfo

# Display current host, port, user, and database connection info.

PGPASSFILE=./.pgpass psql -h localhost -U appuser -d appdb

# Use a password file for noninteractive authentication.

psql -d appdb -c "SELECT now();"

# Execute a single SQL command from the shell.

\dt

# List tables in visible schemas.

\dv

# List views in visible schemas.

\df

# List functions in the current database.

\ds

# List sequences.

\z public.users

# Show grants and ownership for a relation.

\df+ public.set_updated_at

# Show function details including source and volatility.

\dT

# List data types defined in the database.

\det

# List foreign tables defined via FDWs.

\watch 5

# Rerun the current query every 5 seconds.

\?

# List all psql slash commands.

\pset format unaligned

# Print output without table borders for scripting.

\pset format csv

# Render result output as CSV.

\t on

# Suppress headers and footers.

\pset border 2

# Adjust table border rendering.

\pset null '[NULL]'

# Customize how null values are shown.

\o /tmp/query-output.txt

# Send query output to a file until reset.

\o

# Return output to stdout.

\copy (SELECT * FROM public.users ORDER BY user_id) TO '/tmp/users.csv' CSV HEADER

# Client-side export of a query result to CSV.

psql -v ON_ERROR_STOP=1 -d appdb -f deploy.sql

# Exit immediately when a script encounters an error.

psql -v target_schema=public -d appdb -f script.sql

# Inject psql variables from the shell.

psql -a -d appdb -f script.sql

# Print input lines as they are executed.

psql -E -d appdb

# Show underlying SQL generated by backslash commands.

psql -1 -d appdb -f migration.sql

# Wrap the script in a single transaction.

\if :{?target_schema}
  \echo 'target schema set'
\else
  \echo 'target schema missing'
\endif

# Use psql conditionals in interactive or scripted sessions.

\i ./common.sql

# Read and execute another SQL file.

\! date

# Execute a shell command from inside psql.