1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>CREATE DOMAIN</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-createdatabase.html" title="CREATE DATABASE">
<link rel="next" href="sql-createfunction.html" title="CREATE FUNCTION">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-createdomain"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>CREATE DOMAIN — define a new domain</p>
</div>
<a name="id754762"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">CREATE DOMAIN <em class="replaceable"><code>name</code></em> [AS] <em class="replaceable"><code>data_type</code></em>
[ DEFAULT <em class="replaceable"><code>expression</code></em> ]
[ <em class="replaceable"><code>constraint</code></em> [ ... ] ]
where <em class="replaceable"><code>constraint</code></em> is:
[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
{ NOT NULL | NULL | CHECK (<em class="replaceable"><code>expression</code></em>) }</pre>
</div>
<div class="refsect1" lang="en">
<a name="id754825"></a><h2>Description</h2>
<p> <code class="command">CREATE DOMAIN</code> creates a new data domain. The
user who defines a domain becomes its owner.
</p>
<p> If a schema name is given (for example, <code class="literal">CREATE DOMAIN
myschema.mydomain ...</code>) then the domain is created in the
specified schema. Otherwise it is created in the current schema.
The domain name must be unique among the types and domains existing
in its schema.
</p>
<p> Domains are useful for abstracting common fields between tables
into a single location for maintenance. For example, an email address
column may be used in several tables, all with the same properties.
Define a domain and use that rather than setting up each table's
constraints individually.
</p>
<div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Caution</h3>
<p> At present, declaring a function result value as a domain
is pretty dangerous, because none of the procedural languages enforce domain constraints
on their results. You'll need to make sure that the function code itself
respects the constraints. In <span class="application">PL/pgSQL</span>, one possible
workaround is to explicitly cast the result value to the domain type
when you return it. <span class="application">PL/pgSQL</span> does not enforce domain
constraints for local variables within functions, either.
</p>
</div>
</div>
<div class="refsect1" lang="en">
<a name="id754886"></a><h2>Parameters</h2>
<div class="variablelist"><dl>
<dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt>
<dd><p> The name (optionally schema-qualified) of a domain to be created.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt>
<dd><p> The underlying data type of the domain. This may include array
specifiers.
</p></dd>
<dt><span class="term"><code class="literal">DEFAULT <em class="replaceable"><code>expression</code></em></code></span></dt>
<dd>
<p> The <code class="literal">DEFAULT</code> clause specifies a default value for
columns of the domain data type. The value is any
variable-free expression (but subqueries are not allowed).
The data type of the default expression must match the data
type of the domain. If no default value is specified, then
the default value is the null value.
</p>
<p> The default expression will be used in any insert operation
that does not specify a value for the column. If a default
value is defined for a particular column, it overrides any
default associated with the domain. In turn, the domain
default overrides any default value associated with the
underlying data type.
</p>
</dd>
<dt><span class="term"><code class="literal">CONSTRAINT <em class="replaceable"><code>constraint_name</code></em></code></span></dt>
<dd><p> An optional name for a constraint. If not specified,
the system generates a name.
</p></dd>
<dt><span class="term"><code class="literal">NOT NULL</code></span></dt>
<dd><p> Values of this domain are not allowed to be null.
</p></dd>
<dt><span class="term"><code class="literal">NULL</code></span></dt>
<dd>
<p> Values of this domain are allowed to be null. This is the default.
</p>
<p> This clause is only intended for compatibility with
nonstandard SQL databases. Its use is discouraged in new
applications.
</p>
</dd>
<dt><span class="term"><code class="literal">CHECK (<em class="replaceable"><code>expression</code></em>)</code></span></dt>
<dd>
<p> <code class="literal">CHECK</code> clauses specify integrity constraints or tests
which values of the domain must satisfy.
Each constraint must be an expression
producing a Boolean result. It should use the name <code class="literal">VALUE</code>
to refer to the value being tested.
</p>
<p> Currently, <code class="literal">CHECK</code> expressions cannot contain
subqueries nor refer to variables other than <code class="literal">VALUE</code>.
</p>
</dd>
</dl></div>
</div>
<div class="refsect1" lang="en">
<a name="id755053"></a><h2>Examples</h2>
<p> This example creates the <code class="type">us_postal_code</code> data type and
then uses the type in a table definition. A regular expression test
is used to verify that the value looks like a valid US postal code.
</p>
<pre class="programlisting">CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^\\d{5}$'
OR VALUE ~ '^\\d{5}-\\d{4}$'
);
CREATE TABLE us_snail_addy (
address_id SERIAL NOT NULL PRIMARY KEY
, street1 TEXT NOT NULL
, street2 TEXT
, street3 TEXT
, city TEXT NOT NULL
, postal us_postal_code NOT NULL
);</pre>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="sql-createdomain-compatibility"></a><h2>Compatibility</h2>
<p> The command <code class="command">CREATE DOMAIN</code> conforms to the SQL
standard.
</p>
</div>
<div class="refsect1" lang="en">
<a name="sql-createdomain-see-also"></a><h2>See Also</h2>
<span class="simplelist"><a href="sql-alterdomain.html">ALTER DOMAIN</a>, <a href="sql-dropdomain.html">DROP DOMAIN</a></span>
</div>
</div></body>
</html>
|
