# Psql CLI

> Command-line workflows for PostgreSQL using the official psql client. Use when Codex needs to connect to a PostgreSQL database, inspect schemas, tables, indexes, roles, or privileges, run ad hoc SQL or .sql scripts, import or export data with COPY or \\copy, troubleshoot connection or permission failures, or translate a database administration request into safe psql commands.

- Skill: `misonl/psql-cli` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx skillmds@latest add misonl/psql-cli`
- Raw SKILL.md: https://api.skillmd.com/api/skills/misonl/psql-cli/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: misonl (https://skillmd.com/u/misonl)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/misonl/psql-cli

---


# psql CLI

## Overview

Use `psql` as the official PostgreSQL command-line client for direct database work. Prefer this skill when the task is shell-oriented, needs introspection with PostgreSQL meta-commands, or must turn an admin request into concrete `psql` invocations.

## Core Workflow

1. Confirm connection inputs before issuing commands: `host`, `port`, `dbname`, `username`, SSL mode, and how authentication will be supplied.
2. Prefer an explicit connection command over hidden defaults.
3. Inspect first, mutate second. Start with identity and context checks:

```sql
\conninfo
SELECT current_user, current_database(), current_schema();
SHOW search_path;
```

4. Use `psql` meta-commands for discovery before writing manual catalog SQL.
5. When executing risky changes, use explicit transactions where possible and stop on first error in scripts.
6. If the user needs a large command matrix, read [command-cheatsheet.md](./references/command-cheatsheet.md).

## Connection Patterns

Use whichever form matches the environment:

```bash
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:

```sql
\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:

```bash
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:

```bash
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:

```bash
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:

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

```bash
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();'
```

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

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

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

```bash
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"
```

```bash
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:

```bash
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.

```bash
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
```

```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:

```sql
\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:

```sql
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:

```sql
\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.