2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.27 2002/04/23 02:07:16 tgl 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 <replaceable class="parameter">increment</replaceable> ]
25 [ MINVALUE <replaceable class="parameter">minvalue</replaceable> ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> ]
26 [ START <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ 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 <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>
78 The optional clause <option>MINVALUE
79 <replaceable class="parameter">minvalue</replaceable></option>
80 determines the minimum value
81 a sequence can generate. The defaults are 1 and -2^63-1 for
82 ascending and descending sequences, respectively.
88 <term><replaceable class="parameter">maxvalue</replaceable></term>
91 The optional clause <option>MAXVALUE
92 <replaceable class="parameter">maxvalue</replaceable></option>
93 determines the maximum
94 value for the sequence. The defaults are 2^63-1 and -1 for
95 ascending and descending sequences, respectively.
101 <term><replaceable class="parameter">start</replaceable></term>
104 The optional <option>START
105 <replaceable class="parameter">start</replaceable>
106 clause</option> enables the sequence to begin anywhere.
107 The default starting value is
108 <replaceable class="parameter">minvalue</replaceable>
109 for ascending sequences and
110 <replaceable class="parameter">maxvalue</replaceable>
117 <term><replaceable class="parameter">cache</replaceable></term>
120 The <option>CACHE <replaceable class="parameter">cache</replaceable></option> option
121 enables sequence numbers to be preallocated
122 and stored in memory for faster access. The minimum
123 value is 1 (only one value can be generated at a time, i.e., no cache)
124 and this is also the default.
133 The optional CYCLE keyword may be used to enable the sequence
134 to wrap around when the
135 <replaceable class="parameter">maxvalue</replaceable> or
136 <replaceable class="parameter">minvalue</replaceable> has been
138 an ascending or descending sequence respectively. If the limit is
139 reached, the next number generated will be the
140 <replaceable class="parameter">minvalue</replaceable> or
141 <replaceable class="parameter">maxvalue</replaceable>,
143 Without CYCLE, after the limit is reached <function>nextval</> calls
144 will return an error.
152 <refsect2 id="R2-SQL-CREATESEQUENCE-2">
154 <date>1998-09-11</date>
163 <term><computeroutput>
165 </computeroutput></term>
168 Message returned if the command is successful.
173 <term><computeroutput>
174 ERROR: Relation '<replaceable class="parameter">seqname</replaceable>' already exists
175 </computeroutput></term>
178 If the sequence specified already exists.
183 <term><computeroutput>
184 ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">start</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
185 </computeroutput></term>
188 If the specified starting value is out of range.
193 <term><computeroutput>
194 ERROR: DefineSequence: START value (<replaceable class="parameter">start</replaceable>) can't be < MINVALUE (<replaceable class="parameter">min</replaceable>)
195 </computeroutput></term>
198 If the specified starting value is out of range.
203 <term><computeroutput>
204 ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">min</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
205 </computeroutput></term>
208 If the minimum and maximum values are inconsistent.
217 <refsect1 id="R1-SQL-CREATESEQUENCE-1">
219 <date>1998-09-11</date>
225 <command>CREATE SEQUENCE</command> will enter a new sequence number generator
226 into the current database. This involves creating and initializing a
228 table with the name <replaceable class="parameter">seqname</replaceable>.
229 The generator will be owned by the user issuing the command.
233 If a schema name is given then the sequence is created in the
234 specified schema. Otherwise it is created in the current schema (the one
235 at the front of the search path; see <literal>CURRENT_SCHEMA()</>).
236 TEMP sequences exist in a special schema, so a schema name may not be
237 given when creating a TEMP sequence.
238 The sequence name must be distinct from the name of any other sequence,
239 table, index, or view in the same schema.
243 After a sequence is created, you use the functions
244 <function>nextval</function>,
245 <function>currval</function> and
246 <function>setval</function>
247 to operate on the sequence. These functions are documented in
248 the <citetitle>User's Guide</citetitle>.
252 Although you cannot update a sequence directly, you can use a query like
255 SELECT * FROM <replaceable>seqname</replaceable>;
258 to examine the parameters and current state of a sequence. In particular,
259 the <literal>last_value</> field of the sequence shows the last value
260 allocated by any backend process. (Of course, this value may be obsolete
261 by the time it's printed, if other processes are actively doing
262 <function>nextval</> calls.)
267 Unexpected results may be obtained if a <replaceable class="parameter">cache</replaceable> setting greater than one
268 is used for a sequence object that will be used concurrently by multiple
269 backends. Each backend will allocate and cache successive sequence values
270 during one access to the sequence object and increase the sequence
271 object's <literal>last_value</> accordingly. Then, the next <replaceable class="parameter">cache</replaceable>-1 uses of <function>nextval</>
272 within that backend simply return the preallocated values without touching
273 the shared object. So, any numbers allocated but not used within a session
274 will be lost when that session ends. Furthermore, although multiple backends are guaranteed to
275 allocate distinct sequence values, the values may be generated out of
276 sequence when all the backends are considered. (For example, with a <replaceable class="parameter">cache</replaceable>
277 setting of 10, backend A might reserve values 1..10 and return <function>nextval</function>=1,
279 backend B might reserve values 11..20 and return <function>nextval</function>=11 before backend
280 A has generated <literal>nextval</literal>=2.) Thus, with a <replaceable class="parameter">cache</replaceable> setting of one it is safe
281 to assume that <function>nextval</> values are generated sequentially; with a <replaceable class="parameter">cache</replaceable>
282 setting greater than one you should only assume that the <function>nextval</> values
283 are all distinct, not that they are generated purely sequentially.
284 Also, <literal>last_value</> will reflect the latest value reserved by any backend,
285 whether or not it has yet been returned by <function>nextval</>.
286 Another consideration is that a <function>setval</> executed on such a sequence
287 will not be noticed by other backends until they have used up any
288 preallocated values they have cached.
292 <refsect2 id="R2-SQL-CREATESEQUENCE-3">
294 <date>1998-09-11</date>
301 Use <command>DROP SEQUENCE</command> to remove a sequence.
305 Sequences are based on <type>bigint</> arithmetic, so the range cannot
306 exceed the range of an eight-byte integer
307 (-9223372036854775808 to 9223372036854775807). On some older platforms,
308 there may be no compiler support for eight-byte integers, in which case
309 sequences use regular <type>integer</> arithmetic (range
310 -2147483648 to +2147483647).
314 When <replaceable class="parameter">cache</replaceable> is greater than
315 one, each backend uses its own cache to store preallocated numbers.
316 Numbers that are cached but not used in the current session will be
317 lost, resulting in <quote>holes</quote> in the sequence.
322 <refsect1 id="R1-SQL-CREATESEQUENCE-2">
327 Create an ascending sequence called <literal>serial</literal>, starting at 101:
330 CREATE SEQUENCE serial START 101;
333 Select the next number from this sequence:
335 SELECT nextval('serial');
343 Use this sequence in an INSERT:
345 INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
350 Update the sequence value after a COPY FROM:
353 COPY distributors FROM 'input_file';
354 SELECT setval('serial', max(id)) FROM distributors;
360 <refsect1 id="R1-SQL-CREATESEQUENCE-3">
365 <refsect2 id="R2-SQL-CREATESEQUENCE-4">
367 <date>1998-09-11</date>
374 <command>CREATE SEQUENCE</command> is a <productname>PostgreSQL</productname>
376 There is no <command>CREATE SEQUENCE</command> statement
377 in <acronym>SQL92</acronym>.
383 <!-- Keep this comment at the end of the file
388 sgml-minimize-attributes:nil
389 sgml-always-quote-attributes:t
392 sgml-parent-document:nil
393 sgml-default-dtd-file:"../reference.ced"
394 sgml-exposed-tags:nil
395 sgml-local-catalogs:"/usr/lib/sgml/catalog"
396 sgml-local-ecat-files:nil