1ALTER SEQUENCE(7) SQL Commands ALTER SEQUENCE(7)
2
6 ALTER SEQUENCE - change the definition of a sequence generator
7
10 ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
11 [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
12 [ START [ WITH ] start ]
13 [ RESTART [ [ WITH ] restart ] ]
14 [ CACHE cache ] [ [ NO ] CYCLE ]
15 [ OWNED BY { table.column | NONE } ]
16 ALTER SEQUENCE name OWNER TO new_owner
17 ALTER SEQUENCE name RENAME TO new_name
18 ALTER SEQUENCE name SET SCHEMA new_schema
19
23 ALTER SEQUENCE changes the parameters of an existing sequence genera‐
24 tor. Any parameters not specifically set in the ALTER SEQUENCE command
25 retain their prior settings.
26 You must own the sequence to use ALTER SEQUENCE. To change a
28 sequence's schema, you must also have CREATE privilege on the new
29 schema. To alter the owner, you must also be a direct or indirect mem‐
30 ber of the new owning role, and that role must have CREATE privilege on
31 the sequence's schema. (These restrictions enforce that altering the
32 owner doesn't do anything you couldn't do by dropping and recreating
33 the sequence. However, a superuser can alter ownership of any sequence
34 anyway.)
35
37 name The name (optionally schema-qualified) of a sequence to be
38 altered.
39 increment
41 The clause INCREMENT BY increment is optional. A positive value
42 will make an ascending sequence, a negative one a descending
43 sequence. If unspecified, the old increment value will be main‐
44 tained.
45 minvalue
47 NO MINVALUE
49 The optional clause MINVALUE minvalue determines the minimum
50 value a sequence can generate. If NO MINVALUE is specified, the
51 defaults of 1 and -263-1 for ascending and descending sequences,
52 respectively, will be used. If neither option is specified, the
53 current minimum value will be maintained.
54 maxvalue
56 NO MAXVALUE
58 The optional clause MAXVALUE maxvalue determines the maximum
59 value for the sequence. If NO MAXVALUE is specified, the
60 defaults are 263-1 and -1 for ascending and descending
61 sequences, respectively, will be used. If neither option is
62 specified, the current maximum value will be maintained.
63 start The optional clause START WITH start changes the recorded start
65 value of the sequence. This has no effect on the current
66 sequence value; it simply sets the value that future ALTER
67 SEQUENCE RESTART commands will use.
68 restart
70 The optional clause RESTART [ WITH restart ] changes the current
71 value of the sequence. This is equivalent to calling the setval
72 function with is_called = false: the specified value will be
73 returned by the next call of nextval. Writing RESTART with no
74 restart value is equivalent to supplying the start value that
75 was recorded by CREATE SEQUENCE or last set by ALTER SEQUENCE
76 START WITH.
77 cache The clause CACHE cache enables sequence numbers to be preallo‐
79 cated and stored in memory for faster access. The minimum value
80 is 1 (only one value can be generated at a time, i.e., no
81 cache). If unspecified, the old cache value will be maintained.
82 CYCLE The optional CYCLE key word can be used to enable the sequence
84 to wrap around when the maxvalue or minvalue has been reached by
85 an ascending or descending sequence respectively. If the limit
86 is reached, the next number generated will be the minvalue or
87 maxvalue, respectively.
88 NO CYCLE
90 If the optional NO CYCLE key word is specified, any calls to
91 nextval after the sequence has reached its maximum value will
92 return an error. If neither CYCLE or NO CYCLE are specified,
93 the old cycle behavior will be maintained.
94 OWNED BY table.column
96 OWNED BY NONE
98 The OWNED BY option causes the sequence to be associated with a
99 specific table column, such that if that column (or its whole
100 table) is dropped, the sequence will be automatically dropped as
101 well. If specified, this association replaces any previously
102 specified association for the sequence. The specified table must
103 have the same owner and be in the same schema as the sequence.
104 Specifying OWNED BY NONE removes any existing association, mak‐
105 ing the sequence ``free-standing''.
106 new_owner
108 The user name of the new owner of the sequence.
109 new_name
111 The new name for the sequence.
112 new_schema
114 The new schema for the sequence.
115
117 To avoid blocking of concurrent transactions that obtain numbers from
118 the same sequence, ALTER SEQUENCE's effects on the sequence generation
119 parameters are never rolled back; those changes take effect immediately
120 and are not reversible. However, the OWNED BY, OWNER TO, RENAME TO, and
121 SET SCHEMA clauses cause ordinary catalog updates that can be rolled
122 back.
123 ALTER SEQUENCE will not immediately affect nextval results in backends,
125 other than the current one, that have preallocated (cached) sequence
126 values. They will use up all cached values prior to noticing the
127 changed sequence generation parameters. The current backend will be
128 affected immediately.
129 ALTER SEQUENCE does not affect the currval status for the sequence.
131 (Before PostgreSQL 8.3, it sometimes did.)
132 For historical reasons, ALTER TABLE can be used with sequences too; but
134 the only variants of ALTER TABLE that are allowed with sequences are
135 equivalent to the forms shown above.
136
138 Restart a sequence called serial, at 105:
139 ALTER SEQUENCE serial RESTART WITH 105;
141
144 ALTER SEQUENCE conforms to the SQL standard, except for the START WITH,
145 OWNED BY, OWNER TO, RENAME TO, and SET SCHEMA clauses, which are Post‐
146 greSQL extensions.
147
149 CREATE SEQUENCE [create_sequence(7)], DROP SEQUENCE [drop_sequence(7)]
150SQL - Language Statements 2011-09-22 ALTER SEQUENCE(7)