psql

What this skill does

# psql — PostgreSQL Interactive Terminal

## Overview

`psql` is PostgreSQL's official **command-line client**: an interactive REPL *and* a SQL-script
runner built on **libpq**. You use it to connect to a server, type SQL, inspect the schema with
backslash **meta-commands** (`\d`, `\dt`, `\df`, …), format and capture output, and run scripts in
shells, CI, and agents. It is a thin client — the database engine, SQL semantics, and server
administration all live on the **server**, not in `psql`.

**Key characteristics:**
- **Client, not server** — `psql` connects to a running PostgreSQL server; it stores no data and
  runs no SQL itself. Almost everything is gated by the **`psql` (client) version**, independent of
  the server you reach.
- **Two layers in one prompt** — **SQL** (sent to the server, terminated by `;`) and
  **meta-commands** (start with `\`, interpreted locally).
- **libpq under the hood** — same connection strings/URIs, `PG*` environment variables, `~/.pgpass`,
  and service files as every other PostgreSQL client.
- **Scriptable & pipeable** — `-c`/`-f`/heredoc input, machine-readable output (`--csv`, `-qAtX`),
  meaningful exit codes, and `ON_ERROR_STOP` make it a first-class scripting tool.

> **Disambiguation — this is the `psql` *client*.** This skill documents the `psql` program: its
> flags, connections, meta-commands, output formatting, variables, and scripting. It is **not** a
> SQL tutorial and does **not** cover server-side topics. For sibling concerns use:
> **`postgres-sql`** (SQL dialect, data types, DDL/DML), **`postgres-performance`** (`EXPLAIN`,
> indexes, `VACUUM`, planner), **`postgres-admin`** (`postgresql.conf`, roles/auth, backup,
> replication), **`postgres-extensions`** (`CREATE EXTENSION`, contrib modules). When in doubt: if
> it works the same in pgAdmin/DBeaver/a driver, it's *not* a `psql` topic — it belongs to a
> sibling skill.

## When to Use This Skill

| Use psql when… | Prefer a sibling when… |
|---|---|
| Connecting to / inspecting a database from a terminal (`\d`, `\l`, `\df`) | Designing tables or writing queries → **postgres-sql** |
| Running a SQL script or one-off query in a shell / CI / agent | Tuning a slow query, reading `EXPLAIN` → **postgres-performance** |
| Formatting or capturing output (CSV, expanded, redirect to file) | Configuring the server, roles, backups → **postgres-admin** |
| Debugging connection/auth (conninfo, `.pgpass`, services, SSL) | Installing/using an extension → **postgres-extensions** |
| Writing `.psqlrc`, scripting with `\set`/`\if`/`\gexec`/`\gset` | Authoring the SQL those scripts run → **postgres-sql** |

## Prerequisites & Version Note

```bash
psql --version        # e.g. "psql (PostgreSQL) 18.1"   (alias: psql -V)
```

This skill documents the `psql` surface as of the **PostgreSQL 19beta1** source tree, so the latest
*stable* line is **pg18** and **pg19 is beta**. Long-standing features (present in **pg13 and
earlier**) are **bedrock** and shown **unannotated**; features added later are tagged inline as
`(pgNN+)`, e.g. `(pg16+)`. Two pre-14 classics are tagged anyway because scripts hit them on old
clients: `\gexec` (pg9.6+) and the `\if` family (pg10+). Every tag is sourced — see
[references/version-features.md](references/version-features.md) for the full feature→version map.
Confirm what you're running with `psql --version` (client) and `\echo :SERVER_VERSION_NUM` (server).

`psql` ships with PostgreSQL (`postgresql-client` / `postgresql` packages, or
`brew install libpq`). A newer `psql` talks to older servers fine and vice-versa.

## The `psql` CLI at a Glance

These invocations cover ~90% of usage:

```bash
psql                                   # connect using PG* env vars / defaults, interactive REPL
psql -d "postgresql://user@host/mydb"  # connect via a URI (or conninfo string)
psql -h db -U me -d app                # connect via discrete flags
psql -c "SELECT version()"             # run one command and exit (great for scripts)
psql -f script.sql                     # run a SQL file and exit
psql -qAtX -c "SELECT count(*) FROM t" # quiet, unaligned, tuples-only, no .psqlrc → clean scalar
psql --csv -c "SELECT * FROM t"        # CSV output (safe quoting) for parsing
```

**Usage:** `psql [OPTION]... [DBNAME [USERNAME]]`. The first non-option argument is the database
(or a full conninfo/URI); a second is the username.

**Most useful flags** (full list in [references/scripting.md](references/scripting.md) &
[references/connection.md](references/connection.md)):

| Flag | Meaning |
|------|---------|
| `-c CMD` | Run one SQL string **or** one backslash command, then exit (repeatable; combine with `-f`) |
| `-f FILE` | Run a SQL file then exit (gives line-numbered errors) |
| `-1` / `--single-transaction` | Wrap all `-c`/`-f` work in one `BEGIN`/`COMMIT` (all-or-nothing) |
| `-X` / `--no-psqlrc` | Skip `~/.psqlrc` — **always use in scripts** for reproducibility |
| `-q` / `-A` / `-t` | Quiet / unaligned / tuples-only (combine as `-qAt` for clean parsing) |
| `--csv` | CSV output mode (= `\pset format csv`) |
| `-F SEP` / `-R SEP` / `-z` / `-0` | Field / record separators; `-z`/`-0` use NUL (for `xargs -0`) |
| `-v NAME=VAL` | Set a `psql` variable (e.g. `-v ON_ERROR_STOP=1`) |
| `-o FILE` / `-L FILE` | Send query output to a file / log all output to a file too |
| `-x` | Expanded output (one column per line) |
| `-E` | Echo the hidden catalog query behind each `\d` command |
| `-h`/`-p`/`-U`/`-d` · `-w`/`-W` | Host / port / user / dbname · never-prompt / force-prompt for password |

**Output formats** (`\pset format X` or a flag): `aligned` (default), `unaligned`, **`csv`**,
`wrapped`, `html`, `asciidoc`, `latex`, `latex-longtable`, `troff-ms`. Toggle expanded rows with
`\x`.

## Connecting

Four interchangeable mechanisms (mix freely; conninfo > flags > env > service > defaults):

```bash
psql -h db.example.com -p 5432 -U me -d app          # discrete flags
psql "host=db user=me dbname=app sslmode=require"    # conninfo string
psql postgresql://[email protected]:5432/app         # URI
PGHOST=db PGUSER=me PGDATABASE=app psql              # environment variables
psql service=prod                                    # a ~/.pg_service.conf entry
```

- **No password on the command line** (it leaks via `ps`). Use **`~/.pgpass`** (lines of
  `host:port:db:user:password`, `chmod 0600`), the `PGPASSWORD` env var, or an interactive prompt.
- **`~/.pg_service.conf`** names a bundle of parameters: connect with `service=NAME`.
- **`\c [conninfo]`** reconnects mid-session; **`\conninfo`** shows the current connection.
- For TLS use `sslmode=verify-full`; for HA list comma-separated hosts +
  `target_session_attrs=read-write`.

Full reference (flags, `.pgpass`, service files, SSL, multi-host, precedence):
[references/connection.md](references/connection.md).

## Core Workflows

### 1. Inspect the schema (the `\d` family)

```sql
\l                      -- list databases
\dt                     -- list tables (\dv views, \di indexes, \dm matviews, \ds sequences)
\d orders               -- one relation: columns, types, indexes, constraints, triggers
\d+ orders              -- + storage, replica identity, access method, comments
\df pg_catalog.now      -- functions; \sf func shows the CREATE OR REPLACE source
\dn                     -- schemas;  \dx  installed extensions;  \du / \drg (pg16+) roles/grants
\dconfig work_mem       -- server config parameters (pattern-aware SHOW)        (pg15+)
```

Suffixes: `S` includes system objects, `+` adds detail, and in **pg18+** a trailing `x` on a
*listing* command forces expanded output (`\dt+x`). Add `-E` (or `\set ECHO_HIDDEN on`) to see the
catalog query a `\d` command runs — a great way to learn the catalogs. Full catalog:
[references/meta-commands.md](references/meta-commands.md).

### 2. Format & capture output

```sql
\x on                            -- expanded: one column per line (wide rows)
\pset null '(null)'