1ALTER SEQUENCE(7)                SQL Commands                ALTER SEQUENCE(7)
2
3
4

NAME

6       ALTER SEQUENCE - change the definition of a sequence generator
7
8

SYNOPSIS

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
20
21

DESCRIPTION

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
27       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

PARAMETERS

37       name   The name (optionally  schema-qualified)  of  a  sequence  to  be
38              altered.
39
40       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
46       minvalue
47
48       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
55       maxvalue
56
57       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
64       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
69       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
78       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
83       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
89       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
95       OWNED BY table.column
96
97       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
107       new_owner
108              The user name of the new owner of the sequence.
109
110       new_name
111              The new name for the sequence.
112
113       new_schema
114              The new schema for the sequence.
115

NOTES

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
124       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
130       ALTER  SEQUENCE  does  not  affect the currval status for the sequence.
131       (Before PostgreSQL 8.3, it sometimes did.)
132
133       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

EXAMPLES

138       Restart a sequence called serial, at 105:
139
140       ALTER SEQUENCE serial RESTART WITH 105;
141
142

COMPATIBILITY

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

SEE ALSO

149       CREATE SEQUENCE [create_sequence(7)], DROP SEQUENCE [drop_sequence(7)]
150
151
152
153SQL - Language Statements         2014-02-17                 ALTER SEQUENCE(7)
Impressum