Debian Codegraph @

doc/src/sgml/ref/alter_domain.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_domain.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-alterdomain">
7 <indexterm zone="sql-alterdomain">
8 <primary>ALTER DOMAIN</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER DOMAIN</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER DOMAIN</refname>
19 <refpurpose>
20 change the definition of a domain
21 </refpurpose>
22 </refnamediv>
23
24 <refsynopsisdiv>
25 <synopsis>
26 ALTER DOMAIN <replaceable class="parameter">name</replaceable>
27 { SET DEFAULT <replaceable class="parameter">expression</replaceable> | DROP DEFAULT }
28 ALTER DOMAIN <replaceable class="parameter">name</replaceable>
29 { SET | DROP } NOT NULL
30 ALTER DOMAIN <replaceable class="parameter">name</replaceable>
31 ADD <replaceable class="parameter">domain_constraint</replaceable> [ NOT VALID ]
32 ALTER DOMAIN <replaceable class="parameter">name</replaceable>
33 DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ]
34 ALTER DOMAIN <replaceable class="parameter">name</replaceable>
35 RENAME CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> TO <replaceable class="parameter">new_constraint_name</replaceable>
36 ALTER DOMAIN <replaceable class="parameter">name</replaceable>
37 VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable>
38 ALTER DOMAIN <replaceable class="parameter">name</replaceable>
39 OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
40 ALTER DOMAIN <replaceable class="parameter">name</replaceable>
41 RENAME TO <replaceable class="parameter">new_name</replaceable>
42 ALTER DOMAIN <replaceable class="parameter">name</replaceable>
43 SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
44
45 <phrase>where <replaceable class="parameter">domain_constraint</replaceable> is:</phrase>
46
47 [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
48 { NOT NULL | CHECK (<replaceable class="parameter">expression</replaceable>) }
49 </synopsis>
50 </refsynopsisdiv>
51
52 <refsect1>
53 <title>Description</title>
54
55 <para>
56 <command>ALTER DOMAIN</command> changes the definition of an existing domain.
57 There are several sub-forms:
58 </para>
59
60 <variablelist>
61 <varlistentry>
62 <term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
63 <listitem>
64 <para>
65 These forms set or remove the default value for a domain. Note
66 that defaults only apply to subsequent <command>INSERT</command>
67 commands; they do not affect rows already in a table using the domain.
68 </para>
69 </listitem>
70 </varlistentry>
71
72 <varlistentry>
73 <term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term>
74 <listitem>
75 <para>
76 These forms change whether a domain is marked to allow NULL
77 values or to reject NULL values. You can only <literal>SET NOT NULL</literal>
78 when the columns using the domain contain no null values.
79 </para>
80 </listitem>
81 </varlistentry>
82
83 <varlistentry>
84 <term><literal>ADD <replaceable class="parameter">domain_constraint</replaceable> [ NOT VALID ]</literal></term>
85 <listitem>
86 <para>
87 This form adds a new constraint to a domain.
88 When a new constraint is added to a domain, all columns using that
89 domain will be checked against the newly added constraint. These
90 checks can be suppressed by adding the new constraint using the
91 <literal>NOT VALID</literal> option; the constraint can later be made
92 valid using <command>ALTER DOMAIN ... VALIDATE CONSTRAINT</command>.
93 Newly inserted or updated rows are always checked against all
94 constraints, even those marked <literal>NOT VALID</literal>.
95 <literal>NOT VALID</literal> is only accepted for <literal>CHECK</literal> constraints.
96 </para>
97 </listitem>
98 </varlistentry>
99
100 <varlistentry>
101 <term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>
102 <listitem>
103 <para>
104 This form drops constraints on a domain.
105 If <literal>IF EXISTS</literal> is specified and the constraint
106 does not exist, no error is thrown. In this case a notice is issued instead.
107 </para>
108 </listitem>
109 </varlistentry>
110
111 <varlistentry>
112 <term><literal>RENAME CONSTRAINT</literal></term>
113 <listitem>
114 <para>
115 This form changes the name of a constraint on a domain.
116 </para>
117 </listitem>
118 </varlistentry>
119
120 <varlistentry>
121 <term><literal>VALIDATE CONSTRAINT</literal></term>
122 <listitem>
123 <para>
124 This form validates a constraint previously added as
125 <literal>NOT VALID</literal>, that is, it verifies that all values in
126 table columns of the domain type satisfy the specified constraint.
127 </para>
128 </listitem>
129 </varlistentry>
130
131 <varlistentry>
132 <term><literal>OWNER</literal></term>
133 <listitem>
134 <para>
135 This form changes the owner of the domain to the specified user.
136 </para>
137 </listitem>
138 </varlistentry>
139
140 <varlistentry>
141 <term><literal>RENAME</literal></term>
142 <listitem>
143 <para>
144 This form changes the name of the domain.
145 </para>
146 </listitem>
147 </varlistentry>
148
149 <varlistentry>
150 <term><literal>SET SCHEMA</literal></term>
151 <listitem>
152 <para>
153 This form changes the schema of the domain. Any constraints
154 associated with the domain are moved into the new schema as well.
155 </para>
156 </listitem>
157 </varlistentry>
158 </variablelist>
159
160 <para>
161 You must own the domain to use <command>ALTER DOMAIN</command>.
162 To change the schema of a domain, you must also have
163 <literal>CREATE</literal> privilege on the new schema.
164 To alter the owner, you must be able to <literal>SET ROLE</literal> to the
165 new owning role, and that role must have <literal>CREATE</literal> privilege
166 on the domain's schema. (These restrictions enforce that altering the owner
167 doesn't do anything you couldn't do by dropping and recreating the domain.
168 However, a superuser can alter ownership of any domain anyway.)
169 </para>
170 </refsect1>
171
172 <refsect1>
173 <title>Parameters</title>
174
175 <para>
176 <variablelist>
177 <varlistentry>
178 <term><replaceable class="parameter">name</replaceable></term>
179 <listitem>
180 <para>
181 The name (possibly schema-qualified) of an existing domain to
182 alter.
183 </para>
184 </listitem>
185 </varlistentry>
186
187 <varlistentry>
188 <term><replaceable class="parameter">domain_constraint</replaceable></term>
189 <listitem>
190 <para>
191 New domain constraint for the domain.
192 </para>
193 </listitem>
194 </varlistentry>
195
196 <varlistentry>
197 <term><replaceable class="parameter">constraint_name</replaceable></term>
198 <listitem>
199 <para>
200 Name of an existing constraint to drop or rename.
201 </para>
202 </listitem>
203 </varlistentry>
204
205 <varlistentry>
206 <term><literal>NOT VALID</literal></term>
207 <listitem>
208 <para>
209 Do not verify existing stored data for constraint validity.
210 </para>
211 </listitem>
212 </varlistentry>
213
214
215 <varlistentry>
216 <term><literal>CASCADE</literal></term>
217 <listitem>
218 <para>
219 Automatically drop objects that depend on the constraint,
220 and in turn all objects that depend on those objects
221 (see <xref linkend="ddl-depend"/>).
222 </para>
223 </listitem>
224 </varlistentry>
225
226 <varlistentry>
227 <term><literal>RESTRICT</literal></term>
228 <listitem>
229 <para>
230 Refuse to drop the constraint if there are any dependent
231 objects. This is the default behavior.
232 </para>
233 </listitem>
234 </varlistentry>
235
236 <varlistentry>
237 <term><replaceable class="parameter">new_name</replaceable></term>
238 <listitem>
239 <para>
240 The new name for the domain.
241 </para>
242 </listitem>
243 </varlistentry>
244
245 <varlistentry>
246 <term><replaceable class="parameter">new_constraint_name</replaceable></term>
247 <listitem>
248 <para>
249 The new name for the constraint.
250 </para>
251 </listitem>
252 </varlistentry>
253
254 <varlistentry>
255 <term><replaceable class="parameter">new_owner</replaceable></term>
256 <listitem>
257 <para>
258 The user name of the new owner of the domain.
259 </para>
260 </listitem>
261 </varlistentry>
262
263 <varlistentry>
264 <term><replaceable class="parameter">new_schema</replaceable></term>
265 <listitem>
266 <para>
267 The new schema for the domain.
268 </para>
269 </listitem>
270 </varlistentry>
271
272 </variablelist>
273 </para>
274 </refsect1>
275
276 <refsect1>
277 <title>Notes</title>
278
279 <para>
280 Although <command>ALTER DOMAIN ADD CONSTRAINT</command> attempts to verify
281 that existing stored data satisfies the new constraint, this check is not
282 bulletproof, because the command cannot <quote>see</quote> table rows that
283 are newly inserted or updated and not yet committed. If there is a hazard
284 that concurrent operations might insert bad data, the way to proceed is to
285 add the constraint using the <literal>NOT VALID</literal> option, commit
286 that command, wait until all transactions started before that commit have
287 finished, and then issue <command>ALTER DOMAIN VALIDATE
288 CONSTRAINT</command> to search for data violating the constraint. This
289 method is reliable because once the constraint is committed, all new
290 transactions are guaranteed to enforce it against new values of the domain
291 type.
292 </para>
293
294 <para>
295 Currently, <command>ALTER DOMAIN ADD CONSTRAINT</command>, <command>ALTER
296 DOMAIN VALIDATE CONSTRAINT</command>, and <command>ALTER DOMAIN SET NOT
297 NULL</command> will fail if the named domain or any derived domain is used
298 within a container-type column (a composite, array, or range column) in
299 any table in the database. They should eventually be improved to be able
300 to verify the new constraint for such nested values.
301 </para>
302
303 </refsect1>
304
305 <refsect1>
306 <title>Examples</title>
307
308 <para>
309 To add a <literal>NOT NULL</literal> constraint to a domain:
310 <programlisting>
311 ALTER DOMAIN zipcode SET NOT NULL;
312 </programlisting>
313 To remove a <literal>NOT NULL</literal> constraint from a domain:
314 <programlisting>
315 ALTER DOMAIN zipcode DROP NOT NULL;
316 </programlisting>
317 </para>
318
319 <para>
320 To add a check constraint to a domain:
321 <programlisting>
322 ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);
323 </programlisting>
324 </para>
325
326 <para>
327 To remove a check constraint from a domain:
328 <programlisting>
329 ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;
330 </programlisting>
331 </para>
332
333 <para>
334 To rename a check constraint on a domain:
335 <programlisting>
336 ALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;
337 </programlisting>
338 </para>
339
340 <para>
341 To move the domain into a different schema:
342 <programlisting>
343 ALTER DOMAIN zipcode SET SCHEMA customers;
344 </programlisting></para>
345 </refsect1>
346
347 <refsect1 id="sql-alterdomain-compatibility">
348 <title>Compatibility</title>
349
350 <para>
351 <command>ALTER DOMAIN</command> conforms to the <acronym>SQL</acronym>
352 standard, except for the <literal>OWNER</literal>, <literal>RENAME</literal>, <literal>SET SCHEMA</literal>, and
353 <literal>VALIDATE CONSTRAINT</literal> variants, which are
354 <productname>PostgreSQL</productname> extensions. The <literal>NOT VALID</literal>
355 clause of the <literal>ADD CONSTRAINT</literal> variant is also a
356 <productname>PostgreSQL</productname> extension.
357 </para>
358 </refsect1>
359
360 <refsect1 id="sql-alterdomain-see-also">
361 <title>See Also</title>
362
363 <simplelist type="inline">
364 <member><xref linkend="sql-createdomain"/></member>
365 <member><xref linkend="sql-dropdomain"/></member>
366 </simplelist>
367 </refsect1>
368
369 </refentry>