Psql Cli

Connection Patterns

Use whichever form matches the environment:

psql -h <host> -p <port> -U <user> -d <dbname>
psql "postgresql://<user>@<host>:<port>/<dbname>?sslmode=require"
PGPASSWORD='<password>' psql -h <host> -U <user> -d <dbname>

Prefer .pgpass, environment variables, or an interactive prompt over embedding passwords in reusable scripts. Do not write credentials into source files.

Discovery Tasks

Use meta-commands first for routine inspection:

\l
\dn
\dt
\dv
\df
\du
\d+ <table_name>
\dp <table_name>

If the request is "what objects exist" or "why can this role not access the table", stay in discovery mode until the target object and privilege path are explicit.

Query and Script Execution

Use direct SQL for one-off work:

psql -h <host> -U <user> -d <dbname> -c "SELECT now();"
psql -h <host> -U <user> -d <dbname> -At -c "SELECT count(*) FROM public.users;"

Use script mode for repeatable execution:

psql -h <host> -U <user> -d <dbname> -v ON_ERROR_STOP=1 -f migrations.sql

Prefer -At for shell consumption and -v ON_ERROR_STOP=1 for any nontrivial script so failures are surfaced immediately.

Non-Interactive Execution

For automation, prefer a stable baseline:

psql -X -v ON_ERROR_STOP=1 -P pager=off -h <host> -U <user> -d <dbname> -f script.sql
psql -X -v ON_ERROR_STOP=1 -P pager=off -h <host> -U <user> -d <dbname> -c "SELECT now();"

Use these flags deliberately:

• -X disables ~/.psqlrc so local user configuration does not change automation behavior.
• -v ON_ERROR_STOP=1 stops on the first error instead of continuing.
• -P pager=off avoids pager-related hangs or truncated interactive output in agent runs.
• -At is useful when the output will be consumed by shell scripts.

If a command needs both meta-commands and SQL, prefer multiple -c arguments, a script file, or a heredoc instead of trying to compress everything into one fragile quoted string.

Use this selection rule:

• Use multiple -c flags for short, ordered command sequences.
• Use a heredoc for medium-length ad hoc runs that mix meta-commands and SQL.
• Use -f or \i for repeatable scripts that should be reviewed or rerun.

Agent-Safe Templates

Use these minimal templates when speed and predictability matter:

psql -X -v ON_ERROR_STOP=1 -P pager=off -h <host> -U <user> -d <dbname> -c '\conninfo'

psql -X -v ON_ERROR_STOP=1 -P pager=off -h <host> -U <user> -d <dbname> \
  -c '\conninfo' \
  -c 'SELECT current_user, current_database(), current_schema();'

psql -X -v ON_ERROR_STOP=1 -P pager=off -h <host> -U <user> -d <dbname> <<'SQL'
\conninfo
\dn
\dt
SELECT now();
SQL

psql -X -v ON_ERROR_STOP=1 -P pager=off -h <host> -U <user> -d <dbname> -f script.sql

docker exec <container_name> bash -lc "PGPASSWORD='<password>' psql -X -v ON_ERROR_STOP=1 -P pager=off -U <user> -d <dbname> -c '\\conninfo'"

docker exec <container_name> bash -lc "PGPASSWORD='<password>' psql -X -v ON_ERROR_STOP=1 -P pager=off -U <user> -d <dbname> -f /workspace/bootstrap.sql"

docker exec -i <container_name> bash -lc "PGPASSWORD='<password>' psql -X -v ON_ERROR_STOP=1 -P pager=off -U <user> -d <dbname>" <<'SQL'
\conninfo
\copy public.customers TO '/workspace/customers_export.csv' CSV HEADER
SELECT count(*) FROM public.customers;
SQL

Meta-Commands and SQL Together

Remember that \copy, \d, \conninfo, and similar commands are psql meta-commands, not SQL. They are parsed by psql itself, so quoting and execution context matter.

Robust patterns:

psql -X -v ON_ERROR_STOP=1 -P pager=off -h <host> -U <user> -d <dbname> \
  -c '\conninfo' \
  -c 'SELECT current_user, current_database(), current_schema();'

Multiple -c arguments run sequentially in the same psql session, which is often the safest short form for agents.

psql -X -v ON_ERROR_STOP=1 -P pager=off -h <host> -U <user> -d <dbname> <<'SQL'
\pset pager off
\conninfo
SELECT current_user, current_database(), current_schema();
SQL

\conninfo
SELECT current_user, current_database(), current_schema();
\copy public.users TO './users.csv' CSV HEADER

If a heredoc or shell string produces errors like invalid command \\, suspect escaping first.

Import and Export

Distinguish server-side COPY from client-side \copy:

• Use COPY when the PostgreSQL server can access the file path.
• Use \copy when the file lives on the client machine running psql.

Typical patterns:

\copy public.users to './users.csv' csv header
\copy public.users from './users.csv' csv header
COPY public.users TO '/server/path/users.csv' CSV HEADER;

If the environment is remote or managed, prefer \copy; it avoids assuming filesystem access on the database host.

When \copy appears inside a script, heredoc, or shell command, remember that it is still a psql meta-command. Do not wrap it as though it were normal SQL.

In containerized workflows, \copy reads and writes files from the filesystem of the machine running psql. If psql is launched via docker exec, that usually means container paths, not host paths.

For imports that must preserve existing rows, prefer an explicit staging pattern:

BEGIN;
CREATE TEMP TABLE customers_import (
    email TEXT,
    full_name TEXT,
    tier TEXT
) ON COMMIT DROP;
\copy customers_import FROM './import_customers.csv' CSV HEADER
INSERT INTO public.customers (email, full_name, tier)
SELECT email, full_name, tier
FROM customers_import
ON CONFLICT (email) DO NOTHING;
COMMIT;

This pattern keeps the import explicit and makes deduplication behavior obvious.

Troubleshooting

For connection failures, check in this order:

1. Address and port
2. Database name and username
3. Password or auth method
4. SSL requirements
5. Server-side pg_hba.conf or role privileges

For query failures, check:

1. Current database and schema
2. search_path
3. Object existence with \d, \dt, \dn
4. Role privileges with \dp or catalog queries
5. Whether the command should run as SQL, COPY, or \copy

For permission failures, use a short fixed sequence:

\conninfo
SELECT current_user, current_database(), current_schema();
\dp schema_name.table_name
\du
SELECT has_schema_privilege(current_user, 'schema_name', 'USAGE');
SELECT
    has_table_privilege(current_user, 'schema_name.table_name', 'SELECT') AS can_select,
    has_table_privilege(current_user, 'schema_name.table_name', 'INSERT') AS can_insert,
    has_table_privilege(current_user, 'schema_name.table_name', 'UPDATE') AS can_update,
    has_table_privilege(current_user, 'schema_name.table_name', 'DELETE') AS can_delete;

This quickly separates the cases of wrong target object, wrong connected role, wrong schema, missing schema usage, and missing table grants.

Safety Boundaries

• Do not assume superuser privileges.
• Do not hide failures behind fallback SQL or silent retries.
• Prefer read-only inspection before destructive changes.
• Use explicit transactions for exploratory deletes or updates when feasible.
• Keep authentication material out of repository files and committed scripts.