Defines a new domain.
CREATE DOMAIN <name> [AS] <data_type> [DEFAULT <expression>]
[CONSTRAINT <constraint_name>
| NOT NULL | NULL
| CHECK (<expression>) [...]]
CREATE DOMAIN
creates a new domain. A domain is essentially a data type with optional constraints (restrictions on the allowed set of values). The user who defines a domain becomes its owner. The domain name must be unique among the data types and domains existing in its schema.
Domains are useful for abstracting common constraints on fields into a single location for maintenance. For example, several tables might contain email address columns, all requiring the same CHECK
constraint to verify the address syntax. It is easier to define a domain rather than setting up a column constraint for each table that has an email column.
CHECK
clauses specify integrity constraints or tests which values of the domain must satisfy. Each constraint must be an expression producing a Boolean result. It should use the key word
VALUE
to refer to the value being tested. Currently,
CHECK
expressions cannot contain subqueries nor refer to variables other than
VALUE
.
Create the us_zip_code
data type. A regular expression test is used to verify that the value looks like a valid US zip code.
CREATE DOMAIN us_zip_code AS TEXT CHECK
( VALUE ~ '^\\d{5}$' OR VALUE ~ '^\\d{5}-\\d{4}$' );
CREATE DOMAIN
conforms to the SQL standard.
Parent topic: SQL Command Reference