cannot use subquery in DEFAULT expression

Symptoms

A column DEFAULT expression contained a subquery. Default expressions cannot reference other tables, so PostgreSQL raises SQLSTATE 0A000 (feature_not_supported).

• A DEFAULT used a SELECT subquery.
• Defaults must be self-contained expressions.
• Common when trying to default from a lookup table.

What the server log shows

ERROR:  cannot use subquery in DEFAULT expression

Why PostgreSQL raises this — what the manual says

As the CREATE TABLE reference (DEFAULT) explains:

A column DEFAULT expression must be evaluable for a single row in isolation, so it may not contain a subquery or reference other columns of the table; use a trigger or a generated column if the value must depend on a query result.

A column default must be a variable-free expression evaluated per row at insert time — it cannot run a query. A subquery would reference other rows/tables, which defaults forbid, so PostgreSQL reports 0A000.

Common causes

• Trying to default a column from a SELECT against another table.
• Embedding a subquery to compute a default value.

How to fix it

1. Compute the value in the application or in the INSERT statement.
2. Use a BEFORE INSERT trigger to populate the column from a lookup.
3. Use a non-subquery expression (function call, constant, sequence) as the default.

Related & next steps

• SQLSTATE 0A000 — Feature Not Supported (full reference)
• Message: set-returning functions are not allowed in WHERE
• Glossary: Default value

Reference: PostgreSQL 18 — CREATE TABLE.