2 $PostgreSQL: pgsql/doc/src/sgml/ref/create_domain.sgml,v 1.20 2005/01/04 00:39:53 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATEDOMAIN">
8 <refentrytitle id="sql-createdomain-title">CREATE DOMAIN</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>CREATE DOMAIN</refname>
14 <refpurpose>define a new domain</refpurpose>
17 <indexterm zone="sql-createdomain">
18 <primary>CREATE DOMAIN</primary>
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> [ ... ] ]
27 where <replaceable class="PARAMETER">constraint</replaceable> is:
29 [ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
30 { NOT NULL | NULL | CHECK (<replaceable class="PARAMETER">expression</replaceable>) }
35 <title>Description</title>
38 <command>CREATE DOMAIN</command> creates a new data domain. The
39 user who defines a domain becomes its owner.
43 If a schema name is given (for example, <literal>CREATE DOMAIN
44 myschema.mydomain ...</>) then the domain is created in the
45 specified schema. Otherwise it is created in the current schema.
46 The domain name must be unique among the types and domains existing
51 Domains are useful for abstracting common fields between tables into
52 a single location for maintenance. For example, an email address column may be used
53 in several tables, all with the same properties. Define a domain and
54 use that rather than setting up each table's constraints individually.
59 <title>Parameters</title>
63 <term><replaceable class="parameter">name</replaceable></term>
66 The name (optionally schema-qualified) of a domain to be created.
72 <term><replaceable class="PARAMETER">data_type</replaceable></term>
75 The underlying data type of the domain. This may include array
82 <term><literal>DEFAULT <replaceable>expression</replaceable></literal></term>
86 The <literal>DEFAULT</> clause specifies a default value for
87 columns of the domain data type. The value is any
88 variable-free expression (but subqueries are not allowed).
89 The data type of the default expression must match the data
90 type of the domain. If no default value is specified, then
91 the default value is the null value.
95 The default expression will be used in any insert operation
96 that does not specify a value for the column. If a default
97 value is defined for a particular column, it overrides any
98 default associated with the domain. In turn, the domain
99 default overrides any default value associated with the
100 underlying data type.
106 <term><literal>CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable></literal></term>
109 An optional name for a constraint. If not specified,
110 the system generates a name.
116 <term><literal>NOT NULL</></term>
119 Values of this domain are not allowed to be null.
125 <term><literal>NULL</></term>
128 Values of this domain are allowed to be null. This is the default.
132 This clause is only intended for compatibility with
133 nonstandard SQL databases. Its use is discouraged in new
140 <term><literal>CHECK (<replaceable class="PARAMETER">expression</replaceable>)</literal></term>
143 <literal>CHECK</> clauses specify integrity constraints or tests
144 which values of the domain must satisfy.
145 Each constraint must be an expression
146 producing a Boolean result. It should use the name <literal>VALUE</>
147 to refer to the value being tested.
151 Currently, <literal>CHECK</literal> expressions cannot contain
152 subqueries nor refer to variables other than <literal>VALUE</>.
160 <title>Examples</title>
163 This example creates the <type>us_postal_code</type> data type and
164 then uses the type in a table definition. A regular expression test
165 is used to verify that the value looks like a valid US postal code.
168 CREATE DOMAIN us_postal_code AS TEXT
171 OR VALUE ~ '^\d{5}-\d{4}$'
174 CREATE TABLE us_snail_addy (
175 address_id SERIAL NOT NULL PRIMARY KEY
176 , street1 TEXT NOT NULL
180 , postal us_postal_code NOT NULL
186 <refsect1 id="SQL-CREATEDOMAIN-compatibility">
187 <title>Compatibility</title>
190 The command <command>CREATE DOMAIN</command> conforms to the SQL
195 <refsect1 id="SQL-CREATEDOMAIN-see-also">
196 <title>See Also</title>
198 <simplelist type="inline">
199 <member><xref linkend="sql-alterdomain" endterm="sql-alterdomain-title"></member>
200 <member><xref linkend="sql-dropdomain" endterm="sql-dropdomain-title"></member>
207 <!-- Keep this comment at the end of the file
212 sgml-minimize-attributes:nil
213 sgml-always-quote-attributes:t
216 sgml-parent-document:nil
217 sgml-default-dtd-file:"../reference.ced"
218 sgml-exposed-tags:nil
219 sgml-local-catalogs:"/usr/lib/sgml/catalog"
220 sgml-local-ecat-files:nil