doc/src/sgml/ref/create_domain.sgml

   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 would be <literal>CHECK (VALUE IS NOT
 287    NULL)</literal>.  However, per <xref linkend="sql-createdomain-notes"/>,
 288    such constraints are best avoided in practice anyway.)  The
 289    <literal>NULL</literal> <quote>constraint</quote> is a
 290    <productname>PostgreSQL</productname> extension (see also <xref
 291    linkend="sql-createtable-compatibility"/>).
 292   </para>
 293  </refsect1>
 294
 295  <refsect1 id="sql-createdomain-see-also">
 296   <title>See Also</title>
 297
 298   <simplelist type="inline">
 299    <member><xref linkend="sql-alterdomain"/></member>
 300    <member><xref linkend="sql-dropdomain"/></member>
 301   </simplelist>
 302  </refsect1>
 303
 304 </refentry>