1CREATE DOMAIN(7) PostgreSQL 15.4 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 domain has the same collation behavior as its underlying data
49 type. The underlying type must be collatable if COLLATE is
50 specified.
51 DEFAULT expression
53 The DEFAULT clause specifies a default value for columns of the
54 domain data type. The value is any variable-free expression (but
55 subqueries are not allowed). The data type of the default
56 expression must match the data type of the domain. If no default
57 value is specified, then the default value is the null value.
58 The default expression will be used in any insert operation that
60 does not specify a value for the column. If a default value is
61 defined for a particular column, it overrides any default
62 associated with the domain. In turn, the domain default overrides
63 any default value associated with the underlying data type.
64 CONSTRAINT constraint_name
66 An optional name for a constraint. If not specified, the system
67 generates a name.
68 NOT NULL
70 Values of this domain are prevented from being null (but see notes
71 below).
72 NULL
74 Values of this domain are allowed to be null. This is the default.
75 This clause is only intended for compatibility with nonstandard SQL
77 databases. Its use is discouraged in new applications.
78 CHECK (expression)
80 CHECK clauses specify integrity constraints or tests which values
81 of the domain must satisfy. Each constraint must be an expression
82 producing a Boolean result. It should use the key word VALUE to
83 refer to the value being tested. Expressions evaluating to TRUE or
84 UNKNOWN succeed. If the expression produces a FALSE result, an
85 error is reported and the value is not allowed to be converted to
86 the domain type.
87 Currently, CHECK expressions cannot contain subqueries nor refer to
89 variables other than VALUE.
90 When a domain has multiple CHECK constraints, they will be tested
92 in alphabetical order by name. (PostgreSQL versions before 9.5 did
93 not honor any particular firing order for CHECK constraints.)
94
96 Domain constraints, particularly NOT NULL, are checked when converting
97 a value to the domain type. It is possible for a column that is
98 nominally of the domain type to read as null despite there being such a
99 constraint. For example, this can happen in an outer-join query, if the
100 domain column is on the nullable side of the outer join. A more subtle
101 example is
102 INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false));
104 The empty scalar sub-SELECT will produce a null value that is
106 considered to be of the domain type, so no further constraint checking
107 is applied to it, and the insertion will succeed.
108 It is very difficult to avoid such problems, because of SQL's general
110 assumption that a null value is a valid value of every data type. Best
111 practice therefore is to design a domain's constraints so that a null
112 value is allowed, and then to apply column NOT NULL constraints to
113 columns of the domain type as needed, rather than directly to the
114 domain type.
115 PostgreSQL assumes that CHECK constraints' conditions are immutable,
117 that is, they will always give the same result for the same input
118 value. This assumption is what justifies examining CHECK constraints
119 only when a value is first converted to be of a domain type, and not at
120 other times. (This is essentially the same as the treatment of table
121 CHECK constraints, as described in Section 5.4.1.)
122 An example of a common way to break this assumption is to reference a
124 user-defined function in a CHECK expression, and then change the
125 behavior of that function. PostgreSQL does not disallow that, but it
126 will not notice if there are stored values of the domain type that now
127 violate the CHECK constraint. That would cause a subsequent database
128 dump and restore to fail. The recommended way to handle such a change
129 is to drop the constraint (using ALTER DOMAIN), adjust the function
130 definition, and re-add the constraint, thereby rechecking it against
131 stored data.
132
134 This example creates the us_postal_code data type and then uses the
135 type in a table definition. A regular expression test is used to verify
136 that the value looks like a valid US postal code:
137 CREATE DOMAIN us_postal_code AS TEXT
139 CHECK(
140 VALUE ~ '^\d{5}$'
141 OR VALUE ~ '^\d{5}-\d{4}$'
142 );
143 CREATE TABLE us_snail_addy (
145 address_id SERIAL PRIMARY KEY,
146 street1 TEXT NOT NULL,
147 street2 TEXT,
148 street3 TEXT,
149 city TEXT NOT NULL,
150 postal us_postal_code NOT NULL
151 );
152
154 The command CREATE DOMAIN conforms to the SQL standard.
155
157 ALTER DOMAIN (ALTER_DOMAIN(7)), DROP DOMAIN (DROP_DOMAIN(7))
158PostgreSQL 15.4 2023 CREATE DOMAIN(7)