pg_describe

Overview

Uses PostgreSQL parser and analyzer without invoking the executor; upstream and PIGSTY packages require PostgreSQL 17 or newer.

Version

Build

You can build the RPM / DEB packages for pg_describe using pig build:

pig build pkg pg_describe         # build RPM / DEB packages

Install

You can install pg_describe directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_describe;          # Install for current active PG version

pig ext install -y pg_describe -v 18  # PG 18
pig ext install -y pg_describe -v 17  # PG 17

dnf install -y pg_describe_18       # PG 18
dnf install -y pg_describe_17       # PG 17

apt install -y postgresql-18-pg-describe   # PG 18
apt install -y postgresql-17-pg-describe   # PG 17

Create Extension:

CREATE EXTENSION pg_describe;

Usage

Sources:

• pg_describe 1.0.0 README
• pg_describe documentation
• pg_describe 1.0.0 control file
• pg_describe 1.0.0 SQL

pg_describe reports the parameters and result columns of a SQL statement without executing it. It uses PostgreSQL parsing and analysis to infer parameter types, wire-visible result types, source-column provenance, and outer-join-aware nullability. Use it for code generation, migration checks, and query-contract tooling.

Describe a Query

CREATE EXTENSION pg_describe;

SELECT *
FROM pg_describe(
  'SELECT id, email FROM users WHERE id = $1'
);

Rows with kind = 'param' describe $1, $2, and later parameters. Rows with kind = 'column' describe result-column order, name, type OID/name, source table/column, base NOT NULL status, and whether the final expression is known non-null.

Check Join Nullability

SELECT *
FROM pg_describe($query$
  SELECT o.id, c.email
  FROM orders AS o
  LEFT JOIN customers AS c ON c.id = o.customer_id
  WHERE o.placed_at >= $1
$query$);

Even when customers.email is declared NOT NULL, result_not_null is false because a left join can null-extend the row. This distinction is useful when generating nullable client types.

Execution and Security Boundary

• The statement is parsed and analyzed but not executed. Describing a DELETE, volatile function call, or expensive query does not run the statement.
• Normal name resolution and privilege checks still apply. Callers cannot use pg_describe to inspect objects they could not reference themselves.
• Parameter types must be inferable from context; ambiguous $n parameters still produce PostgreSQL analysis errors.
• The result describes PostgreSQL’s analyzed output, not dynamic SQL assembled later by an application.

Requirements and Caveats

• Upstream 1.0.0 requires PostgreSQL 17; PostgreSQL 16 is described as possibly working but untested. Pigsty packages target PostgreSQL 17 and 18.
• The extension is relocatable and does not require preloading or a restart.
• The companion pg-describe-gen TypeScript tool is a separate npm package. The PostgreSQL extension works without it.
• This is a young API. Pin the extension/tool versions in CI and review generated changes alongside schema migrations.