4
4
<title>ALTER DOMAIN</title>
5
5
<link rel="stylesheet" href="stylesheet.css" type="text/css">
6
6
<link rev="made" href="pgsql-docs@postgresql.org">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.64.1">
8
<link rel="home" href="index.html" title="PostgreSQL 8.0.0beta5 Documentation">
7
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
8
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
9
9
<link rel="up" href="sql-commands.html" title="SQL Commands">
10
<link rel="previous" href="sql-alterdatabase.html" title="ALTER DATABASE">
10
<link rel="prev" href="sql-alterdatabase.html" title="ALTER DATABASE">
11
11
<link rel="next" href="sql-alterfunction.html" title="ALTER FUNCTION">
12
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
13
14
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
14
<a name="sql-alterdomain"></a><div class="titlepage">
15
<a name="sql-alterdomain"></a><div class="titlepage"></div>
18
16
<div class="refnamediv">
21
19
— change the definition of a domain
24
<a name="id2661182"></a><div class="refsynopsisdiv">
22
<a name="id741489"></a><div class="refsynopsisdiv">
26
<pre class="synopsis">ALTER DOMAIN <i class="replaceable"><tt>name</tt></i>
27
{ SET DEFAULT <i class="replaceable"><tt>expression</tt></i> | DROP DEFAULT }
28
ALTER DOMAIN <i class="replaceable"><tt>name</tt></i>
24
<pre class="synopsis">ALTER DOMAIN <em class="replaceable"><code>name</code></em>
25
{ SET DEFAULT <em class="replaceable"><code>expression</code></em> | DROP DEFAULT }
26
ALTER DOMAIN <em class="replaceable"><code>name</code></em>
29
27
{ SET | DROP } NOT NULL
30
ALTER DOMAIN <i class="replaceable"><tt>name</tt></i>
31
ADD <i class="replaceable"><tt>domain_constraint</tt></i>
32
ALTER DOMAIN <i class="replaceable"><tt>name</tt></i>
33
DROP CONSTRAINT <i class="replaceable"><tt>constraint_name</tt></i> [ RESTRICT | CASCADE ]
34
ALTER DOMAIN <i class="replaceable"><tt>name</tt></i>
35
OWNER TO <i class="replaceable"><tt>new_owner</tt></i>
28
ALTER DOMAIN <em class="replaceable"><code>name</code></em>
29
ADD <em class="replaceable"><code>domain_constraint</code></em>
30
ALTER DOMAIN <em class="replaceable"><code>name</code></em>
31
DROP CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> [ RESTRICT | CASCADE ]
32
ALTER DOMAIN <em class="replaceable"><code>name</code></em>
33
OWNER TO <em class="replaceable"><code>new_owner</code></em>
34
ALTER DOMAIN <em class="replaceable"><code>name</code></em>
35
SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
38
38
<div class="refsect1" lang="en">
39
<a name="id2661254"></a><h2>Description</h2>
40
<p> <tt class="command">ALTER DOMAIN</tt> changes the definition of an existing domain.
39
<a name="id741584"></a><h2>Description</h2>
40
<p> <code class="command">ALTER DOMAIN</code> changes the definition of an existing domain.
41
41
There are several sub-forms:
43
43
<div class="variablelist"><dl>
44
44
<dt><span class="term">SET/DROP DEFAULT</span></dt>
45
45
<dd><p> These forms set or remove the default value for a domain. Note
46
that defaults only apply to subsequent <tt class="command">INSERT</tt>
46
that defaults only apply to subsequent <code class="command">INSERT</code>
47
47
commands; they do not affect rows already in a table using the domain.
49
49
<dt><span class="term">SET/DROP NOT NULL</span></dt>
50
50
<dd><p> These forms change whether a domain is marked to allow NULL
51
values or to reject NULL values. You may only <tt class="literal">SET NOT NULL</tt>
51
values or to reject NULL values. You may only <code class="literal">SET NOT NULL</code>
52
52
when the columns using the domain contain no null values.
54
<dt><span class="term">ADD <i class="replaceable"><tt>domain_constraint</tt></i></span></dt>
54
<dt><span class="term">ADD <em class="replaceable"><code>domain_constraint</code></em></span></dt>
55
55
<dd><p> This form adds a new constraint to a domain using the same syntax as
56
56
<a href="sql-createdomain.html">CREATE DOMAIN</a>.
57
57
This will only succeed if all columns using the domain satisfy the
63
63
<dt><span class="term">OWNER</span></dt>
64
64
<dd><p> This form changes the owner of the domain to the specified user.
66
<dt><span class="term">SET SCHEMA</span></dt>
67
<dd><p> This form changes the schema of the domain. Any constraints
68
associated with the domain are moved into the new schema as well.
67
<p> You must own the domain to use <tt class="command">ALTER DOMAIN</tt>; except for
68
<tt class="command">ALTER DOMAIN OWNER</tt>, which may only be executed by a superuser.
71
<p> You must own the domain to use <code class="command">ALTER DOMAIN</code>.
72
To change the schema of a domain, you must also have
73
<code class="literal">CREATE</code> privilege on the new schema.
74
To alter the owner, you must also be a direct or indirect member of the new
75
owning role, and that role must have <code class="literal">CREATE</code> privilege on
76
the domain's schema. (These restrictions enforce that altering the owner
77
doesn't do anything you couldn't do by dropping and recreating the domain.
78
However, a superuser can alter ownership of any domain anyway.)
71
81
<div class="refsect1" lang="en">
72
<a name="id2661359"></a><h2>Parameters</h2>
82
<a name="id741721"></a><h2>Parameters</h2>
74
84
<div class="variablelist"><dl>
75
<dt><span class="term"><i class="replaceable"><tt>name</tt></i></span></dt>
76
<dd><p> The name (possibly schema-qualified) of an existing domain to
79
<dt><span class="term"><i class="replaceable"><tt>domain_constraint</tt></i></span></dt>
80
<dd><p> New domain constraint for the domain.
82
<dt><span class="term"><i class="replaceable"><tt>constraint_name</tt></i></span></dt>
83
<dd><p> Name of an existing constraint to drop.
85
<dt><span class="term"><tt class="literal">CASCADE</tt></span></dt>
85
<dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt>
86
<dd><p> The name (possibly schema-qualified) of an existing domain to
89
<dt><span class="term"><em class="replaceable"><code>domain_constraint</code></em></span></dt>
90
<dd><p> New domain constraint for the domain.
92
<dt><span class="term"><em class="replaceable"><code>constraint_name</code></em></span></dt>
93
<dd><p> Name of an existing constraint to drop.
95
<dt><span class="term"><code class="literal">CASCADE</code></span></dt>
86
96
<dd><p> Automatically drop objects that depend on the constraint.
88
<dt><span class="term"><tt class="literal">RESTRICT</tt></span></dt>
98
<dt><span class="term"><code class="literal">RESTRICT</code></span></dt>
89
99
<dd><p> Refuse to drop the constraint if there are any dependent
90
objects. This is the default behavior.
92
<dt><span class="term"><i class="replaceable"><tt>new_owner</tt></i></span></dt>
93
<dd><p> The user name of the new owner of the domain.
100
objects. This is the default behavior.
102
<dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt>
103
<dd><p> The user name of the new owner of the domain.
105
<dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt>
106
<dd><p> The new schema for the domain.
99
112
<div class="refsect1" lang="en">
100
<a name="id2661435"></a><h2>Examples</h2>
101
<p> To add a <tt class="literal">NOT NULL</tt> constraint to a domain:
113
<a name="id741817"></a><h2>Examples</h2>
114
<p> To add a <code class="literal">NOT NULL</code> constraint to a domain:
103
116
<pre class="programlisting">ALTER DOMAIN zipcode SET NOT NULL;
106
To remove a <tt class="literal">NOT NULL</tt> constraint from a domain:
119
To remove a <code class="literal">NOT NULL</code> constraint from a domain:
108
121
<pre class="programlisting">ALTER DOMAIN zipcode DROP NOT NULL;
127
<div class="refsect1" lang="en">
128
<a name="id2661487"></a><h2>Compatibility</h2>
129
<p> The <tt class="command">ALTER DOMAIN</tt> statement is compatible with SQL99,
130
except for the <tt class="literal">OWNER</tt> variant, which is a
131
<span class="productname">PostgreSQL</span> extension.
139
<p> To move the domain into a different schema:
141
<pre class="programlisting">ALTER DOMAIN zipcode SET SCHEMA customers;
146
<div class="refsect1" lang="en">
147
<a name="sql-alterdomain-compatibility"></a><h2>Compatibility</h2>
148
<p> <code class="command">ALTER DOMAIN</code> conforms to the <acronym class="acronym">SQL</acronym>
150
except for the <code class="literal">OWNER</code> and <code class="literal">SET SCHEMA</code> variants,
151
which are <span class="productname">PostgreSQL</span> extensions.
154
<div class="refsect1" lang="en">
155
<a name="sql-alterdomain-see-also"></a><h2>See Also</h2>
156
<span class="simplelist"><a href="sql-createdomain.html">CREATE DOMAIN</a>, <a href="sql-dropdomain.html">DROP DOMAIN</a></span>