| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/create_domain.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-createdomain"> |
| 7 |
<indexterm zone="sql-createdomain"> |
| 8 |
<primary>CREATE DOMAIN</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>CREATE DOMAIN</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>CREATE DOMAIN</refname> |
| 19 |
<refpurpose>define a new domain</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> |
| 25 |
[ COLLATE <replaceable>collation</replaceable> ] |
| 26 |
[ DEFAULT <replaceable>expression</replaceable> ] |
| 27 |
[ <replaceable class="parameter">domain_constraint</replaceable> [ ... ] ] |
| 28 |
|
| 29 |
<phrase>where <replaceable class="parameter">domain_constraint</replaceable> is:</phrase> |
| 30 |
|
| 31 |
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] |
| 32 |
{ NOT NULL | NULL | CHECK (<replaceable class="parameter">expression</replaceable>) } |
| 33 |
</synopsis> |
| 34 |
</refsynopsisdiv> |
| 35 |
|
| 36 |
<refsect1> |
| 37 |
<title>Description</title> |
| 38 |
|
| 39 |
<para> |
| 40 |
<command>CREATE DOMAIN</command> creates a new domain. A domain is |
| 41 |
essentially a data type with optional constraints (restrictions on |
| 42 |
the allowed set of values). |
| 43 |
The user who defines a domain becomes its owner. |
| 44 |
</para> |
| 45 |
|
| 46 |
<para> |
| 47 |
If a schema name is given (for example, <literal>CREATE DOMAIN |
| 48 |
myschema.mydomain ...</literal>) then the domain is created in the |
| 49 |
specified schema. Otherwise it is created in the current schema. |
| 50 |
The domain name must be unique among the types and domains existing |
| 51 |
in its schema. |
| 52 |
</para> |
| 53 |
|
| 54 |
<para> |
| 55 |
Domains are useful for abstracting common constraints on fields into |
| 56 |
a single location for maintenance. For example, several tables might |
| 57 |
contain email address columns, all requiring the same CHECK constraint |
| 58 |
to verify the address syntax. |
| 59 |
Define a domain rather than setting up each table's constraint |
| 60 |
individually. |
| 61 |
</para> |
| 62 |
|
| 63 |
<para> |
| 64 |
To be able to create a domain, you must have <literal>USAGE</literal> |
| 65 |
privilege on the underlying type. |
| 66 |
</para> |
| 67 |
</refsect1> |
| 68 |
|
| 69 |
<refsect1> |
| 70 |
<title>Parameters</title> |
| 71 |
|
| 72 |
<variablelist> |
| 73 |
<varlistentry> |
| 74 |
<term><replaceable class="parameter">name</replaceable></term> |
| 75 |
<listitem> |
| 76 |
<para> |
| 77 |
The name (optionally schema-qualified) of a domain to be created. |
| 78 |
</para> |
| 79 |
</listitem> |
| 80 |
</varlistentry> |
| 81 |
|
| 82 |
<varlistentry> |
| 83 |
<term><replaceable class="parameter">data_type</replaceable></term> |
| 84 |
<listitem> |
| 85 |
<para> |
| 86 |
The underlying data type of the domain. This can include array |
| 87 |
specifiers. |
| 88 |
</para> |
| 89 |
</listitem> |
| 90 |
</varlistentry> |
| 91 |
|
| 92 |
<varlistentry> |
| 93 |
<term><replaceable>collation</replaceable></term> |
| 94 |
<listitem> |
| 95 |
<para> |
| 96 |
An optional collation for the domain. If no collation is |
| 97 |
specified, the domain has the same collation behavior as its |
| 98 |
underlying data type. |
| 99 |
The underlying type must be collatable if <literal>COLLATE</literal> |
| 100 |
is specified. |
| 101 |
</para> |
| 102 |
</listitem> |
| 103 |
</varlistentry> |
| 104 |
|
| 105 |
<varlistentry> |
| 106 |
<term><literal>DEFAULT <replaceable>expression</replaceable></literal></term> |
| 107 |
|
| 108 |
<listitem> |
| 109 |
<para> |
| 110 |
The <literal>DEFAULT</literal> clause specifies a default value for |
| 111 |
columns of the domain data type. The value is any |
| 112 |
variable-free expression (but subqueries are not allowed). |
| 113 |
The data type of the default expression must match the data |
| 114 |
type of the domain. If no default value is specified, then |
| 115 |
the default value is the null value. |
| 116 |
</para> |
| 117 |
|
| 118 |
<para> |
| 119 |
The default expression will be used in any insert operation |
| 120 |
that does not specify a value for the column. If a default |
| 121 |
value is defined for a particular column, it overrides any |
| 122 |
default associated with the domain. In turn, the domain |
| 123 |
default overrides any default value associated with the |
| 124 |
underlying data type. |
| 125 |
</para> |
| 126 |
</listitem> |
| 127 |
</varlistentry> |
| 128 |
|
| 129 |
<varlistentry> |
| 130 |
<term><literal>CONSTRAINT <replaceable class="parameter">constraint_name</replaceable></literal></term> |
| 131 |
<listitem> |
| 132 |
<para> |
| 133 |
An optional name for a constraint. If not specified, |
| 134 |
the system generates a name. |
| 135 |
</para> |
| 136 |
</listitem> |
| 137 |
</varlistentry> |
| 138 |
|
| 139 |
<varlistentry> |
| 140 |
<term><literal>NOT NULL</literal></term> |
| 141 |
<listitem> |
| 142 |
<para> |
| 143 |
Values of this domain are prevented from being null |
| 144 |
(but see notes below). |
| 145 |
</para> |
| 146 |
</listitem> |
| 147 |
</varlistentry> |
| 148 |
|
| 149 |
<varlistentry> |
| 150 |
<term><literal>NULL</literal></term> |
| 151 |
<listitem> |
| 152 |
<para> |
| 153 |
Values of this domain are allowed to be null. This is the default. |
| 154 |
</para> |
| 155 |
|
| 156 |
<para> |
| 157 |
This clause is only intended for compatibility with |
| 158 |
nonstandard SQL databases. Its use is discouraged in new |
| 159 |
applications. |
| 160 |
</para> |
| 161 |
</listitem> |
| 162 |
</varlistentry> |
| 163 |
|
| 164 |
<varlistentry> |
| 165 |
<term><literal>CHECK (<replaceable class="parameter">expression</replaceable>)</literal></term> |
| 166 |
<listitem> |
| 167 |
<para><literal>CHECK</literal> clauses specify integrity constraints or tests |
| 168 |
which values of the domain must satisfy. |
| 169 |
Each constraint must be an expression |
| 170 |
producing a Boolean result. It should use the key word <literal>VALUE</literal> |
| 171 |
to refer to the value being tested. Expressions evaluating |
| 172 |
to TRUE or UNKNOWN succeed. If the expression produces a FALSE result, |
| 173 |
an error is reported and the value is not allowed to be converted |
| 174 |
to the domain type. |
| 175 |
</para> |
| 176 |
|
| 177 |
<para> |
| 178 |
Currently, <literal>CHECK</literal> expressions cannot contain |
| 179 |
subqueries nor refer to variables other than <literal>VALUE</literal>. |
| 180 |
</para> |
| 181 |
|
| 182 |
<para> |
| 183 |
When a domain has multiple <literal>CHECK</literal> constraints, |
| 184 |
they will be tested in alphabetical order by name. |
| 185 |
(<productname>PostgreSQL</productname> versions before 9.5 did not honor any |
| 186 |
particular firing order for <literal>CHECK</literal> constraints.) |
| 187 |
</para> |
| 188 |
</listitem> |
| 189 |
</varlistentry> |
| 190 |
</variablelist> |
| 191 |
</refsect1> |
| 192 |
|
| 193 |
<refsect1 id="sql-createdomain-notes"> |
| 194 |
<title>Notes</title> |
| 195 |
|
| 196 |
<para> |
| 197 |
Domain constraints, particularly <literal>NOT NULL</literal>, are checked when |
| 198 |
converting a value to the domain type. It is possible for a column that |
| 199 |
is nominally of the domain type to read as null despite there being such |
| 200 |
a constraint. For example, this can happen in an outer-join query, if |
| 201 |
the domain column is on the nullable side of the outer join. A more |
| 202 |
subtle example is |
| 203 |
<programlisting> |
| 204 |
INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM tab WHERE false)); |
| 205 |
</programlisting> |
| 206 |
The empty scalar sub-SELECT will produce a null value that is considered |
| 207 |
to be of the domain type, so no further constraint checking is applied |
| 208 |
to it, and the insertion will succeed. |
| 209 |
</para> |
| 210 |
|
| 211 |
<para> |
| 212 |
It is very difficult to avoid such problems, because of SQL's general |
| 213 |
assumption that a null value is a valid value of every data type. Best practice |
| 214 |
therefore is to design a domain's constraints so that a null value is allowed, |
| 215 |
and then to apply column <literal>NOT NULL</literal> constraints to columns of |
| 216 |
the domain type as needed, rather than directly to the domain type. |
| 217 |
</para> |
| 218 |
|
| 219 |
<para> |
| 220 |
<productname>PostgreSQL</productname> assumes that |
| 221 |
<literal>CHECK</literal> constraints' conditions are immutable, that is, |
| 222 |
they will always give the same result for the same input value. This |
| 223 |
assumption is what justifies examining <literal>CHECK</literal> |
| 224 |
constraints only when a value is first converted to be of a domain type, |
| 225 |
and not at other times. (This is essentially the same as the treatment |
| 226 |
of table <literal>CHECK</literal> constraints, as described in |
| 227 |
<xref linkend="ddl-constraints-check-constraints"/>.) |
| 228 |
</para> |
| 229 |
|
| 230 |
<para> |
| 231 |
An example of a common way to break this assumption is to reference a |
| 232 |
user-defined function in a <literal>CHECK</literal> expression, and then |
| 233 |
change the behavior of that |
| 234 |
function. <productname>PostgreSQL</productname> does not disallow that, |
| 235 |
but it will not notice if there are stored values of the domain type that |
| 236 |
now violate the <literal>CHECK</literal> constraint. That would cause a |
| 237 |
subsequent database dump and restore to fail. The recommended way to |
| 238 |
handle such a change is to drop the constraint (using <command>ALTER |
| 239 |
DOMAIN</command>), adjust the function definition, and re-add the |
| 240 |
constraint, thereby rechecking it against stored data. |
| 241 |
</para> |
| 242 |
|
| 243 |
<para> |
| 244 |
It's also good practice to ensure that domain <literal>CHECK</literal> |
| 245 |
expressions will not throw errors. |
| 246 |
</para> |
| 247 |
</refsect1> |
| 248 |
|
| 249 |
<refsect1> |
| 250 |
<title>Examples</title> |
| 251 |
|
| 252 |
<para> |
| 253 |
This example creates the <type>us_postal_code</type> data type and |
| 254 |
then uses the type in a table definition. A regular expression test |
| 255 |
is used to verify that the value looks like a valid US postal code: |
| 256 |
|
| 257 |
<programlisting> |
| 258 |
CREATE DOMAIN us_postal_code AS TEXT |
| 259 |
CHECK( |
| 260 |
VALUE ~ '^\d{5}$' |
| 261 |
OR VALUE ~ '^\d{5}-\d{4}$' |
| 262 |
); |
| 263 |
|
| 264 |
CREATE TABLE us_snail_addy ( |
| 265 |
address_id SERIAL PRIMARY KEY, |
| 266 |
street1 TEXT NOT NULL, |
| 267 |
street2 TEXT, |
| 268 |
street3 TEXT, |
| 269 |
city TEXT NOT NULL, |
| 270 |
postal us_postal_code NOT NULL |
| 271 |
); |
| 272 |
</programlisting></para> |
| 273 |
</refsect1> |
| 274 |
|
| 275 |
<refsect1 id="sql-createdomain-compatibility"> |
| 276 |
<title>Compatibility</title> |
| 277 |
|
| 278 |
<para> |
| 279 |
The command <command>CREATE DOMAIN</command> conforms to the SQL |
| 280 |
standard. |
| 281 |
</para> |
| 282 |
|
| 283 |
<para> |
| 284 |
The syntax <literal>NOT NULL</literal> in this command is a |
| 285 |
<productname>PostgreSQL</productname> extension. (A standard-conforming |
| 286 |
way to write the same for non-composite data types would be |
| 287 |
<literal>CHECK (VALUE IS NOT |
| 288 |
NULL)</literal>. However, per <xref linkend="sql-createdomain-notes"/>, |
| 289 |
such constraints are best avoided in practice anyway.) The |
| 290 |
<literal>NULL</literal> <quote>constraint</quote> is a |
| 291 |
<productname>PostgreSQL</productname> extension (see also <xref |
| 292 |
linkend="sql-createtable-compatibility"/>). |
| 293 |
</para> |
| 294 |
</refsect1> |
| 295 |
|
| 296 |
<refsect1 id="sql-createdomain-see-also"> |
| 297 |
<title>See Also</title> |
| 298 |
|
| 299 |
<simplelist type="inline"> |
| 300 |
<member><xref linkend="sql-alterdomain"/></member> |
| 301 |
<member><xref linkend="sql-dropdomain"/></member> |
| 302 |
</simplelist> |
| 303 |
</refsect1> |
| 304 |
|
| 305 |
</refentry> |