I386LINUX/util/I386LINUX/doc/postgresql/html/sql-altersequence.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
   2 <HTML
   3 ><HEAD
   4 ><TITLE
   5 >ALTER SEQUENCE</TITLE
   6 ><META
   7 NAME="GENERATOR"
   8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
   9 REV="MADE"
  10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
  11 REL="HOME"
  12 TITLE="PostgreSQL 7.4.1 Documentation"
  13 HREF="index.html"><LINK
  14 REL="UP"
  15 TITLE="SQL Commands"
  16 HREF="sql-commands.html"><LINK
  17 REL="PREVIOUS"
  18 TITLE="ALTER SCHEMA"
  19 HREF="sql-alterschema.html"><LINK
  20 REL="NEXT"
  21 TITLE="ALTER TABLE"
  22 HREF="sql-altertable.html"><LINK
  23 REL="STYLESHEET"
  24 TYPE="text/css"
  25 HREF="stylesheet.css"><META
  26 NAME="creation"
  27 CONTENT="2003-12-22T03:48:47"></HEAD
  28 ><BODY
  29 CLASS="REFENTRY"
  30 ><DIV
  31 CLASS="NAVHEADER"
  32 ><TABLE
  33 SUMMARY="Header navigation table"
  34 WIDTH="100%"
  35 BORDER="0"
  36 CELLPADDING="0"
  37 CELLSPACING="0"
  38 ><TR
  39 ><TH
  40 COLSPAN="5"
  41 ALIGN="center"
  42 VALIGN="bottom"
  43 >PostgreSQL 7.4.1 Documentation</TH
  44 ></TR
  45 ><TR
  46 ><TD
  47 WIDTH="10%"
  48 ALIGN="left"
  49 VALIGN="top"
  50 ><A
  51 HREF="sql-alterschema.html"
  52 ACCESSKEY="P"
  53 >Prev</A
  54 ></TD
  55 ><TD
  56 WIDTH="10%"
  57 ALIGN="left"
  58 VALIGN="top"
  59 ><A
  60 HREF="sql-alterschema.html"
  61 >Fast Backward</A
  62 ></TD
  63 ><TD
  64 WIDTH="60%"
  65 ALIGN="center"
  66 VALIGN="bottom"
  67 ></TD
  68 ><TD
  69 WIDTH="10%"
  70 ALIGN="right"
  71 VALIGN="top"
  72 ><A
  73 HREF="sql-altertable.html"
  74 >Fast Forward</A
  75 ></TD
  76 ><TD
  77 WIDTH="10%"
  78 ALIGN="right"
  79 VALIGN="top"
  80 ><A
  81 HREF="sql-altertable.html"
  82 ACCESSKEY="N"
  83 >Next</A
  84 ></TD
  85 ></TR
  86 ></TABLE
  87 ><HR
  88 ALIGN="LEFT"
  89 WIDTH="100%"></DIV
  90 ><H1
  91 ><A
  92 NAME="SQL-ALTERSEQUENCE"
  93 ></A
  94 >ALTER SEQUENCE</H1
  95 ><DIV
  96 CLASS="REFNAMEDIV"
  97 ><A
  98 NAME="AEN35119"
  99 ></A
 100 ><H2
 101 >Name</H2
 102 >   ALTER SEQUENCE
 103   &nbsp;--&nbsp;   alter the definition of a sequence generator
 104   </DIV
 105 ><A
 106 NAME="AEN35122"
 107 ></A
 108 ><DIV
 109 CLASS="REFSYNOPSISDIV"
 110 ><A
 111 NAME="AEN35124"
 112 ></A
 113 ><H2
 114 >Synopsis</H2
 115 ><PRE
 116 CLASS="SYNOPSIS"
 117 >ALTER SEQUENCE <VAR
 118 CLASS="REPLACEABLE"
 119 >name</VAR
 120 > [ INCREMENT [ BY ] <VAR
 121 CLASS="REPLACEABLE"
 122 >increment</VAR
 123 > ]
 124     [ MINVALUE <VAR
 125 CLASS="REPLACEABLE"
 126 >minvalue</VAR
 127 > | NO MINVALUE ] [ MAXVALUE <VAR
 128 CLASS="REPLACEABLE"
 129 >maxvalue</VAR
 130 > | NO MAXVALUE ]
 131     [ RESTART [ WITH ] <VAR
 132 CLASS="REPLACEABLE"
 133 >start</VAR
 134 > ] [ CACHE <VAR
 135 CLASS="REPLACEABLE"
 136 >cache</VAR
 137 > ] [ [ NO ] CYCLE ]
 138   </PRE
 139 ></DIV
 140 ><DIV
 141 CLASS="REFSECT1"
 142 ><A
 143 NAME="AEN35132"
 144 ></A
 145 ><H2
 146 >Description</H2
 147 ><P
 148 >   <TT
 149 CLASS="COMMAND"
 150 >ALTER SEQUENCE</TT
 151 > changes the parameters of an existing
 152    sequence generator.  Any parameter not specifically set in the
 153    <TT
 154 CLASS="COMMAND"
 155 >ALTER SEQUENCE</TT
 156 > command retains its prior setting.
 157   </P
 158 ></DIV
 159 ><DIV
 160 CLASS="REFSECT1"
 161 ><A
 162 NAME="AEN35137"
 163 ></A
 164 ><H2
 165 >Parameters</H2
 166 ><P
 167 >    <P
 168 ></P
 169 ></P><DIV
 170 CLASS="VARIABLELIST"
 171 ><DL
 172 ><DT
 173 ><VAR
 174 CLASS="REPLACEABLE"
 175 >name</VAR
 176 ></DT
 177 ><DD
 178 ><P
 179 >        The name (optionally schema-qualified) of a sequence to be altered.
 180        </P
 181 ></DD
 182 ><DT
 183 ><VAR
 184 CLASS="REPLACEABLE"
 185 >increment</VAR
 186 ></DT
 187 ><DD
 188 ><P
 189 >        The clause <TT
 190 CLASS="LITERAL"
 191 >INCREMENT BY <VAR
 192 CLASS="REPLACEABLE"
 193 >increment</VAR
 194 ></TT
 195 > is
 196         optional. A positive value will make an ascending sequence, a
 197         negative one a descending sequence.  If unspecified, the old
 198         increment value will be maintained.
 199        </P
 200 ></DD
 201 ><DT
 202 ><VAR
 203 CLASS="REPLACEABLE"
 204 >minvalue</VAR
 205 ><BR><TT
 206 CLASS="LITERAL"
 207 >NO MINVALUE</TT
 208 ></DT
 209 ><DD
 210 ><P
 211 >        The optional clause <TT
 212 CLASS="LITERAL"
 213 >MINVALUE <VAR
 214 CLASS="REPLACEABLE"
 215 >minvalue</VAR
 216 ></TT
 217 > determines
 218         the minimum value a sequence can generate. If <TT
 219 CLASS="LITERAL"
 220 >NO
 221         MINVALUE</TT
 222 > is specified, the defaults of 1 and
 223         -2<SUP
 224 >63</SUP
 225 >-1 for ascending and descending sequences,
 226         respectively, will be used.  If neither option is specified,
 227         the current minimum value will be maintained.
 228        </P
 229 ></DD
 230 ><DT
 231 ><VAR
 232 CLASS="REPLACEABLE"
 233 >maxvalue</VAR
 234 ><BR><TT
 235 CLASS="LITERAL"
 236 >NO MAXVALUE</TT
 237 ></DT
 238 ><DD
 239 ><P
 240 >        The optional clause <TT
 241 CLASS="LITERAL"
 242 >MAXVALUE <VAR
 243 CLASS="REPLACEABLE"
 244 >maxvalue</VAR
 245 ></TT
 246 > determines
 247         the maximum value for the sequence. If <TT
 248 CLASS="LITERAL"
 249 >NO
 250         MAXVALUE</TT
 251 > is specified, the defaults are
 252         2<SUP
 253 >63</SUP
 254 >-1 and -1 for ascending and descending
 255         sequences, respectively, will be used.  If neither option is
 256         specified, the current maximum value will be maintained.
 257        </P
 258 ></DD
 259 ><DT
 260 ><VAR
 261 CLASS="REPLACEABLE"
 262 >start</VAR
 263 ></DT
 264 ><DD
 265 ><P
 266 >        The optional clause <TT
 267 CLASS="LITERAL"
 268 >RESTART WITH <VAR
 269 CLASS="REPLACEABLE"
 270 >start</VAR
 271 ></TT
 272 > changes the
 273         current value of the sequence.
 274        </P
 275 ></DD
 276 ><DT
 277 ><VAR
 278 CLASS="REPLACEABLE"
 279 >cache</VAR
 280 ></DT
 281 ><DD
 282 ><P
 283 >        The clause <TT
 284 CLASS="LITERAL"
 285 >CACHE <VAR
 286 CLASS="REPLACEABLE"
 287 >cache</VAR
 288 ></TT
 289 > enables
 290         sequence numbers to be preallocated and stored in memory for
 291         faster access. The minimum value is 1 (only one value can be
 292         generated at a time, i.e., no cache).  If unspecified, the old
 293         cache value will be maintained.
 294        </P
 295 ></DD
 296 ><DT
 297 >CYCLE</DT
 298 ><DD
 299 ><P
 300 >        The optional <TT
 301 CLASS="LITERAL"
 302 >CYCLE</TT
 303 > key word may be used to enable
 304         the sequence to wrap around when the
 305         <VAR
 306 CLASS="REPLACEABLE"
 307 >maxvalue</VAR
 308 > or
 309         <VAR
 310 CLASS="REPLACEABLE"
 311 >minvalue</VAR
 312 > has been
 313         reached by
 314         an ascending or descending sequence respectively. If the limit is
 315         reached, the next number generated will be the
 316         <VAR
 317 CLASS="REPLACEABLE"
 318 >minvalue</VAR
 319 > or
 320         <VAR
 321 CLASS="REPLACEABLE"
 322 >maxvalue</VAR
 323 >,
 324         respectively.
 325        </P
 326 ></DD
 327 ><DT
 328 >NO CYCLE</DT
 329 ><DD
 330 ><P
 331 >        If the optional <TT
 332 CLASS="LITERAL"
 333 >NO CYCLE</TT
 334 > key word is
 335         specified, any calls to <CODE
 336 CLASS="FUNCTION"
 337 >nextval</CODE
 338 > after the
 339         sequence has reached its maximum value will return an error.
 340         If neither <TT
 341 CLASS="LITERAL"
 342 >CYCLE</TT
 343 > or <TT
 344 CLASS="LITERAL"
 345 >NO
 346         CYCLE</TT
 347 > are specified, the old cycle behaviour will be
 348         maintained.
 349        </P
 350 ></DD
 351 ></DL
 352 ></DIV
 353 ><P>
 354    </P
 355 ></DIV
 356 ><DIV
 357 CLASS="REFSECT1"
 358 ><A
 359 NAME="AEN35206"
 360 ></A
 361 ><H2
 362 >Examples</H2
 363 ><P
 364 >   Restart a sequence called <TT
 365 CLASS="LITERAL"
 366 >serial</TT
 367 >, at 105:
 368 </P><PRE
 369 CLASS="PROGRAMLISTING"
 370 >ALTER SEQUENCE serial RESTART WITH 105;</PRE
 371 ><P>
 372   </P
 373 ></DIV
 374 ><DIV
 375 CLASS="REFSECT1"
 376 ><A
 377 NAME="AEN35211"
 378 ></A
 379 ><H2
 380 >Notes</H2
 381 ><P
 382 >   To avoid blocking of concurrent transactions that obtain numbers from the
 383    same sequence, <TT
 384 CLASS="COMMAND"
 385 >ALTER SEQUENCE</TT
 386 > is never rolled back;
 387    the changes take effect immediately and are not reversible.
 388   </P
 389 ><P
 390 >   <TT
 391 CLASS="COMMAND"
 392 >ALTER SEQUENCE</TT
 393 > will not immediately affect
 394    <TT
 395 CLASS="LITERAL"
 396 >nextval</TT
 397 > results in backends,
 398    other than the current one, that have preallocated (cached) sequence
 399    values. They will use up all cached values prior to noticing the changed
 400    sequence parameters.  The current backend will be affected immediately.
 401   </P
 402 ></DIV
 403 ><DIV
 404 CLASS="REFSECT1"
 405 ><A
 406 NAME="AEN35218"
 407 ></A
 408 ><H2
 409 >   Compatibility
 410   </H2
 411 ><DIV
 412 CLASS="REFSECT2"
 413 ><A
 414 NAME="AEN35220"
 415 ></A
 416 ><H3
 417 >    SQL99
 418    </H3
 419 ><P
 420 >    <TT
 421 CLASS="COMMAND"
 422 >ALTER SEQUENCE</TT
 423 > is a <SPAN
 424 CLASS="PRODUCTNAME"
 425 >PostgreSQL</SPAN
 426 >
 427     language extension.
 428     There is no <TT
 429 CLASS="COMMAND"
 430 >ALTER SEQUENCE</TT
 431 > statement
 432     in <ACRONYM
 433 CLASS="ACRONYM"
 434 >SQL99</ACRONYM
 435 >.
 436    </P
 437 ></DIV
 438 ></DIV
 439 ><DIV
 440 CLASS="NAVFOOTER"
 441 ><HR
 442 ALIGN="LEFT"
 443 WIDTH="100%"><TABLE
 444 SUMMARY="Footer navigation table"
 445 WIDTH="100%"
 446 BORDER="0"
 447 CELLPADDING="0"
 448 CELLSPACING="0"
 449 ><TR
 450 ><TD
 451 WIDTH="33%"
 452 ALIGN="left"
 453 VALIGN="top"
 454 ><A
 455 HREF="sql-alterschema.html"
 456 ACCESSKEY="P"
 457 >Prev</A
 458 ></TD
 459 ><TD
 460 WIDTH="34%"
 461 ALIGN="center"
 462 VALIGN="top"
 463 ><A
 464 HREF="index.html"
 465 ACCESSKEY="H"
 466 >Home</A
 467 ></TD
 468 ><TD
 469 WIDTH="33%"
 470 ALIGN="right"
 471 VALIGN="top"
 472 ><A
 473 HREF="sql-altertable.html"
 474 ACCESSKEY="N"
 475 >Next</A
 476 ></TD
 477 ></TR
 478 ><TR
 479 ><TD
 480 WIDTH="33%"
 481 ALIGN="left"
 482 VALIGN="top"
 483 >ALTER SCHEMA</TD
 484 ><TD
 485 WIDTH="34%"
 486 ALIGN="center"
 487 VALIGN="top"
 488 ><A
 489 HREF="sql-commands.html"
 490 ACCESSKEY="U"
 491 >Up</A
 492 ></TD
 493 ><TD
 494 WIDTH="33%"
 495 ALIGN="right"
 496 VALIGN="top"
 497 >ALTER TABLE</TD
 498 ></TR
 499 ></TABLE
 500 ></DIV
 501 ></BODY
 502 ></HTML
 503 >