The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Connect with a servicepsql service=reportingView examples
Override database and rolepsql -d appdb -U app_readerView examples
Suppress startup filespsql -X -d appdbView examples
Stop after an errorpsql -X -v ON_ERROR_STOP=1 -f migration.sql appdbView examples
Wrap a file atomicallypsql -X -1 -v ON_ERROR_STOP=1 -f migration.sql appdbView examples
Execute one SQL commandpsql -X -d appdb -c 'SELECT current_timestamp;'View examples
Set a script variablepsql -v tenant=acme -f report.sql appdbView examples
Quote a literal variableSELECT :'tenant' AS tenant;View examples
Quote an identifierSELECT count(*) FROM :"table_name";View examples
List relations\dt app.*View examples
Describe a relation\d+ app.ordersView examples
Show the current connection\conninfoView examples
Enable expanded display\x autoView examples
Select CSV output\pset format csvView examples
Hide headings and row counts\t onView examples
Include relative to script\ir fragments/indexes.sqlView examples
Print the query buffer\pView examples
Capture one result rowSELECT current_user AS actor \gset session_View examples
Time subsequent commands\timing onView examples

psql is both an interactive SQL terminal and a script runner. Its backslash commands run in the client, while SQL is sent to the server; reliable automation depends on understanding that boundary, quoting variables safely, failing on errors, and avoiding secrets in process arguments or history.

Step by step

Detailed examples

01

Connect without leaking credentials or trusting an unsafe schema

Use a service definition, password file, or protected environment supplied by a secret manager instead of putting passwords in command arguments. A connection URI can carry TLS policy, but URI-encode special characters. For sessions executing untrusted SQL, use an empty or tightly controlled search_path so attacker-writable schemas cannot shadow objects.

Connect through a named service with TLS verification
\set ON_ERROR_STOP on
SELECT current_database(), current_user, inet_server_addr();

Note: Invoke with psql service=reporting; store sslmode=verify-full and host identity in pg_service.conf, and protect .pgpass with mode 0600.

Back to quick reference ↑
02

Make scripts stop predictably on errors

Without ON_ERROR_STOP, psql continues after script errors and exits successfully in many cases. Set it for automation and wrap related statements in a transaction. --single-transaction makes a file atomic only when its commands are transaction-safe; commands such as CREATE DATABASE and VACUUM cannot run inside its transaction.

Run a migration as one transaction
\set ON_ERROR_STOP on
ALTER TABLE app.orders ADD COLUMN source text;
UPDATE app.orders SET source = 'legacy' WHERE source IS NULL;
ALTER TABLE app.orders ALTER COLUMN source SET NOT NULL;

Note: Invoke with psql -X -1 -f migration.sql appdb; do not include commands that PostgreSQL forbids inside a transaction.

Back to quick reference ↑
03

Interpolate values with SQL-aware quoting

psql variables are textual client substitutions. Use :'name' for a SQL literal and :"name" for an identifier; raw :name is safe only for already-controlled SQL fragments. Variables are not server bind parameters, and variable interpolation does not occur inside quoted SQL text.

Pass a tenant as a safely quoted literal
\set ON_ERROR_STOP on
SELECT order_id, total
FROM app.orders
WHERE tenant_id = :'tenant'
ORDER BY order_id;

Note: Invoke with -v tenant=acme; :'tenant' asks psql to quote the value as a SQL literal.

Back to quick reference ↑
04

Inspect objects through supported meta-commands

Description commands query catalogs and format the result for humans. Add S for system objects and + for size or detail where supported. Their output is version-specific, so automation should query stable catalog or information-schema columns instead of parsing formatted descriptions.

Inspect a relation and its indexes
\d+ app.orders
\di+ app.orders*

Note: Run interactively; use catalog queries for machine-readable inventory.

Back to quick reference ↑
05

Choose stable output for people or pipelines

Aligned tables are readable interactively; unaligned tuples-only output is easier to consume in simple pipelines. CSV output handles delimiters and quoting better than ad hoc separators. Locale and null rendering can still matter, so prefer a real driver for complex application interchange.

Export a query through the client
\copy (SELECT id, email FROM app.users ORDER BY id) TO 'users.csv' WITH (FORMAT csv, HEADER true)

Note: \copy reads or writes files as the client OS user; server-side COPY uses server filesystem permissions.

Back to quick reference ↑
06

Control the query buffer and compose scripts

Meta-commands operate on psql state, not database transactions. i resolves a path from the process working directory, while ir resolves relative to the including script. Use p and to inspect or clear a partially entered statement before accidental execution.

Compose a migration from sibling files
\set ON_ERROR_STOP on
\ir 010_tables.sql
\ir 020_indexes.sql
\ir 030_grants.sql

Note: \ir makes nested includes location-independent when the migration directory moves.

Back to quick reference ↑
07

Use conditions and captured scalar results deliberately

gset requires exactly one result row and stores each column in a psql variable; NULL unsets the variable. Conditional blocks are evaluated by psql, making them useful for guarded scripts, but authorization must still be enforced by the server. Do not treat client-side conditions as a security boundary.

Gate a version-specific statement
SELECT current_setting('server_version_num')::int >= 180000 AS supported \gset
\if :supported
  \echo 'PostgreSQL 18 feature path'
\else
  \warn 'Unsupported server version'
\endif
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development Grouppsqlpostgresql.org
  2. PostgreSQL Global Development GroupDatabase Connection Control Functionspostgresql.org
  3. PostgreSQL Global Development GroupThe Connection Service Filepostgresql.org
  4. PostgreSQL Global Development GroupThe Password Filepostgresql.org
  5. PostgreSQL Global Development GroupSchemas and Secure Search Pathspostgresql.org
  6. PostgreSQL Global Development GroupCOPYpostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback