2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.31 2003/03/20 07:02:07 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATESEQUENCE">
8 <refentrytitle id="sql-createsequence-title">CREATE SEQUENCE</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
16 define a new sequence generator
21 <date>1999-07-20</date>
24 CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
25 [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
26 [ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
29 <refsect2 id="R2-SQL-CREATESEQUENCE-1">
31 <date>1998-09-11</date>
40 <term>TEMPORARY or TEMP</term>
43 If specified, the sequence object is created only for this session,
44 and is automatically dropped on session exit.
45 Existing permanent sequences with the same name are not visible
46 (in this session) while the temporary sequence exists, unless
47 they are referenced with schema-qualified names.
53 <term><replaceable class="parameter">seqname</replaceable></term>
56 The name (optionally schema-qualified) of a sequence to be created.
62 <term><replaceable class="parameter">increment</replaceable></term>
66 <option>INCREMENT BY <replaceable class="parameter">increment</replaceable></option>
67 clause is optional. A positive value will make an
68 ascending sequence, a negative one a descending sequence.
69 The default value is one (1).
75 <term><replaceable class="parameter">minvalue</replaceable></term>
76 <term>NO MINVALUE</term>
79 The optional clause <option>MINVALUE
80 <replaceable class="parameter">minvalue</replaceable></option>
81 determines the minimum value
82 a sequence can generate. If this clause is not supplied or <option>NO MINVALUE</option>
83 is specified, then defaults will be used. The defaults are 1 and -2^63-1 for
84 ascending and descending sequences, respectively.
90 <term><replaceable class="parameter">maxvalue</replaceable></term>
91 <term>NO MAXVALUE</term>
94 The optional clause <option>MAXVALUE
95 <replaceable class="parameter">maxvalue</replaceable></option>
96 determines the maximum
97 value for the sequence. If this clause is not supplied or
98 <option>NO MAXVALUE</option> is specified, then default values will be used.
99 The defaults are 2^63-1 and -1 for ascending and descending sequences, respectively.
105 <term><replaceable class="parameter">start</replaceable></term>
108 The optional <option>START WITH
109 <replaceable class="parameter">start</replaceable>
110 clause</option> enables the sequence to begin anywhere.
111 The default starting value is
112 <replaceable class="parameter">minvalue</replaceable>
113 for ascending sequences and
114 <replaceable class="parameter">maxvalue</replaceable>
121 <term><replaceable class="parameter">cache</replaceable></term>
124 The <option>CACHE <replaceable class="parameter">cache</replaceable></option> option
125 enables sequence numbers to be preallocated
126 and stored in memory for faster access. The minimum
127 value is 1 (only one value can be generated at a time, i.e., no cache)
128 and this is also the default.
137 The optional <option>CYCLE</option> keyword may be used to enable
138 the sequence to wrap around when the
139 <replaceable class="parameter">maxvalue</replaceable> or
140 <replaceable class="parameter">minvalue</replaceable> has been
142 an ascending or descending sequence respectively. If the limit is
143 reached, the next number generated will be the
144 <replaceable class="parameter">minvalue</replaceable> or
145 <replaceable class="parameter">maxvalue</replaceable>,
152 <term>NO CYCLE</term>
155 If the optional <option>NO CYCLE</option> keyword is specified, any
156 calls to <function>nextval</function> after the sequence has reached
157 its maximum value will return an error. If neither
158 <option>CYCLE</option> or <option>NO CYCLE</option> are specified,
159 <option>NO CYCLE</option> is the default.
167 <refsect2 id="R2-SQL-CREATESEQUENCE-2">
169 <date>1998-09-11</date>
178 <term><computeroutput>
180 </computeroutput></term>
183 Message returned if the command is successful.
188 <term><computeroutput>
189 ERROR: Relation '<replaceable class="parameter">seqname</replaceable>' already exists
190 </computeroutput></term>
193 If the sequence specified already exists.
198 <term><computeroutput>
199 ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">start</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
200 </computeroutput></term>
203 If the specified starting value is out of range.
208 <term><computeroutput>
209 ERROR: DefineSequence: START value (<replaceable class="parameter">start</replaceable>) can't be < MINVALUE (<replaceable class="parameter">min</replaceable>)
210 </computeroutput></term>
213 If the specified starting value is out of range.
218 <term><computeroutput>
219 ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">min</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
220 </computeroutput></term>
223 If the minimum and maximum values are inconsistent.
232 <refsect1 id="R1-SQL-CREATESEQUENCE-1">
234 <date>1998-09-11</date>
240 <command>CREATE SEQUENCE</command> will enter a new sequence number generator
241 into the current database. This involves creating and initializing a
243 table with the name <replaceable class="parameter">seqname</replaceable>.
244 The generator will be owned by the user issuing the command.
248 If a schema name is given then the sequence is created in the
249 specified schema. Otherwise it is created in the current schema (the one
250 at the front of the search path; see <literal>CURRENT_SCHEMA()</>).
251 TEMP sequences exist in a special schema, so a schema name may not be
252 given when creating a TEMP sequence.
253 The sequence name must be distinct from the name of any other sequence,
254 table, index, or view in the same schema.
258 After a sequence is created, you use the functions
259 <function>nextval</function>,
260 <function>currval</function> and
261 <function>setval</function>
262 to operate on the sequence. These functions are documented in
267 Although you cannot update a sequence directly, you can use a query like
270 SELECT * FROM <replaceable>seqname</replaceable>;
273 to examine the parameters and current state of a sequence. In particular,
274 the <literal>last_value</> field of the sequence shows the last value
275 allocated by any backend process. (Of course, this value may be obsolete
276 by the time it's printed, if other processes are actively doing
277 <function>nextval</> calls.)
282 Unexpected results may be obtained if a <replaceable class="parameter">cache</replaceable> setting greater than one
283 is used for a sequence object that will be used concurrently by multiple
284 backends. Each backend will allocate and cache successive sequence values
285 during one access to the sequence object and increase the sequence
286 object's <literal>last_value</> accordingly. Then, the next <replaceable class="parameter">cache</replaceable>-1 uses of <function>nextval</>
287 within that backend simply return the preallocated values without touching
288 the shared object. So, any numbers allocated but not used within a session
289 will be lost when that session ends. Furthermore, although multiple backends are guaranteed to
290 allocate distinct sequence values, the values may be generated out of
291 sequence when all the backends are considered. (For example, with a <replaceable class="parameter">cache</replaceable>
292 setting of 10, backend A might reserve values 1..10 and return <function>nextval</function>=1,
294 backend B might reserve values 11..20 and return <function>nextval</function>=11 before backend
295 A has generated <literal>nextval</literal>=2.) Thus, with a <replaceable class="parameter">cache</replaceable> setting of one it is safe
296 to assume that <function>nextval</> values are generated sequentially; with a <replaceable class="parameter">cache</replaceable>
297 setting greater than one you should only assume that the <function>nextval</> values
298 are all distinct, not that they are generated purely sequentially.
299 Also, <literal>last_value</> will reflect the latest value reserved by any backend,
300 whether or not it has yet been returned by <function>nextval</>.
301 Another consideration is that a <function>setval</> executed on such a sequence
302 will not be noticed by other backends until they have used up any
303 preallocated values they have cached.
307 <refsect2 id="R2-SQL-CREATESEQUENCE-3">
309 <date>1998-09-11</date>
316 Use <command>DROP SEQUENCE</command> to remove a sequence.
320 Sequences are based on <type>bigint</> arithmetic, so the range cannot
321 exceed the range of an eight-byte integer
322 (-9223372036854775808 to 9223372036854775807). On some older platforms,
323 there may be no compiler support for eight-byte integers, in which case
324 sequences use regular <type>integer</> arithmetic (range
325 -2147483648 to +2147483647).
329 When <replaceable class="parameter">cache</replaceable> is greater than
330 one, each backend uses its own cache to store preallocated numbers.
331 Numbers that are cached but not used in the current session will be
332 lost, resulting in <quote>holes</quote> in the sequence.
337 <refsect1 id="R1-SQL-CREATESEQUENCE-2">
342 Create an ascending sequence called <literal>serial</literal>, starting at 101:
345 CREATE SEQUENCE serial START 101;
348 Select the next number from this sequence:
350 SELECT nextval('serial');
358 Use this sequence in an INSERT:
360 INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
365 Update the sequence value after a COPY FROM:
368 COPY distributors FROM 'input_file';
369 SELECT setval('serial', max(id)) FROM distributors;
375 <refsect1 id="R1-SQL-CREATESEQUENCE-3">
380 <refsect2 id="R2-SQL-CREATESEQUENCE-4">
382 <date>1998-09-11</date>
389 <command>CREATE SEQUENCE</command> is a <productname>PostgreSQL</productname>
391 There is no <command>CREATE SEQUENCE</command> statement
392 in <acronym>SQL92</acronym>.
398 <!-- Keep this comment at the end of the file
403 sgml-minimize-attributes:nil
404 sgml-always-quote-attributes:t
407 sgml-parent-document:nil
408 sgml-default-dtd-file:"../reference.ced"
409 sgml-exposed-tags:nil
410 sgml-local-catalogs:"/usr/lib/sgml/catalog"
411 sgml-local-ecat-files:nil