pgsf Postgres SQL Formatter v0.38.0

A guided tour for someone new to pgsf (postgres_sql_formatter): what problem it solves, the one idea you need to hold in your head, the day-to-day loop, and how it fits with the rest of the schema toolchain. For the exhaustive option-by-option reference, see the README; this guide is the "why" and the "how it fits together."

The problem it solves

PostgreSQL doesn't care how your SQL looks. CREATE, create, two spaces or a tab, OR REPLACE or not — all parse to the same thing. That freedom is the problem. Hand-written DDL drifts. A schema dumped from pg_dump comes out in PostgreSQL's house style, not yours. Two developers writing the "same" function produce diffs that are all whitespace and keyword case. Code review burns on layout instead of logic, and a schema you've exported to a file tree never quite matches the next export.

The fix is the same one Go and Rust settled on: one canonical form, applied mechanically. gofmt and cargo fmt ended the formatting argument by making the formatter — not a style guide PDF — the source of truth. pgsf does that for PostgreSQL SQL. There is no config file and no set of stylistic knobs to bikeshed: the house style is fixed, and pgsf is its executable form. You don't format SQL to a style; you run it through pgsf and get the style.

The one idea: format is an idempotent fixed point

The single property everything else rests on is idempotency:

format(format(x)) == format(x)

Run pgsf on any SQL and you get its canonical form. Run pgsf on that and nothing changes — it's already canonical. The output is a fixed point: a stable target every input maps onto, and stays at.

That one guarantee is what makes the tool trustworthy in the ways that matter:

• It's safe in a loop. Format-on-save, a pre-commit hook, a CI gate — none of them can fight each other, because once a file is canonical, formatting it again is a no-op.
• It enables a check mode. "Is this file already formatted?" is a real, decidable question (format(x) == x?) precisely because the canonical form is unique. That's what --check answers.
• It survives round-trips. Export a schema, format it, re-export, re-format — the formatted form is the one both passes converge on, so the tree on disk stays byte-stable across regenerations.

Idempotency isn't a nice-to-have here; it's the executable spec the formatter is built and fuzz-tested to satisfy. Alongside it sits token conservation — every significant token survives the transform except for a small, registered set of meaning-preserving rewrites (keyword case-folding, OR REPLACE stripping, type-alias normalization, and a handful more). The formatter restyles your SQL; it never silently loses a piece of it.

Getting started

just build                 # compile target/release/pgsf
sudo just install          # install to /usr/local/bin/pgsf (sudo: that dir is root-owned)
just install ~/.local/bin  # …or a PATH dir you own — no sudo, no rebuild

build and install are separate recipes, so you compile unprivileged and only sudo the copy; install never recompiles.

The simplest possible use — format a file and look at the result on stdout, touching nothing:

pgsf input.sql             # canonical form → stdout

Run pgsf with no arguments to print help.

The everyday loop

pgsf reads from a file argument, a directory, or stdin, and writes to stdout by default. The flags choose what it reads and whether it writes back.

Look before you leap — format to stdout and eyeball (or pipe into a diff):

pgsf input.sql                       # → stdout, original untouched
pgsf input.sql | diff input.sql -    # see exactly what would change
cat snippet.sql | pgsf               # read from stdin

Format in place once you trust it — -i rewrites the file:

pgsf -i input.sql                    # rewrite the file in canonical form

Format a whole tree — -d walks a directory recursively, formats every .sql file in parallel across your cores, and writes each back (it implies -i):

pgsf -d schema/                      # format every .sql under schema/, in place

Gate it in CI — --check writes nothing, prints the path of every file that isn't already canonical, and exits non-zero if any drift, exactly like gofmt -l / cargo fmt --check:

pgsf --check input.sql               # one file
pgsf --check -d schema/              # a whole tree — fail the pipeline on any drift
cat file.sql | pgsf --check          # stdin

A passing --check run is silent on stdout and exits 0. Under -d the drifting paths are printed sorted, so the output is stable across runs and easy to diff. The one rule for check mode: pass the same body/strip flags your canonical write uses, so the check enforces the very form the write produces.

Bodies: the two flags that change what gets formatted

By default pgsf formats everything — the statement, and the SQL or PL/pgSQL inside a function or procedure body. Two flags adjust that, and they're the only behavioral knobs the tool has. (Every example below is real, verified pgsf output — identifiers quote, and headers lay out on the tool's tab-column grid; don't expect plain, unquoted SQL back.)

-p, --preserve-body formats only the header of a CREATE FUNCTION / CREATE PROCEDURE (the create … as $delimiter$ framing) and leaves the body's content exactly as written. Use it when the body is hand-tuned and you want the canonical wrapper without touching the logic inside:

-- pgsf -p           (header canonicalized, body verbatim)
create function		"logic"."example" ()
	returns		setof text
	language	sql
	stable
	not leakproof
	called		on null input
	security	definer
	parallel	unsafe
as $function$
SELECT name FROM users
$function$;

-- pgsf              (no -p: the body is formatted too)
create function		"logic"."example" ()
	returns		setof text
	language	sql
	stable
	not leakproof
	called		on null input
	security	definer
	parallel	unsafe
as $function$
select			"name"
	from		"users"
$function$;

A few things happen to the header either way, -p or not: keywords lowercase, every identifier quotes, and the function's implicit default attributes — here called on null input and parallel unsafe — are made explicit even though the input never wrote them. or replace round-trips: a create or replace function comes back as one, and only -s (below) removes it.

Comments are preserved. A comment that owns its line is kept above the statement it introduces, and one sharing the line with a statement's ; stays after it:

