2
$PostgreSQL: pgsql/doc/src/sgml/ref/create_domain.sgml,v 1.20.4.1 2005-05-02 01:56:16 neilc 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