Type Constraints in 65 lines of SQL

Summary

PostgreSQL’s extensible type system can encode validation and presentation behavior for a custom Semantic Versioning (SemVer) type. The article stores major, minor, patch, pre-release, and build-metadata components in a composite type, then wraps that type in a domain whose check constraint rejects null or negative numeric components and invalid array elements. A SQL helper validates nullable arrays with regular expressions, and the domain automatically enforces these rules when values are inserted into a table. For ergonomics, a function formats the composite value as a SemVer string, while a stored generated column persists that representation; the article also notes possible parsers, operators, and aggregation functions. It concludes that the ceremony is worthwhile when data integrity is critical and the specification is stable or changes infrequently.

Takeaways

1. 1

   A PostgreSQL domain can layer check constraints over a composite type, and its validation is applied automatically when the domain is used in table columns or inserted values.

2. 2

   The semver_elements_match_regex helper validates nullable text arrays by checking elements with bool_and and requiring a non-empty array when the input is not null.

3. 3

   A stored generated column can persist semver_to_text(package_semver), providing a conventional string representation alongside the structured value without a query or filter penalty.