2 doc/src/sgml/ref/alter_sequence.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERSEQUENCE">
7 <indexterm zone="sql-altersequence">
8 <primary>ALTER SEQUENCE</primary>
12 <refentrytitle>ALTER SEQUENCE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 <refname>ALTER SEQUENCE</refname>
20 change the definition of a sequence generator
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 [ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
31 [ RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] ]
32 [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
33 [ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
34 ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="PARAMETER">new_owner</replaceable> | CURRENT_USER | SESSION_USER }
35 ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
36 ALTER SEQUENCE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
41 <title>Description</title>
44 <command>ALTER SEQUENCE</command> changes the parameters of an existing
45 sequence generator. Any parameters not specifically set in the
46 <command>ALTER SEQUENCE</command> command retain their prior settings.
50 You must own the sequence to use <command>ALTER SEQUENCE</>.
51 To change a sequence's schema, you must also have <literal>CREATE</>
52 privilege on the new schema.
53 To alter the owner, you must also be a direct or indirect member of the new
54 owning role, and that role must have <literal>CREATE</literal> privilege on
55 the sequence's schema. (These restrictions enforce that altering the owner
56 doesn't do anything you couldn't do by dropping and recreating the sequence.
57 However, a superuser can alter ownership of any sequence anyway.)
62 <title>Parameters</title>
67 <term><replaceable class="parameter">name</replaceable></term>
70 The name (optionally schema-qualified) of a sequence to be altered.
76 <term><literal>IF EXISTS</literal></term>
79 Do not throw an error if the sequence does not exist. A notice is issued
86 <term><replaceable class="parameter">data_type</replaceable></term>
90 clause <literal>AS <replaceable class="parameter">data_type</replaceable></literal>
91 changes the data type of the sequence. Valid types are
92 are <literal>smallint</literal>, <literal>integer</literal>,
93 and <literal>bigint</literal>.
97 Changing the data type automatically changes the minimum and maximum
98 values of the sequence if and only if the previous minimum and maximum
99 values were the minimum or maximum value of the old data type (in
100 other words, if the sequence had been created using <literal>NO
101 MINVALUE</literal> or <literal>NO MAXVALUE</literal>, implicitly or
102 explicitly). Otherwise, the minimum and maximum values are preserved,
103 unless new values are given as part of the same command. If the
104 minimum and maximum values do not fit into the new data type, an error
111 <term><replaceable class="parameter">increment</replaceable></term>
114 The clause <literal>INCREMENT BY <replaceable
115 class="parameter">increment</replaceable></literal> is
116 optional. A positive value will make an ascending sequence, a
117 negative one a descending sequence. If unspecified, the old
118 increment value will be maintained.
124 <term><replaceable class="parameter">minvalue</replaceable></term>
125 <term><literal>NO MINVALUE</literal></term>
128 The optional clause <literal>MINVALUE <replaceable
129 class="parameter">minvalue</replaceable></literal> determines
130 the minimum value a sequence can generate. If <literal>NO
131 MINVALUE</literal> is specified, the defaults of 1 and
132 the minimum value of the data type for ascending and descending sequences,
133 respectively, will be used. If neither option is specified,
134 the current minimum value will be maintained.
140 <term><replaceable class="parameter">maxvalue</replaceable></term>
141 <term><literal>NO MAXVALUE</literal></term>
144 The optional clause <literal>MAXVALUE <replaceable
145 class="parameter">maxvalue</replaceable></literal> determines
146 the maximum value for the sequence. If <literal>NO
147 MAXVALUE</literal> is specified, the defaults of
148 the maximum value of the data type and -1 for ascending and descending
149 sequences, respectively, will be used. If neither option is
150 specified, the current maximum value will be maintained.
156 <term><replaceable class="parameter">start</replaceable></term>
159 The optional clause <literal>START WITH <replaceable
160 class="parameter">start</replaceable></literal> changes the
161 recorded start value of the sequence. This has no effect on the
162 <emphasis>current</> sequence value; it simply sets the value
163 that future <command>ALTER SEQUENCE RESTART</> commands will use.
169 <term><replaceable class="parameter">restart</replaceable></term>
172 The optional clause <literal>RESTART [ WITH <replaceable
173 class="parameter">restart</replaceable> ]</literal> changes the
174 current value of the sequence. This is similar to calling the
175 <function>setval</> function with <literal>is_called</literal> =
176 <literal>false</>: the specified value will be returned by the
177 <emphasis>next</> call of <function>nextval</>.
178 Writing <literal>RESTART</> with no <replaceable
179 class="parameter">restart</> value is equivalent to supplying
180 the start value that was recorded by <command>CREATE SEQUENCE</>
181 or last set by <command>ALTER SEQUENCE START WITH</>.
185 In contrast to a <function>setval</function> call,
186 a <literal>RESTART</literal> operation on a sequence is transactional
187 and blocks concurrent transactions from obtaining numbers from the
188 same sequence. If that's not the desired mode of
189 operation, <function>setval</> should be used.
195 <term><replaceable class="parameter">cache</replaceable></term>
198 The clause <literal>CACHE <replaceable
199 class="parameter">cache</replaceable></literal> enables
200 sequence numbers to be preallocated and stored in memory for
201 faster access. The minimum value is 1 (only one value can be
202 generated at a time, i.e., no cache). If unspecified, the old
203 cache value will be maintained.
209 <term><literal>CYCLE</literal></term>
212 The optional <literal>CYCLE</literal> key word can be used to enable
213 the sequence to wrap around when the
214 <replaceable class="parameter">maxvalue</replaceable> or
215 <replaceable class="parameter">minvalue</replaceable> has been
217 an ascending or descending sequence respectively. If the limit is
218 reached, the next number generated will be the
219 <replaceable class="parameter">minvalue</replaceable> or
220 <replaceable class="parameter">maxvalue</replaceable>,
227 <term><literal>NO CYCLE</literal></term>
230 If the optional <literal>NO CYCLE</literal> key word is
231 specified, any calls to <function>nextval</function> after the
232 sequence has reached its maximum value will return an error.
233 If neither <literal>CYCLE</literal> or <literal>NO
234 CYCLE</literal> are specified, the old cycle behavior will be
241 <term><literal>OWNED BY</literal> <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable></term>
242 <term><literal>OWNED BY NONE</literal></term>
245 The <literal>OWNED BY</literal> option causes the sequence to be
246 associated with a specific table column, such that if that column
247 (or its whole table) is dropped, the sequence will be automatically
248 dropped as well. If specified, this association replaces any
249 previously specified association for the sequence. The specified
250 table must have the same owner and be in the same schema as the
252 Specifying <literal>OWNED BY NONE</literal> removes any existing
253 association, making the sequence <quote>free-standing</>.
259 <term><replaceable class="PARAMETER">new_owner</replaceable></term>
262 The user name of the new owner of the sequence.
268 <term><replaceable class="parameter">new_name</replaceable></term>
271 The new name for the sequence.
277 <term><replaceable class="parameter">new_schema</replaceable></term>
280 The new schema for the sequence.
293 <command>ALTER SEQUENCE</command> will not immediately affect
294 <function>nextval</> results in backends,
295 other than the current one, that have preallocated (cached) sequence
296 values. They will use up all cached values prior to noticing the changed
297 sequence generation parameters. The current backend will be affected
302 <command>ALTER SEQUENCE</command> does not affect the <function>currval</>
303 status for the sequence. (Before <productname>PostgreSQL</productname>
304 8.3, it sometimes did.)
308 <command>ALTER SEQUENCE</command> blocks
309 concurrent <function>nextval</function>, <function>currval</function>,
310 <function>lastval</function>, and <command>setval</command> calls.
314 For historical reasons, <command>ALTER TABLE</command> can be used with
315 sequences too; but the only variants of <command>ALTER TABLE</command>
316 that are allowed with sequences are equivalent to the forms shown above.
321 <title>Examples</title>
324 Restart a sequence called <literal>serial</literal>, at 105:
326 ALTER SEQUENCE serial RESTART WITH 105;
327 </programlisting></para>
331 <title>Compatibility</title>
334 <command>ALTER SEQUENCE</command> conforms to the <acronym>SQL</acronym>
335 standard, except for the <literal>AS</literal>, <literal>START WITH</>,
336 <literal>OWNED BY</>, <literal>OWNER TO</>, <literal>RENAME TO</>, and
337 <literal>SET SCHEMA</literal> clauses, which are
338 <productname>PostgreSQL</productname> extensions.
343 <title>See Also</title>
345 <simplelist type="inline">
346 <member><xref linkend="sql-createsequence"></member>
347 <member><xref linkend="sql-dropsequence"></member>