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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>ALTER SEQUENCE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-alterschema.html" title="ALTER SCHEMA" /><link rel="next" href="sql-alterserver.html" title="ALTER SERVER" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ALTER SEQUENCE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterschema.html" title="ALTER SCHEMA">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-alterserver.html" title="ALTER SERVER">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-ALTERSEQUENCE"><div class="titlepage"></div><a id="id-1.9.3.30.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER SEQUENCE</span></h2><p>ALTER SEQUENCE —
change the definition of a sequence generator
</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
[ AS <em class="replaceable"><code>data_type</code></em> ]
[ INCREMENT [ BY ] <em class="replaceable"><code>increment</code></em> ]
[ MINVALUE <em class="replaceable"><code>minvalue</code></em> | NO MINVALUE ] [ MAXVALUE <em class="replaceable"><code>maxvalue</code></em> | NO MAXVALUE ]
[ START [ WITH ] <em class="replaceable"><code>start</code></em> ]
[ RESTART [ [ WITH ] <em class="replaceable"><code>restart</code></em> ] ]
[ CACHE <em class="replaceable"><code>cache</code></em> ] [ [ NO ] CYCLE ]
[ OWNED BY { <em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em> | NONE } ]
ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> SET { LOGGED | UNLOGGED }
ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> RENAME TO <em class="replaceable"><code>new_name</code></em>
ALTER SEQUENCE [ IF EXISTS ] <em class="replaceable"><code>name</code></em> SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
</pre></div><div class="refsect1" id="id-1.9.3.30.5"><h2>Description</h2><p>
<code class="command">ALTER SEQUENCE</code> changes the parameters of an existing
sequence generator. Any parameters not specifically set in the
<code class="command">ALTER SEQUENCE</code> command retain their prior settings.
</p><p>
You must own the sequence to use <code class="command">ALTER SEQUENCE</code>.
To change a sequence's schema, you must also have <code class="literal">CREATE</code>
privilege on the new schema.
To alter the owner, you must be able to <code class="literal">SET ROLE</code> to the
new owning role, and that role must have <code class="literal">CREATE</code>
privilege on the sequence's schema.
(These restrictions enforce that altering the owner
doesn't do anything you couldn't do by dropping and recreating the sequence.
However, a superuser can alter ownership of any sequence anyway.)
</p></div><div class="refsect1" id="id-1.9.3.30.6"><h2>Parameters</h2><p>
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
The name (optionally schema-qualified) of a sequence to be altered.
</p></dd><dt><span class="term"><code class="literal">IF EXISTS</code></span></dt><dd><p>
Do not throw an error if the sequence does not exist. A notice is issued
in this case.
</p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>
The optional
clause <code class="literal">AS <em class="replaceable"><code>data_type</code></em></code>
changes the data type of the sequence. Valid types are
<code class="literal">smallint</code>, <code class="literal">integer</code>,
and <code class="literal">bigint</code>.
</p><p>
Changing the data type automatically changes the minimum and maximum
values of the sequence if and only if the previous minimum and maximum
values were the minimum or maximum value of the old data type (in
other words, if the sequence had been created using <code class="literal">NO
MINVALUE</code> or <code class="literal">NO MAXVALUE</code>, implicitly or
explicitly). Otherwise, the minimum and maximum values are preserved,
unless new values are given as part of the same command. If the
minimum and maximum values do not fit into the new data type, an error
will be generated.
</p></dd><dt><span class="term"><em class="replaceable"><code>increment</code></em></span></dt><dd><p>
The clause <code class="literal">INCREMENT BY <em class="replaceable"><code>increment</code></em></code> is
optional. A positive value will make an ascending sequence, a
negative one a descending sequence. If unspecified, the old
increment value will be maintained.
</p></dd><dt><span class="term"><em class="replaceable"><code>minvalue</code></em><br /></span><span class="term"><code class="literal">NO MINVALUE</code></span></dt><dd><p>
The optional clause <code class="literal">MINVALUE <em class="replaceable"><code>minvalue</code></em></code> determines
the minimum value a sequence can generate. If <code class="literal">NO
MINVALUE</code> is specified, the defaults of 1 and
the minimum value of the data type for ascending and descending sequences,
respectively, will be used. If neither option is specified,
the current minimum value will be maintained.
</p></dd><dt><span class="term"><em class="replaceable"><code>maxvalue</code></em><br /></span><span class="term"><code class="literal">NO MAXVALUE</code></span></dt><dd><p>
The optional clause <code class="literal">MAXVALUE <em class="replaceable"><code>maxvalue</code></em></code> determines
the maximum value for the sequence. If <code class="literal">NO
MAXVALUE</code> is specified, the defaults of
the maximum value of the data type and -1 for ascending and descending
sequences, respectively, will be used. If neither option is
specified, the current maximum value will be maintained.
</p></dd><dt><span class="term"><em class="replaceable"><code>start</code></em></span></dt><dd><p>
The optional clause <code class="literal">START WITH <em class="replaceable"><code>start</code></em></code> changes the
recorded start value of the sequence. This has no effect on the
<span class="emphasis"><em>current</em></span> sequence value; it simply sets the value
that future <code class="command">ALTER SEQUENCE RESTART</code> commands will use.
</p></dd><dt><span class="term"><em class="replaceable"><code>restart</code></em></span></dt><dd><p>
The optional clause <code class="literal">RESTART [ WITH <em class="replaceable"><code>restart</code></em> ]</code> changes the
current value of the sequence. This is similar to calling the
<code class="function">setval</code> function with <code class="literal">is_called</code> =
<code class="literal">false</code>: the specified value will be returned by the
<span class="emphasis"><em>next</em></span> call of <code class="function">nextval</code>.
Writing <code class="literal">RESTART</code> with no <em class="replaceable"><code>restart</code></em> value is equivalent to supplying
the start value that was recorded by <code class="command">CREATE SEQUENCE</code>
or last set by <code class="command">ALTER SEQUENCE START WITH</code>.
</p><p>
In contrast to a <code class="function">setval</code> call,
a <code class="literal">RESTART</code> operation on a sequence is transactional
and blocks concurrent transactions from obtaining numbers from the
same sequence. If that's not the desired mode of
operation, <code class="function">setval</code> should be used.
</p></dd><dt><span class="term"><em class="replaceable"><code>cache</code></em></span></dt><dd><p>
The clause <code class="literal">CACHE <em class="replaceable"><code>cache</code></em></code> enables
sequence numbers to be preallocated and stored in memory for
faster access. The minimum value is 1 (only one value can be
generated at a time, i.e., no cache). If unspecified, the old
cache value will be maintained.
</p></dd><dt><span class="term"><code class="literal">CYCLE</code></span></dt><dd><p>
The optional <code class="literal">CYCLE</code> key word can be used to enable
the sequence to wrap around when the
<em class="replaceable"><code>maxvalue</code></em> or
<em class="replaceable"><code>minvalue</code></em> has been
reached by
an ascending or descending sequence respectively. If the limit is
reached, the next number generated will be the
<em class="replaceable"><code>minvalue</code></em> or
<em class="replaceable"><code>maxvalue</code></em>,
respectively.
</p></dd><dt><span class="term"><code class="literal">NO CYCLE</code></span></dt><dd><p>
If the optional <code class="literal">NO CYCLE</code> key word is
specified, any calls to <code class="function">nextval</code> after the
sequence has reached its maximum value will return an error.
If neither <code class="literal">CYCLE</code> or <code class="literal">NO
CYCLE</code> are specified, the old cycle behavior will be
maintained.
</p></dd><dt><span class="term"><code class="literal">SET { LOGGED | UNLOGGED }</code></span></dt><dd><p>
This form changes the sequence from unlogged to logged or vice-versa
(see <a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>). It cannot be applied to a
temporary sequence.
</p></dd><dt><span class="term"><code class="literal">OWNED BY</code> <em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em><br /></span><span class="term"><code class="literal">OWNED BY NONE</code></span></dt><dd><p>
The <code class="literal">OWNED BY</code> option causes the sequence to be
associated with a specific table column, such that if that column
(or its whole table) is dropped, the sequence will be automatically
dropped as well. If specified, this association replaces any
previously specified association for the sequence. The specified
table must have the same owner and be in the same schema as the
sequence.
Specifying <code class="literal">OWNED BY NONE</code> removes any existing
association, making the sequence <span class="quote">“<span class="quote">free-standing</span>”</span>.
</p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>
The user name of the new owner of the sequence.
</p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>
The new name for the sequence.
</p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p>
The new schema for the sequence.
</p></dd></dl></div><p>
</p></div><div class="refsect1" id="id-1.9.3.30.7"><h2>Notes</h2><p>
<code class="command">ALTER SEQUENCE</code> will not immediately affect
<code class="function">nextval</code> results in backends,
other than the current one, that have preallocated (cached) sequence
values. They will use up all cached values prior to noticing the changed
sequence generation parameters. The current backend will be affected
immediately.
</p><p>
<code class="command">ALTER SEQUENCE</code> does not affect the <code class="function">currval</code>
status for the sequence. (Before <span class="productname">PostgreSQL</span>
8.3, it sometimes did.)
</p><p>
<code class="command">ALTER SEQUENCE</code> blocks
concurrent <code class="function">nextval</code>, <code class="function">currval</code>,
<code class="function">lastval</code>, and <code class="command">setval</code> calls.
</p><p>
For historical reasons, <code class="command">ALTER TABLE</code> can be used with
sequences too; but the only variants of <code class="command">ALTER TABLE</code>
that are allowed with sequences are equivalent to the forms shown above.
</p></div><div class="refsect1" id="id-1.9.3.30.8"><h2>Examples</h2><p>
Restart a sequence called <code class="literal">serial</code>, at 105:
</p><pre class="programlisting">
ALTER SEQUENCE serial RESTART WITH 105;
</pre></div><div class="refsect1" id="id-1.9.3.30.9"><h2>Compatibility</h2><p>
<code class="command">ALTER SEQUENCE</code> conforms to the <acronym class="acronym">SQL</acronym>
standard, except for the <code class="literal">AS</code>, <code class="literal">START WITH</code>,
<code class="literal">OWNED BY</code>, <code class="literal">OWNER TO</code>, <code class="literal">RENAME TO</code>, and
<code class="literal">SET SCHEMA</code> clauses, which are
<span class="productname">PostgreSQL</span> extensions.
</p></div><div class="refsect1" id="id-1.9.3.30.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createsequence.html" title="CREATE SEQUENCE"><span class="refentrytitle">CREATE SEQUENCE</span></a>, <a class="xref" href="sql-dropsequence.html" title="DROP SEQUENCE"><span class="refentrytitle">DROP SEQUENCE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-alterschema.html" title="ALTER SCHEMA">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-alterserver.html" title="ALTER SERVER">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER SCHEMA </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER SERVER</td></tr></table></div></body></html>
|
