Thread: Proposal: Support for "NOT NULL" Constraints on Composite Type Fields (Checked at Rest)

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 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