doc/src/sgml/ref/create_domain.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATEDOMAIN">
   7  <refmeta>
   8   <refentrytitle id="sql-createdomain-title">CREATE DOMAIN</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>CREATE DOMAIN</refname>
  14   <refpurpose>define a new domain</refpurpose>
  15  </refnamediv>
  16
  17  <indexterm zone="sql-createdomain">
  18   <primary>CREATE DOMAIN</primary>
  19  </indexterm>
  20
  21  <refsynopsisdiv>
  22 <synopsis>
  23 CREATE DOMAIN <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable>
  24     [ DEFAULT <replaceable>expression</> ]
  25     [ <replaceable class="PARAMETER">constraint</replaceable> [ ... ] ]
  26
  27 where <replaceable class="PARAMETER">constraint</replaceable> is:
  28
  29 [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
  30 { NOT NULL | NULL | CHECK (<replaceable class="PARAMETER">expression</replaceable>) }
  31 </synopsis>
  32  </refsynopsisdiv>
  33
  34  <refsect1>
  35   <title>Description</title>
  36
  37   <para>
  38    <command>CREATE DOMAIN</command> creates a new domain.  A domain is
  39    essentially a data type with optional constraints (restrictions on
  40    the allowed set of values).
  41    The user who defines a domain becomes its owner.
  42   </para>
  43
  44   <para>
  45    If a schema name is given (for example, <literal>CREATE DOMAIN
  46    myschema.mydomain ...</>) then the domain is created in the
  47    specified schema.  Otherwise it is created in the current schema.
  48    The domain name must be unique among the types and domains existing
  49    in its schema.
  50   </para>
  51
  52   <para>
  53    Domains are useful for abstracting common constraints on fields into
  54    a single location for maintenance.  For example, several tables might
  55    contain email address columns, all requiring the same CHECK constraint
  56    to verify the address syntax.
  57    Define a domain rather than setting up each table's constraint
  58    individually.
  59   </para>
  60  </refsect1>
  61
  62  <refsect1>
  63   <title>Parameters</title>
  64
  65     <variablelist>
  66      <varlistentry>
  67       <term><replaceable class="parameter">name</replaceable></term>
  68       <listitem>
  69        <para>
  70         The name (optionally schema-qualified) of a domain to be created.
  71        </para>
  72       </listitem>
  73      </varlistentry>
  74
  75      <varlistentry>
  76       <term><replaceable class="PARAMETER">data_type</replaceable></term>
  77       <listitem>
  78        <para>
  79         The underlying data type of the domain. This can include array
  80         specifiers.
  81        </para>
  82       </listitem>
  83      </varlistentry>
  84
  85      <varlistentry>
  86       <term><literal>DEFAULT <replaceable>expression</replaceable></literal></term>
  87
  88       <listitem>
  89        <para>
  90         The <literal>DEFAULT</> clause specifies a default value for
  91         columns of the domain data type.  The value is any
  92         variable-free expression (but subqueries are not allowed).
  93         The data type of the default expression must match the data
  94         type of the domain.  If no default value is specified, then
  95         the default value is the null value.
  96        </para>
  97
  98        <para>
  99         The default expression will be used in any insert operation
 100         that does not specify a value for the column.  If a default
 101         value is defined for a particular column, it overrides any
 102         default associated with the domain.  In turn, the domain
 103         default overrides any default value associated with the
 104         underlying data type.
 105        </para>
 106       </listitem>
 107      </varlistentry>
 108
 109      <varlistentry>
 110       <term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term>
 111       <listitem>
 112        <para>
 113         An optional name for a constraint.  If not specified,
 114         the system generates a name.
 115        </para>
 116       </listitem>
 117      </varlistentry>
 118
 119      <varlistentry>
 120       <term><literal>NOT NULL</></term>
 121       <listitem>
 122        <para>
 123         Values of this domain are not allowed to be null.
 124        </para>
 125       </listitem>
 126      </varlistentry>
 127
 128      <varlistentry>
 129       <term><literal>NULL</></term>
 130       <listitem>
 131        <para>
 132         Values of this domain are allowed to be null.  This is the default.
 133        </para>
 134
 135        <para>
 136         This clause is only intended for compatibility with
 137         nonstandard SQL databases.  Its use is discouraged in new
 138         applications.
 139        </para>
 140       </listitem>
 141      </varlistentry>
 142
 143    <varlistentry>
 144     <term><literal>CHECK (<replaceable class="PARAMETER">expression</replaceable>)</literal></term>
 145     <listitem>
 146      <para>
 147       <literal>CHECK</> clauses specify integrity constraints or tests
 148       which values of the domain must satisfy.
 149       Each constraint must be an expression
 150       producing a Boolean result.  It should use the key word <literal>VALUE</>
 151       to refer to the value being tested.
 152      </para>
 153
 154      <para>
 155       Currently, <literal>CHECK</literal> expressions cannot contain
 156       subqueries nor refer to variables other than <literal>VALUE</>.
 157      </para>
 158     </listitem>
 159    </varlistentry>
 160   </variablelist>
 161  </refsect1>
 162
 163  <refsect1>
 164   <title>Examples</title>
 165
 166   <para>
 167    This example creates the <type>us_postal_code</type> data type and
 168    then uses the type in a table definition.  A regular expression test
 169    is used to verify that the value looks like a valid US postal code:
 170
 171 <programlisting>
 172 CREATE DOMAIN us_postal_code AS TEXT
 173 CHECK(
 174    VALUE ~ '^\\d{5}$'
 175 OR VALUE ~ '^\\d{5}-\\d{4}$'
 176 );
 177
 178 CREATE TABLE us_snail_addy (
 179   address_id SERIAL PRIMARY KEY,
 180   street1 TEXT NOT NULL,
 181   street2 TEXT,
 182   street3 TEXT,
 183   city TEXT NOT NULL,
 184   postal us_postal_code NOT NULL
 185 );
 186 </programlisting>
 187   </para>
 188  </refsect1>
 189
 190  <refsect1 id="SQL-CREATEDOMAIN-compatibility">
 191   <title>Compatibility</title>
 192
 193   <para>
 194    The command <command>CREATE DOMAIN</command> conforms to the SQL
 195    standard.
 196   </para>
 197  </refsect1>
 198
 199  <refsect1 id="SQL-CREATEDOMAIN-see-also">
 200   <title>See Also</title>
 201
 202   <simplelist type="inline">
 203    <member><xref linkend="sql-alterdomain" endterm="sql-alterdomain-title"></member>
 204    <member><xref linkend="sql-dropdomain" endterm="sql-dropdomain-title"></member>
 205   </simplelist>
 206  </refsect1>
 207
 208 </refentry>