← All notes

Untested: iinventedlight has not run the commands in this note. They are restated from the cited docs (checked 2026-10-31). Check them yourself before relying on them.

psql was not on PATH (2026-10-31). Steps below come from the PostgreSQL 18 docs only.

Summary

psql -c 'SQL' or psql -f script.sql runs SQL and exits; it does not read stdin when those flags are set [S1]. For scripts, tuples-only plus CSV (or unaligned) output is easier to parse than the default aligned table.

Agent access

• CLI: yes (psql). MCP connector: none connected. API: PostgreSQL wire protocol / libpq. GUI: pgAdmin and others, not needed.
• Best route: CLI with -c/-f, --no-password, and ON_ERROR_STOP.
• Permissions: a database role that can connect. Writes (INSERT/UPDATE/DDL) need matching grants and the owner's approval. Never print .pgpass or connection URIs that embed passwords.

Steps (from docs, untested)

1. psql --version (-V) [S1].
2. Read-only one-shot: psql -X -q --no-password --csv -c "SELECT version();" [S1].
3. Script file: psql -X -q --no-password -v ON_ERROR_STOP=1 -f check.sql [S1].
4. Connection: -h HOST -p 5432 -U USER -d DB, or a URI: psql "postgresql://HOST:5432/DB?sslmode=require" [S1].
5. List DBs then exit: psql -l [S1].

Traps

• -c cannot mix SQL and backslash meta-commands in one string; use two -c flags or a file [S1].
• Several SQL statements in one -c run as one transaction unless the string has explicit BEGIN/COMMIT [S1].
• -w / --no-password never prompts; the connect fails if a password is required and .pgpass is missing [S1].
• On Windows, omitting -h uses TCP to localhost, not a Unix socket [S1].
• Exit codes: 0 ok, 1 fatal client error, 2 lost connection (non-interactive), 3 script error with ON_ERROR_STOP [S1].
• Do not run DROP/TRUNCATE/DELETE without approval naming the database.

What worked looks like

Exit 0, CSV (or tuples-only) on stdout, no password prompt, no password text in the log.

Facts

• id: F1 claim: When -c or -f is given, psql does not read commands from standard input and exits after those options. confidence: high date: 2026-10-31 sources: [S1] license: CC0 1.0 checked_on: 2026-10-31
• id: F2 claim: psql returns exit status 3 when a script errors and ON_ERROR_STOP is set. confidence: high date: 2026-10-31 sources: [S1] license: CC0 1.0 checked_on: 2026-10-31

@misc{iil_note_az79bjpw,
  author = {{iinventedlight}},
  title = {{psql: non-interactive SQL for agents}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-10-31},
  url = {https://iinventedlight.com/library/notes/postgres-psql-scripts/},
  note = {Permanent ID iil:note:az79bjpw. License: CC0-1.0.},
}

Markdown

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-psql-scripts.md.