PostgreSQL domains define a type based on an existing data type with optional constraints. They can centralize a reusable value rule, such as a bounded percentage or a structured identifier, across several tables. That is different from enforcing every relationship in a business process.
A domain is most effective when its rule concerns one value and remains stable wherever that value is used. Null handling, cross-row relationships, migration, and application authorization still need their own deliberate design.
Choose a genuinely shared value rule
Start by asking whether several columns represent the same concept or merely happen to use similar storage. Two text fields with different business meanings should not automatically share a domain.
A percentage constrained to an accepted range is a plausible reusable rule. A shipping code whose interpretation depends on country, customer status, and current service availability may need a richer model than one domain check.
Centralization also centralizes change impact. Before defining the domain, list the consumers and identify who owns the meaning of the value. A convenient local rule should not quietly become a dependency across unrelated applications.
Keep the underlying type deliberate
The base type still determines storage and much of the value’s behavior. A domain over numeric data does not remove precision decisions, and a domain over text does not remove collation or normalization concerns.
CREATE DOMAIN bounded_percentage AS numeric
CHECK (VALUE >= 0 AND VALUE <= 100);
CREATE TABLE survey_result (
result_id bigint PRIMARY KEY,
score bounded_percentage NOT NULL
);This example separates the reusable range check from the table column’s requirement to contain a value. It is illustrative; a production numeric definition should also match the precision and scale the application requires.
Understand CHECK and unknown results
Domain check expressions use VALUE to refer to the candidate value. A check succeeds when it evaluates to true or unknown and rejects a false result.
That matters for nulls. An ordinary comparison involving null can produce unknown rather than false. A check that appears to describe a range should not be mistaken for a requirement that the column always contains a value.
Test null, boundary values, values just outside the range, and the representations produced by the real client library. Do not validate only strings manually entered in an administrator’s SQL console.
Prefer column-level NOT NULL where required
The PostgreSQL documentation recommends allowing null in domain constraints and applying NOT NULL to individual columns that require a value. Domain-level null constraints have subtleties around conversion and SQL expressions.
Outer joins can produce null results even for a column nominally using a non-null domain. Other expressions can also expose cases that do not behave like a simple fresh conversion from a base type.
Keep requiredness attached to the table’s data contract. One consumer may require a percentage, while another legitimately represents an unknown percentage. The shared domain should not force those distinct states into a single accidental policy.
Separate defaults from validation
A domain can define a default, but a column-specific default overrides it. Defaults apply when a value is omitted in the relevant insert context; they are not a replacement for checking explicitly supplied data.
An explicit null is also a different input from an omitted value. Test both paths and make sure the application does not silently turn unknown data into a plausible business value.
Use defaults only when the chosen value is semantically justified for all intended consumers. A default percentage of zero, for example, may wrongly imply a measured result when the information is simply unavailable.
Keep constraints stable for the same input
PostgreSQL assumes check conditions are immutable: the same input should keep producing the same result. A rule depending on changing external state does not fit that assumption.
Avoid using a domain check as a live lookup against another table, a remote service, the current user’s permissions, or a changing time-based policy. Some such constructions are restricted directly; others can be hidden inside user-defined functions and still undermine the model.
If a check calls a function, review both the function’s actual behavior and its change process. Labeling something immutable is not a substitute for ensuring that it really behaves that way.
Treat function changes as constraint migrations
Changing a function used by a check does not automatically make PostgreSQL revisit every stored value. Existing values can therefore become inconsistent with the new behavior without an immediate visible error.
That inconsistency may surface during dump and restore or another operation that rechecks values. The documented approach involves managing the constraint and rechecking stored data as part of the change.
Plan the migration rather than editing a shared function casually. Inventory consumers, test existing data, estimate validation work, and define rollback. A central rule can be easier to maintain only if its deployment is also centrally understood.
Keep cross-row integrity in the right mechanism
Domains describe allowed individual values. They do not replace primary keys, foreign keys, unique constraints, exclusion constraints, or transaction rules that enforce relationships across records.
For example, an identifier-shaped text domain can restrict formatting, but it does not establish that the identifier refers to an existing account. A percentage domain does not prove that several percentages add up to the required total.
Choose the mechanism that matches the invariant. Combining a domain with table constraints is normal and often clearer than trying to encode an entire data model inside one reusable value check.
Test application and tooling behavior
Client libraries, serializers, schema tools, and ORMs can handle custom types differently. Test reads, writes, nulls, errors, and schema inspection through the actual application stack.
Map constraint failures to a controlled application response without exposing internal schema details unnecessarily. Keep the database constraint as a reliable backstop rather than depending entirely on duplicate frontend validation.
Check migrations and restores in a representative environment. A development database that already contains a manually created domain can conceal a missing migration in the deployment artifact.
Name and evolve the contract clearly
Use a name that describes the value concept, not an incidental implementation. Schema qualification and ownership should follow the database’s reviewed conventions, especially when several applications share it.
Document which values are accepted, whether normalization occurs elsewhere, and which columns require non-null values. Also record how the rule changes and who approves those changes.
When a concept diverges across consumers, a new domain or separate column rule may be clearer than weakening the existing shared definition. Reuse is useful only while the shared meaning remains real.
Frequently asked questions
Is a domain a new table?
No. It is a type based on another type with optional constraints and related behavior such as defaults.
Does a CHECK automatically reject null?
Not necessarily. Check expressions that evaluate to unknown succeed, so requiredness should be handled explicitly.
Can changing a check function invalidate stored data?
Yes. Existing values are not automatically rechecked merely because the function’s behavior changes. Treat that change as a schema migration.
Consult the CREATE DOMAIN documentation for constraint semantics, null caveats, and recommended maintenance behavior.
For a complementary workflow, read PostgreSQL Constraints: Validate the Real Invariant.
