Use CREATE DOMAIN to define a custom data type with optional constraints — such as DEFAULT, NOT NULL, and CHECK — that can be reused across multiple tables. The user who defines a domain becomes its owner.
Synopsis
CREATE DOMAIN name [ AS ] data_type
[ COLLATE collation ]
[ DEFAULT expression ]
[ constraint [ ... ] ]where constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }Parameters
Command parameters
| Parameter | Description |
|---|---|
name | The name of the domain to create, optionally schema-qualified (for example, myschema.mydomain). If no schema is specified, the domain is created in the current schema. The name must be unique among all types and domains in the schema. |
data_type | The underlying data type of the domain. Array specifiers are supported. |
collation | An optional collation for the domain. If omitted, the domain inherits the collation behavior of its underlying data type. The underlying type must be collatable if COLLATE is specified. |
DEFAULT expression | A default value for columns of this domain type. Must be a variable-free expression; subqueries are not allowed. The expression's data type must match the domain's data type. If omitted, the default is NULL. |
Constraint parameters
| Parameter | Description |
|---|---|
CONSTRAINT constraint_name | An optional name for the constraint. If omitted, the system generates one. |
NOT NULL | Prevents domain values from being null. See Usage notes for edge cases. |
NULL | Allows domain values to be null. This is the default. Use only for compatibility with nonstandard SQL databases; avoid in new applications. |
CHECK (expression) | Defines an integrity constraint that domain values must satisfy. The expression must produce a Boolean result and must use the keyword VALUE to refer to the value being tested. Expressions evaluating to TRUE or UNKNOWN pass. A FALSE result raises an error and rejects the value. Subqueries and references to variables other than VALUE are not allowed. When a domain has multiple CHECK constraints, they are evaluated in alphabetical order by name. |
How the default value works
The DEFAULT expression takes effect in any INSERT that omits a value for the column. The priority order is:
Column-level default (highest priority)
Domain default
Underlying data type default
Usage notes
Domains are useful for centralizing constraints. Instead of repeating the same CHECK constraint across multiple tables, define it once in a domain and use that domain as the column type.
NOT NULL has an edge case with outer joins. Domain constraints, including NOT NULL, are checked when a value is converted to the domain type — not at every read. A column of domain type can still return NULL in an outer-join query if the domain column is on the nullable side. For example:
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));The empty scalar subquery produces NULL, which is treated as a valid domain value. No constraint check is applied, and the INSERT succeeds.
Because SQL treats NULL as a valid value for every data type, it is difficult to prevent this completely. The recommended approach is to allow NULL in domain constraints and apply NOT NULL at the column level instead.
CHECK constraints assume immutability. PolarDB evaluates CHECK constraints only when a value is first converted to the domain type, relying on the assumption that the constraint expression always returns the same result for the same input.
If you define a CHECK expression that calls a user-defined function, and then change that function's behavior, stored domain values may silently violate the constraint — causing a subsequent database dump and restore to fail. To fix this, drop the constraint, update the function, and re-add the constraint so PolarDB rechecks all stored values:
ALTER DOMAIN your_domain DROP CONSTRAINT your_constraint;
-- Update the function definition here
ALTER DOMAIN your_domain ADD CONSTRAINT your_constraint CHECK (...);Privilege requirement. To create a domain, you must have USAGE privilege on the underlying type.
Example
The following example creates a us_postal_code domain that accepts only valid US postal code formats, then uses it in a table definition.
CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);
CREATE TABLE us_snail_addy (
address_id SERIAL PRIMARY KEY,
street1 TEXT NOT NULL,
street2 TEXT,
street3 TEXT,
city TEXT NOT NULL,
postal us_postal_code NOT NULL
);The regular expression validates that postal contains either a 5-digit code or a 5+4-digit code. Applying NOT NULL at the column level (rather than in the domain) avoids the outer-join edge case described above.
What's next
ALTER DOMAIN: Modify an existing domain — add or drop constraints, change the default value, or rename the domain.
DROP DOMAIN: Remove a domain from the schema.