1CREATE DOMAIN(7)         PostgreSQL 14.3 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
115       PostgreSQL assumes that CHECK constraints' conditions are immutable,
116       that is, they will always give the same result for the same input
117       value. This assumption is what justifies examining CHECK constraints
118       only when a value is first converted to be of a domain type, and not at
119       other times. (This is essentially the same as the treatment of table
120       CHECK constraints, as described in Section 5.4.1.)
121
122       An example of a common way to break this assumption is to reference a
123       user-defined function in a CHECK expression, and then change the
124       behavior of that function.  PostgreSQL does not disallow that, but it
125       will not notice if there are stored values of the domain type that now
126       violate the CHECK constraint. That would cause a subsequent database
127       dump and reload to fail. The recommended way to handle such a change is
128       to drop the constraint (using ALTER DOMAIN), adjust the function
129       definition, and re-add the constraint, thereby rechecking it against
130       stored data.
131

EXAMPLES

133       This example creates the us_postal_code data type and then uses the
134       type in a table definition. A regular expression test is used to verify
135       that the value looks like a valid US postal code:
136
137           CREATE DOMAIN us_postal_code AS TEXT
138           CHECK(
139              VALUE ~ '^\d{5}$'
140           OR VALUE ~ '^\d{5}-\d{4}$'
141           );
142
143           CREATE TABLE us_snail_addy (
144             address_id SERIAL PRIMARY KEY,
145             street1 TEXT NOT NULL,
146             street2 TEXT,
147             street3 TEXT,
148             city TEXT NOT NULL,
149             postal us_postal_code NOT NULL
150           );
151

COMPATIBILITY

153       The command CREATE DOMAIN conforms to the SQL standard.
154

SEE ALSO

156       ALTER DOMAIN (ALTER_DOMAIN(7)), DROP DOMAIN (DROP_DOMAIN(7))
157
158
159
160PostgreSQL 14.3                      2022                     CREATE DOMAIN(7)
Impressum