Dear PostgreSQL Hackers,
I would like to propose adding support for NOT NULL constraints on
individual fields within composite types, with enforcement *only at rest*
when the composite type is used as a table column. This approach addresses
several key challenges while aligning with PostgreSQL’s existing behavior
for NOT NULL constraints.
Rationale
1.
*Consistency with Existing NOT NULL Behavior*:
- NOT NULL constraints for table columns are already enforced *only
during INSERT or UPDATE* operations. This proposal extends the same
principle to composite types, ensuring constraints are checked
at the same
points in the data lifecycle.
- This avoids runtime inconsistencies, as queries (e.g., joins) and
in-memory operations in procedural languages (e.g., plpgsql) are not
expected to revalidate NOT NULL constraints.
2.
*Avoids Fundamental SQL Conflicts*:
- SQL semantics, particularly in outer joins, allow for NULL values in
query results. Enforcing NOT NULL constraints outside of the “at
rest” context would lead to contradictions, as demonstrated
<https://www.postgresql.org/message-id/20534.1481817136%40sss.pgh.pa.us>
by Tom Lane with domains in a previous discussion.
- By limiting enforcement to storage, this proposal avoids the
conflict while preserving the utility of NOT NULL for data integrity.
3.
*Improved Usability for Composite Types*:
- Composite types are often used to model structured data. Allowing
field-level NOT NULL constraints enhances their expressiveness,
enabling developers to enforce more robust data models directly within
PostgreSQL.
- This improvement benefits schema design by embedding field
constraints directly in the composite type definition, reducing
reliance on
table-level CHECK constraints or application-level validations.
Scope of Enforcement
- *At Rest*:
- Constraints are enforced during INSERT and UPDATE operations when a
composite type is used as a column in a table.
- *Out of Scope*:
- Constraints are *not enforced* during query execution, in
procedural language variables, or for composite types passed as function
parameters, to avoid runtime performance and semantic conflicts.
Why This is a Good Compromise
This proposal strikes a balance between utility and feasibility:
- It avoids the known pitfalls of universally enforcing NOT NULL
constraints for composite types while still enabling field-level integrity
checks during storage operations.
- It aligns with PostgreSQL’s existing constraint enforcement model,
ensuring intuitive behavior for users already familiar with how NOT NULL
works for table columns.
Next Steps
If there is interest, I would be happy to explore the technical
implications further and submit a draft patch for discussion. Feedback on
potential implementation challenges or edge cases is very welcome.
Thank you for considering this proposal!
Best regards,
Alec Larson
