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
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>ALTER SEQUENCE</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-alterschema.html" title="ALTER SCHEMA">
<link rel="next" href="sql-altertable.html" title="ALTER TABLE">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-altersequence"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p> ALTER SEQUENCE
— change the definition of a sequence generator
</p>
</div>
<a name="id744692"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">ALTER SEQUENCE <em class="replaceable"><code>name</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 ]
[ RESTART [ WITH ] <em class="replaceable"><code>start</code></em> ] [ CACHE <em class="replaceable"><code>cache</code></em> ] [ [ NO ] CYCLE ]
ALTER SEQUENCE <em class="replaceable"><code>name</code></em> SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
</pre>
</div>
<div class="refsect1" lang="en">
<a name="id744766"></a><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.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id744806"></a><h2>Parameters</h2>
<p> </p>
<div class="variablelist"><dl>
<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"><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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><em xmlns="" class="replaceable"><code>minvalue</code></em></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><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
-2<sup>63</sup>-1 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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><em xmlns="" class="replaceable"><code>maxvalue</code></em></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><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 are
2<sup>63</sup>-1 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">RESTART WITH <em class="replaceable"><code>start</code></em></code> changes the
current value of the sequence.
</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 may 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"><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" lang="en">
<a name="id745103"></a><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>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id745124"></a><h2>Notes</h2>
<p> To avoid blocking of concurrent transactions that obtain numbers from the
same sequence, <code class="command">ALTER SEQUENCE</code> is never rolled back;
the changes take effect immediately and are not reversible.
</p>
<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 parameters. The current backend will be affected immediately.
</p>
<p> Some variants of <code class="command">ALTER TABLE</code> can be used with
sequences as well; for example, to rename a sequence use <code class="command">ALTER
TABLE RENAME</code>.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id745178"></a><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">SET SCHEMA</code> variant, which is a
<span class="productname">PostgreSQL</span> extension.
</p>
</div>
</div></body>
</html>
|
