CREATE_DOMAIN(7)

1CREATE DOMAIN(7)         PostgreSQL 11.6 Documentation        CREATE DOMAIN(7)
2
3
4

NAME

6       CREATE_DOMAIN - define a new domain
7

SYNOPSIS

9       CREATE DOMAIN name [ AS ] data_type
10           [ COLLATE collation ]
11           [ DEFAULT expression ]
12           [ constraint [ ... ] ]
13
14       where constraint is:
15
16       [ CONSTRAINT constraint_name ]
17       { NOT NULL | NULL | CHECK (expression) }
18

DESCRIPTION

20       CREATE DOMAIN creates a new domain. A domain is essentially a data type
21       with optional constraints (restrictions on the allowed set of values).
22       The user who defines a domain becomes its owner.
23
24       If a schema name is given (for example, CREATE DOMAIN myschema.mydomain
25       ...) then the domain is created in the specified schema. Otherwise it
26       is created in the current schema. The domain name must be unique among
27       the types and domains existing in its schema.
28
29       Domains are useful for abstracting common constraints on fields into a
30       single location for maintenance. For example, several tables might
31       contain email address columns, all requiring the same CHECK constraint
32       to verify the address syntax. Define a domain rather than setting up
33       each table's constraint individually.
34
35       To be able to create a domain, you must have USAGE privilege on the
36       underlying type.
37

PARAMETERS

39       name
40           The name (optionally schema-qualified) of a domain to be created.
41
42       data_type
43           The underlying data type of the domain. This can include array
44           specifiers.
45
46       collation
47           An optional collation for the domain. If no collation is specified,
48           the underlying data type's default collation is used. The
49           underlying type must be collatable if COLLATE is specified.
50
51       DEFAULT expression
52           The DEFAULT clause specifies a default value for columns of the
53           domain data type. The value is any variable-free expression (but
54           subqueries are not allowed). The data type of the default
55           expression must match the data type of the domain. If no default
56           value is specified, then the default value is the null value.
57
58           The default expression will be used in any insert operation that
59           does not specify a value for the column. If a default value is
60           defined for a particular column, it overrides any default
61           associated with the domain. In turn, the domain default overrides
62           any default value associated with the underlying data type.
63
64       CONSTRAINT constraint_name
65           An optional name for a constraint. If not specified, the system
66           generates a name.
67
68       NOT NULL
69           Values of this domain are prevented from being null (but see notes
70           below).
71
72       NULL
73           Values of this domain are allowed to be null. This is the default.
74
75           This clause is only intended for compatibility with nonstandard SQL
76           databases. Its use is discouraged in new applications.
77
78       CHECK (expression)
79           CHECK clauses specify integrity constraints or tests which values
80           of the domain must satisfy. Each constraint must be an expression
81           producing a Boolean result. It should use the key word VALUE to
82           refer to the value being tested. Expressions evaluating to TRUE or
83           UNKNOWN succeed. If the expression produces a FALSE result, an
84           error is reported and the value is not allowed to be converted to
85           the domain type.
86
87           Currently, CHECK expressions cannot contain subqueries nor refer to
88           variables other than VALUE.
89
90           When a domain has multiple CHECK constraints, they will be tested
91           in alphabetical order by name. (PostgreSQL versions before 9.5 did
92           not honor any particular firing order for CHECK constraints.)
93

NOTES

95       Domain constraints, particularly NOT NULL, are checked when converting
96       a value to the domain type. It is possible for a column that is
97       nominally of the domain type to read as null despite there being such a
98       constraint. For example, this can happen in an outer-join query, if the
99       domain column is on the nullable side of the outer join. A more subtle
100       example is
101
102           INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
103
104       The empty scalar sub-SELECT will produce a null value that is
105       considered to be of the domain type, so no further constraint checking
106       is applied to it, and the insertion will succeed.
107
108       It is very difficult to avoid such problems, because of SQL's general
109       assumption that a null value is a valid value of every data type. Best
110       practice therefore is to design a domain's constraints so that a null
111       value is allowed, and then to apply column NOT NULL constraints to
112       columns of the domain type as needed, rather than directly to the
113       domain type.
114

EXAMPLES

116       This example creates the us_postal_code data type and then uses the
117       type in a table definition. A regular expression test is used to verify
118       that the value looks like a valid US postal code:
119
120           CREATE DOMAIN us_postal_code AS TEXT
121           CHECK(
122              VALUE ~ '^\d{5}$'
123           OR VALUE ~ '^\d{5}-\d{4}$'
124           );
125
126           CREATE TABLE us_snail_addy (
127             address_id SERIAL PRIMARY KEY,
128             street1 TEXT NOT NULL,
129             street2 TEXT,
130             street3 TEXT,
131             city TEXT NOT NULL,
132             postal us_postal_code NOT NULL
133           );
134

COMPATIBILITY

136       The command CREATE DOMAIN conforms to the SQL standard.
137