Skip to main content

Nullability

This is the reason the extension exists rather than a catalog query.

Two different questions​

pg_attribute.attnotnull says whether a source column is declared NOT NULL. Whether a result column can be NULL is a different question, and an outer join is where the two come apart:

SELECT ord, name, source_table::text, base_not_null, result_not_null
FROM pg_describe('SELECT o.id, c.email FROM orders o
                  LEFT JOIN customers c ON c.id = o.customer_id');
 ord | name  | source_table | base_not_null | result_not_null
-----+-------+--------------+---------------+-----------------
   1 | id    | orders       | t             | t
   2 | email | customers    | t             | f

customers.email is declared NOT NULL, and base_not_null reports that correctly. The result column is NULL anyway for an order with no customer, because the join null-extends the whole right side when it finds no match.

A tool reading attnotnull alone types that field non-nullable and hands you a null-dereference on the first guest order. Outer joins are not an exotic corner; they are most of the reporting queries anyone writes.

Use result_not_null. base_not_null is exposed because it is occasionally useful to know the difference — for a migration report, say — not because you should type against it.

How it is computed​

By walking the query's join tree, recursing into both arms of each join before applying that join's own rule, and collecting every base relation beneath a null-extended side. That handles the nesting a per-join implementation gets wrong:

a LEFT JOIN (b JOIN c)       b and c are BOTH nullable
(a LEFT JOIN b) JOIN c       only b is nullable
a LEFT JOIN (b LEFT JOIN c)  b and c

In the first case, an outer join that finds no match null-extends its entire right subtree at once, so the inner join between b and c is irrelevant. All three shapes are in the regression suite; an implementation inspecting only each join's immediate arms passes the second and fails the first.

FULL JOIN nulls both sides. RIGHT JOIN nulls the left.

Grouping sets​

GROUP BY ROLLUP, CUBE and GROUPING SETS null-extend grouping columns in their super-aggregate rows — the total row of a rollup has NULL where the grouping column was. When a query uses them, no provenance-bearing column is reported non-null.

What is not modelled​

All of these report nullable or unknown, so all of them fail safe:

  • Set operations. UNION branches are subquery range table entries, so target-list Vars do not resolve to a base relation and no flags are reported.
  • Subqueries and CTEs. Same reason.
  • CHECK constraints, partial indexes, WHERE x IS NOT NULL. Predicates that guarantee non-nullness are not consulted, so a column that cannot be NULL in practice may still be reported nullable.
  • Expressions. upper(x), count(*) and literals have a type but no source column, so both flags are NULL, meaning unknown.

NULL in result_not_null means unknown, not not null. Treat it as nullable.

In generated TypeScript​

pg-describe-gen follows the same rule: result_not_null = t becomes T, anything else becomes T | null.

export interface ListRecentOrdersRow {
  id: string            // orders.id       — NOT NULL, and not outer-joined
  email: string | null  // customers.email — NOT NULL, but LEFT JOINed
}

Over-declaring a null costs one impossible check. Under-declaring one costs a crash. When the analysis is uncertain, it picks the cheap mistake.

Parameters are the exception: they are declared non-null. Passing null is legal SQL, but it is almost always a bug rather than an intent, and | null on every parameter makes the generated types tiresome to use.