Debian Codegraph @

doc/src/sgml/ref/alter_sequence.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_sequence.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-altersequence">
7 <indexterm zone="sql-altersequence">
8 <primary>ALTER SEQUENCE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER SEQUENCE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER SEQUENCE</refname>
19 <refpurpose>
20 change the definition of a sequence generator
21 </refpurpose>
22 </refnamediv>
23
24 <refsynopsisdiv>
25 <synopsis>
26 ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable>
27 [ AS <replaceable class="parameter">data_type</replaceable> ]
28 [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
29 [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
30 [ [ NO ] CYCLE ]
31 [ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
32 [ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
33 [ CACHE <replaceable class="parameter">cache</replaceable> ]
34 [ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
35 ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET { LOGGED | UNLOGGED }
36 ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
37 ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
38 ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
39 </synopsis>
40 </refsynopsisdiv>
41
42 <refsect1>
43 <title>Description</title>
44
45 <para>
46 <command>ALTER SEQUENCE</command> changes the parameters of an existing
47 sequence generator. Any parameters not specifically set in the
48 <command>ALTER SEQUENCE</command> command retain their prior settings.
49 </para>
50
51 <para>
52 You must own the sequence to use <command>ALTER SEQUENCE</command>.
53 To change a sequence's schema, you must also have <literal>CREATE</literal>
54 privilege on the new schema.
55 To alter the owner, you must be able to <literal>SET ROLE</literal> to the
56 new owning role, and that role must have <literal>CREATE</literal>
57 privilege on the sequence's schema.
58 (These restrictions enforce that altering the owner
59 doesn't do anything you couldn't do by dropping and recreating the sequence.
60 However, a superuser can alter ownership of any sequence anyway.)
61 </para>
62 </refsect1>
63
64 <refsect1>
65 <title>Parameters</title>
66
67 <para>
68 <variablelist>
69 <varlistentry>
70 <term><replaceable class="parameter">name</replaceable></term>
71 <listitem>
72 <para>
73 The name (optionally schema-qualified) of a sequence to be altered.
74 </para>
75 </listitem>
76 </varlistentry>
77
78 <varlistentry>
79 <term><literal>IF EXISTS</literal></term>
80 <listitem>
81 <para>
82 Do not throw an error if the sequence does not exist. A notice is issued
83 in this case.
84 </para>
85 </listitem>
86 </varlistentry>
87
88 <varlistentry>
89 <term><replaceable class="parameter">data_type</replaceable></term>
90 <listitem>
91 <para>
92 The optional
93 clause <literal>AS <replaceable class="parameter">data_type</replaceable></literal>
94 changes the data type of the sequence. Valid types are
95 <literal>smallint</literal>, <literal>integer</literal>,
96 and <literal>bigint</literal>.
97 </para>
98
99 <para>
100 Changing the data type automatically changes the minimum and maximum
101 values of the sequence if and only if the previous minimum and maximum
102 values were the minimum or maximum value of the old data type (in
103 other words, if the sequence had been created using <literal>NO
104 MINVALUE</literal> or <literal>NO MAXVALUE</literal>, implicitly or
105 explicitly). Otherwise, the minimum and maximum values are preserved,
106 unless new values are given as part of the same command. If the
107 minimum and maximum values do not fit into the new data type, an error
108 will be generated.
109 </para>
110 </listitem>
111 </varlistentry>
112
113 <varlistentry>
114 <term><replaceable class="parameter">increment</replaceable></term>
115 <listitem>
116 <para>
117 The clause <literal>INCREMENT BY <replaceable
118 class="parameter">increment</replaceable></literal> is
119 optional. A positive value will make an ascending sequence, a
120 negative one a descending sequence. If unspecified, the old
121 increment value will be maintained.
122 </para>
123 </listitem>
124 </varlistentry>
125
126 <varlistentry>
127 <term><replaceable class="parameter">minvalue</replaceable></term>
128 <term><literal>NO MINVALUE</literal></term>
129 <listitem>
130 <para>
131 The optional clause <literal>MINVALUE <replaceable
132 class="parameter">minvalue</replaceable></literal> determines
133 the minimum value a sequence can generate. If <literal>NO
134 MINVALUE</literal> is specified, the defaults of 1 and
135 the minimum value of the data type for ascending and descending sequences,
136 respectively, will be used. If neither option is specified,
137 the current minimum value will be maintained.
138 </para>
139 </listitem>
140 </varlistentry>
141
142 <varlistentry>
143 <term><replaceable class="parameter">maxvalue</replaceable></term>
144 <term><literal>NO MAXVALUE</literal></term>
145 <listitem>
146 <para>
147 The optional clause <literal>MAXVALUE <replaceable
148 class="parameter">maxvalue</replaceable></literal> determines
149 the maximum value for the sequence. If <literal>NO
150 MAXVALUE</literal> is specified, the defaults of
151 the maximum value of the data type and -1 for ascending and descending
152 sequences, respectively, will be used. If neither option is
153 specified, the current maximum value will be maintained.
154 </para>
155 </listitem>
156 </varlistentry>
157
158 <varlistentry>
159 <term><literal>CYCLE</literal></term>
160 <listitem>
161 <para>
162 The optional <literal>CYCLE</literal> key word can be used to enable
163 the sequence to wrap around when the
164 <replaceable class="parameter">maxvalue</replaceable> or
165 <replaceable class="parameter">minvalue</replaceable> has been
166 reached by
167 an ascending or descending sequence respectively. If the limit is
168 reached, the next number generated will be the
169 <replaceable class="parameter">minvalue</replaceable> or
170 <replaceable class="parameter">maxvalue</replaceable>,
171 respectively.
172 </para>
173 </listitem>
174 </varlistentry>
175
176 <varlistentry>
177 <term><literal>NO CYCLE</literal></term>
178 <listitem>
179 <para>
180 If the optional <literal>NO CYCLE</literal> key word is
181 specified, any calls to <function>nextval</function> after the
182 sequence has reached its maximum value will return an error.
183 If neither <literal>CYCLE</literal> or <literal>NO
184 CYCLE</literal> are specified, the old cycle behavior will be
185 maintained.
186 </para>
187 </listitem>
188 </varlistentry>
189
190 <varlistentry>
191 <term><replaceable class="parameter">start</replaceable></term>
192 <listitem>
193 <para>
194 The optional clause <literal>START WITH <replaceable
195 class="parameter">start</replaceable></literal> changes the
196 recorded start value of the sequence. This has no effect on the
197 <emphasis>current</emphasis> sequence value; it simply sets the value
198 that future <command>ALTER SEQUENCE RESTART</command> commands will use.
199 </para>
200 </listitem>
201 </varlistentry>
202
203 <varlistentry>
204 <term><replaceable class="parameter">restart</replaceable></term>
205 <listitem>
206 <para>
207 The optional clause <literal>RESTART [ WITH <replaceable
208 class="parameter">restart</replaceable> ]</literal> changes the
209 current value of the sequence. This is similar to calling the
210 <function>setval</function> function with <literal>is_called</literal> =
211 <literal>false</literal>: the specified value will be returned by the
212 <emphasis>next</emphasis> call of <function>nextval</function>.
213 Writing <literal>RESTART</literal> with no <replaceable
214 class="parameter">restart</replaceable> value is equivalent to supplying
215 the start value that was recorded by <command>CREATE SEQUENCE</command>
216 or last set by <command>ALTER SEQUENCE START WITH</command>.
217 </para>
218
219 <para>
220 In contrast to a <function>setval</function> call,
221 a <literal>RESTART</literal> operation on a sequence is transactional
222 and blocks concurrent transactions from obtaining numbers from the
223 same sequence. If that's not the desired mode of
224 operation, <function>setval</function> should be used.
225 </para>
226 </listitem>
227 </varlistentry>
228
229 <varlistentry>
230 <term><replaceable class="parameter">cache</replaceable></term>
231 <listitem>
232 <para>
233 The clause <literal>CACHE <replaceable
234 class="parameter">cache</replaceable></literal> enables
235 sequence numbers to be preallocated and stored in memory for
236 faster access. The minimum value is 1 (only one value can be
237 generated at a time, i.e., no cache). If unspecified, the old
238 cache value will be maintained.
239 </para>
240 </listitem>
241 </varlistentry>
242
243 <varlistentry>
244 <term><literal>SET { LOGGED | UNLOGGED }</literal></term>
245 <listitem>
246 <para>
247 This form changes the sequence from unlogged to logged or vice-versa
248 (see <xref linkend="sql-createsequence"/>). It cannot be applied to a
249 temporary sequence.
250 </para>
251 </listitem>
252 </varlistentry>
253
254 <varlistentry>
255 <term><literal>OWNED BY</literal> <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable></term>
256 <term><literal>OWNED BY NONE</literal></term>
257 <listitem>
258 <para>
259 The <literal>OWNED BY</literal> option causes the sequence to be
260 associated with a specific table column, such that if that column
261 (or its whole table) is dropped, the sequence will be automatically
262 dropped as well. If specified, this association replaces any
263 previously specified association for the sequence. The specified
264 table must have the same owner and be in the same schema as the
265 sequence.
266 Specifying <literal>OWNED BY NONE</literal> removes any existing
267 association, making the sequence <quote>free-standing</quote>.
268 </para>
269 </listitem>
270 </varlistentry>
271
272 <varlistentry>
273 <term><replaceable class="parameter">new_owner</replaceable></term>
274 <listitem>
275 <para>
276 The user name of the new owner of the sequence.
277 </para>
278 </listitem>
279 </varlistentry>
280
281 <varlistentry>
282 <term><replaceable class="parameter">new_name</replaceable></term>
283 <listitem>
284 <para>
285 The new name for the sequence.
286 </para>
287 </listitem>
288 </varlistentry>
289
290 <varlistentry>
291 <term><replaceable class="parameter">new_schema</replaceable></term>
292 <listitem>
293 <para>
294 The new schema for the sequence.
295 </para>
296 </listitem>
297 </varlistentry>
298
299 </variablelist>
300 </para>
301 </refsect1>
302
303 <refsect1>
304 <title>Notes</title>
305
306 <para>
307 <command>ALTER SEQUENCE</command> will not immediately affect
308 <function>nextval</function> results in backends,
309 other than the current one, that have preallocated (cached) sequence
310 values. They will use up all cached values prior to noticing the changed
311 sequence generation parameters. The current backend will be affected
312 immediately.
313 </para>
314
315 <para>
316 <command>ALTER SEQUENCE</command> does not affect the <function>currval</function>
317 status for the sequence. (Before <productname>PostgreSQL</productname>
318 8.3, it sometimes did.)
319 </para>
320
321 <para>
322 <command>ALTER SEQUENCE</command> blocks
323 concurrent <function>nextval</function>, <function>currval</function>,
324 <function>lastval</function>, and <command>setval</command> calls.
325 </para>
326
327 <para>
328 For historical reasons, <command>ALTER TABLE</command> can be used with
329 sequences too; but the only variants of <command>ALTER TABLE</command>
330 that are allowed with sequences are equivalent to the forms shown above.
331 </para>
332 </refsect1>
333
334 <refsect1>
335 <title>Examples</title>
336
337 <para>
338 Restart a sequence called <literal>serial</literal>, at 105:
339 <programlisting>
340 ALTER SEQUENCE serial RESTART WITH 105;
341 </programlisting></para>
342 </refsect1>
343
344 <refsect1>
345 <title>Compatibility</title>
346
347 <para>
348 <command>ALTER SEQUENCE</command> conforms to the <acronym>SQL</acronym>
349 standard, except for the <literal>AS</literal>, <literal>START WITH</literal>,
350 <literal>OWNED BY</literal>, <literal>OWNER TO</literal>, <literal>RENAME TO</literal>, and
351 <literal>SET SCHEMA</literal> clauses, which are
352 <productname>PostgreSQL</productname> extensions.
353 </para>
354 </refsect1>
355
356 <refsect1>
357 <title>See Also</title>
358
359 <simplelist type="inline">
360 <member><xref linkend="sql-createsequence"/></member>
361 <member><xref linkend="sql-dropsequence"/></member>
362 </simplelist>
363 </refsect1>
364
365 </refentry>