Defines a new domain.
CREATE DOMAIN <name> [AS] <data_type> [DEFAULT <expression>]
[ COLLATE <collation> ]
[ 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.
If a schema name is given (for example, CREATE DOMAIN myschema.mydomain ...
) then the domain is created in the specified schema. Otherwise it is created in the current 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.
To be able to create a domain, you must have USAGE
privilege on the underlying type.
COLLATE
is specified.
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false))
.
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 Commands