---
title: "Type Constraints in 65 lines of SQL"
description: "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."
---

# Type Constraints in 65 lines of SQL

[Supabase](https://yomu.fyi/company/supabase) · oli\_rice · Feb 17, 2023

**Type:** Tutorial

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

## Context

The initial composite type accepts invalid SemVer components, including null required fields, negative numeric values, disallowed pre-release characters, and empty build-metadata elements. The work is motivated by making invalid states unrepresentable while retaining flexible and efficient querying for a critical application type.

## Approach / What changed

The implementation stores SemVer components in a PostgreSQL composite type, defines a semver domain over it, and applies a check constraint for numeric and array-element validation. A SQL helper checks nullable arrays against regular expressions. A formatting function produces conventional SemVer text, and a stored generated column persists that representation. The article also describes possible parsing functions, equality operators, and a max aggregation.

## Takeaways

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

**Tags:** [Performance](https://yomu.fyi/topic/performance), [Postgres](https://yomu.fyi/topic/postgres), [Reliability](https://yomu.fyi/topic/reliability)

- Source: [Supabase](https://supabase.com/blog/type-constraints-in-65-lines-of-sql)
- Source URL: https://supabase.com/blog/type-constraints-in-65-lines-of-sql
- Ingested by Yomu: 2026-08-29T01:16:34.941Z

[Read original post](https://supabase.com/blog/type-constraints-in-65-lines-of-sql)