-- Accounts that can still sign in.
create view		"logic"."active_users"
	as		(
				select			"id"
					from		"users"
			); -- refreshed nightly

A comment inside a statement — after a column, mid-expression — is re-attached to the end of the output line holding the token it followed. Two limits are structural: a comment spanning lines cannot be placed, and only one comment fits on a line, since a -- comment runs to the end of it. A comment that cannot be placed is dropped rather than put somewhere it does not belong.

--strip-comments drops them instead. Schema export is the context that wants it: a catalog dump carries object documentation in COMMENT ON statements, which pgsf lays out either way, and the generated DDL has no comments of its own worth keeping. A comment inside a function or procedure body is part of that body's content and survives regardless: the body is laid out around its comments.

-s, --strip-or-replace drops OR REPLACE from every statement that can carry it — a function, a procedure, a view, a trigger, and the rest:

-- pgsf               (no -s: or replace is kept)
create or replace view	"logic"."active_users"
	as		(
				select			"id",
							"name"
					from		"users"
					where		"active"
			);

-- pgsf -s            (or replace dropped)
create view		"logic"."active_users"
	as		(
				select			"id",
							"name"
					from		"users"
					where		"active"
			);

This is the schema-export form — a declared view is created, not replaced.

Both flags compose, and each does its own job across a tree: pgsf -p -s -d schema/ preserves function/procedure bodies and strips every OR REPLACE; add --check to gate that exact form in CI.

Comments in a body follow one more rule: the body is laid out around them, a comment that owns its line staying above the statement it introduces and one sharing a line staying after it. If any comment in the body cannot be anchored that way, the whole body passes through as written rather than being re-laid-out with a comment separated from the code it documents.

Validation: rejecting invalid input

By default pgsf checks input against the real PostgreSQL grammar before formatting it. Input that isn't valid — or isn't Postgres-compatible — SQL is rejected with an error instead of silently producing output the tool can't promise is correct:

$ echo "select * form users;" | pgsf
error: <stdin>: invalid SQL: Invalid statement: syntax error at or near "form"

This is a different thing from pgsf's existing leniency for a construct it recognizes but doesn't fully model — that case still formats, with a warning, as a verbatim passthrough (never a hard error). Validation only rejects input that isn't grammatically SQL to begin with, where pgsf genuinely can't promise to format it correctly at all. Pass --no-validate to skip the check (e.g. for a dialect or extension pgsf doesn't recognize but you still want passed through best-effort):

pgsf --no-validate weird_dialect.sql

Under -d/--check, an invalid file is reported and excluded from the total rather than aborting the whole run.

Where it fits: formatting a pgstd export

pgsf is one half of a two-tool schema pipeline, and the division of labor is clean: pgstd (postgres_schema_to_directory) extracts a live database into a per-schema, per-object-type file tree of plain SQL; pgsf then pretty-prints that tree in place, post-hoc:

pgstd -d develop -U postgres … schema/    # 1. extract plain SQL into a file tree
pgsf -d schema/                           # 2. canonicalize the tree in place

The two tools share no build coupling — pgstd emits SQL, any formatter could canonicalize it, and pgsf is the one that does. Because pgstd already emits separate constraint clauses and the redundant-cast policy is shared across both, the post-hoc format is byte-identical to what an integrated pass would produce, and idempotency makes the regenerated tree byte-stable: re-extract, re-format, and a clean tree diffs empty. That stability is exactly what lets --check -d serve as a CI gate against the committed schema.

Using it as a library

pgsf is a Rust library as well as a CLI; reach for the crate when you're building a tool (like a schema exporter) that needs to canonicalize SQL in-process rather than shelling out:

use postgres_sql_formatter::{FormatOptions, format_sql, format_sql_with_warnings};

// `FormatOptions` is `#[non_exhaustive]`: build it from `Default` and name what you
// set, so an option added in a later release takes its default.
let options = FormatOptions::default()
    .with_preserve_body(true)       // the -p flag
    .with_strip_or_replace(true)    // the -s flag
    .with_skip_validation(false);   // the --no-validate flag (inverted)

let formatted = format_sql(&sql, &options)?;  // dispatches by statement

// The same, with every construct pgsf passed through unformatted.
let result = format_sql_with_warnings(&sql, &options)?;
for warning in &result.warnings {
    eprintln!("{warning}");  // e.g. "statement not modeled by the engine, passed through unchanged: …"
}
let formatted = result.sql;

FormatOptions carries the same behavioral choices the -p / -s / --no-validate flags expose, so the library and the CLI produce identical output for identical inputs. Both functions validate the input against the real PostgreSQL grammar before formatting and return Err(SyntaxError) for input that isn't valid (or isn't Postgres-compatible) SQL — distinct from pgsf's lenient handling of a recognized-but-unmodeled construct, which passes through as written and is reported: format_sql_with_warnings returns each one as a Warning (its kind — a statement, a routine body, a condition — and the text), format_sql drops them, and the library never writes to stderr. The pgsf CLI prints each as a one-line warning.

Where to go next

• The README is the full reference — every option, the installation recipes, the git hooks, library usage, and worked before/after examples.
• design/formatting-contract.md states the contract for humans: idempotency, and the fact that the style is fixed and specified separately with pgsf as its executable form.
• design/invariants.md is the executable spec — the idempotency and token-/comment-conservation invariants as a corpus fuzz gate, and the allow-list of meaning-preserving canonical transforms.
• technical.md is the architecture: the tokenizer, the block-based recursive layout, PL/pgSQL handling, and the per-construct formatting rules; developer_guide.md is where to start on a change to it.
• formatting-reference.md shows the canonical form per construct, and conventions/sql-formatting.md is the specification behind it.