1CREATE DOMAIN(7) PostgreSQL 14.3 Documentation CREATE DOMAIN(7)
2
6 CREATE_DOMAIN - define a new domain
7
9 CREATE DOMAIN name [ AS ] data_type
10 [ COLLATE collation ]
11 [ DEFAULT expression ]
12 [ constraint [ ... ] ]
13 where constraint is:
15 [ CONSTRAINT constraint_name ]
17 { NOT NULL | NULL | CHECK (expression) }
18
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 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 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 To be able to create a domain, you must have USAGE privilege on the
36 underlying type.
37
39 name
40 The name (optionally schema-qualified) of a domain to be created.
41 data_type
43 The underlying data type of the domain. This can include array
44 specifiers.
45 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 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 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 CONSTRAINT constraint_name
65 An optional name for a constraint. If not specified, the system
66 generates a name.
67 NOT NULL
69 Values of this domain are prevented from being null (but see notes
70 below).
71 NULL
73 Values of this domain are allowed to be null. This is the default.
74 This clause is only intended for compatibility with nonstandard SQL
76 databases. Its use is discouraged in new applications.
77 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 Currently, CHECK expressions cannot contain subqueries nor refer to
88 variables other than VALUE.
89 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
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 INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
103 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 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 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 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
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 CREATE DOMAIN us_postal_code AS TEXT
138 CHECK(
139 VALUE ~ '^\d{5}$'
140 OR VALUE ~ '^\d{5}-\d{4}$'
141 );
142 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
153 The command CREATE DOMAIN conforms to the SQL standard.
154
156 ALTER DOMAIN (ALTER_DOMAIN(7)), DROP DOMAIN (DROP_DOMAIN(7))
157PostgreSQL 14.3 2022 CREATE DOMAIN(7)