2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.24 2002/01/20 22:19:56 petere Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATESEQUENCE">
8 <refentrytitle id="sql-createsequence-title">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 define a new sequence generator
23 <date>1999-07-20</date>
26 CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</replaceable> [ INCREMENT <replaceable class="parameter">increment</replaceable> ]
27 [ MINVALUE <replaceable class="parameter">minvalue</replaceable> ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> ]
28 [ START <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ CYCLE ]
31 <refsect2 id="R2-SQL-CREATESEQUENCE-1">
33 <date>1998-09-11</date>
42 <term>TEMPORARY or TEMP</term>
45 If specified, the sequence object is created only for this session,
46 and is automatically dropped on session exit.
47 Existing permanent sequences with the same name are not visible
48 (in this session) while the temporary sequence exists.
54 <term><replaceable class="parameter">seqname</replaceable></term>
57 The name of a sequence to be created.
63 <term><replaceable class="parameter">increment</replaceable></term>
67 <option>INCREMENT <replaceable class="parameter">increment</replaceable></option>
68 clause is optional. A positive value will make an
69 ascending sequence, a negative one a descending sequence.
70 The default value is one (1).
76 <term><replaceable class="parameter">minvalue</replaceable></term>
79 The optional clause <option>MINVALUE
80 <replaceable class="parameter">minvalue</replaceable></option>
81 determines the minimum value
82 a sequence can generate. The defaults are 1 and -2^63-1 for
83 ascending and descending sequences, respectively.
89 <term><replaceable class="parameter">maxvalue</replaceable></term>
92 The optional clause <option>MAXVALUE
93 <replaceable class="parameter">maxvalue</replaceable></option>
94 determines the maximum
95 value for the sequence. The defaults are 2^63-1 and -1 for
96 ascending and descending sequences, respectively.
102 <term><replaceable class="parameter">start</replaceable></term>
105 The optional <option>START
106 <replaceable class="parameter">start</replaceable>
107 clause</option> enables the sequence to begin anywhere.
108 The default starting value is
109 <replaceable class="parameter">minvalue</replaceable>
110 for ascending sequences and
111 <replaceable class="parameter">maxvalue</replaceable>
118 <term><replaceable class="parameter">cache</replaceable></term>
121 The <option>CACHE <replaceable class="parameter">cache</replaceable></option> option
122 enables sequence numbers to be preallocated
123 and stored in memory for faster access. The minimum
124 value is 1 (only one value can be generated at a time, i.e., no cache)
125 and this is also the default.
134 The optional CYCLE keyword may be used to enable the sequence
135 to wrap around when the
136 <replaceable class="parameter">maxvalue</replaceable> or
137 <replaceable class="parameter">minvalue</replaceable> has been
139 an ascending or descending sequence respectively. If the limit is
140 reached, the next number generated will be the
141 <replaceable class="parameter">minvalue</replaceable> or
142 <replaceable class="parameter">maxvalue</replaceable>,
144 Without CYCLE, after the limit is reached <function>nextval</> calls
145 will return an error.
153 <refsect2 id="R2-SQL-CREATESEQUENCE-2">
155 <date>1998-09-11</date>
164 <term><computeroutput>
166 </computeroutput></term>
169 Message returned if the command is successful.
174 <term><computeroutput>
175 ERROR: Relation '<replaceable class="parameter">seqname</replaceable>' already exists
176 </computeroutput></term>
179 If the sequence specified already exists.
184 <term><computeroutput>
185 ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">start</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
186 </computeroutput></term>
189 If the specified starting value is out of range.
194 <term><computeroutput>
195 ERROR: DefineSequence: START value (<replaceable class="parameter">start</replaceable>) can't be < MINVALUE (<replaceable class="parameter">min</replaceable>)
196 </computeroutput></term>
199 If the specified starting value is out of range.
204 <term><computeroutput>
205 ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">min</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
206 </computeroutput></term>
209 If the minimum and maximum values are inconsistent.
218 <refsect1 id="R1-SQL-CREATESEQUENCE-1">
220 <date>1998-09-11</date>
226 <command>CREATE SEQUENCE</command> will enter a new sequence number generator
227 into the current database. This involves creating and initializing a
229 table with the name <replaceable class="parameter">seqname</replaceable>.
230 The generator will be owned by the user issuing the command.
234 After a sequence is created, you use the functions
235 <function>nextval</function>,
236 <function>currval</function> and
237 <function>setval</function>
238 to operate on the sequence. These functions are documented in
239 the <citetitle>User's Guide</citetitle>.
243 Although you cannot update a sequence directly, you can use a query like
246 SELECT * FROM <replaceable>seqname</replaceable>;
249 to examine the parameters and current state of a sequence. In particular,
250 the <literal>last_value</> field of the sequence shows the last value
251 allocated by any backend process. (Of course, this value may be obsolete
252 by the time it's printed, if other processes are actively doing
253 <function>nextval</> calls.)
258 Unexpected results may be obtained if a <replaceable class="parameter">cache</replaceable> setting greater than one
259 is used for a sequence object that will be used concurrently by multiple
260 backends. Each backend will allocate and cache successive sequence values
261 during one access to the sequence object and increase the sequence
262 object's <literal>last_value</> accordingly. Then, the next <replaceable class="parameter">cache</replaceable>-1 uses of <function>nextval</>
263 within that backend simply return the preallocated values without touching
264 the shared object. So, any numbers allocated but not used within a session
265 will be lost when that session ends. Furthermore, although multiple backends are guaranteed to
266 allocate distinct sequence values, the values may be generated out of
267 sequence when all the backends are considered. (For example, with a <replaceable class="parameter">cache</replaceable>
268 setting of 10, backend A might reserve values 1..10 and return <function>nextval</function>=1,
270 backend B might reserve values 11..20 and return <function>nextval</function>=11 before backend
271 A has generated nextval=2.) Thus, with a <replaceable class="parameter">cache</replaceable> setting of one it is safe
272 to assume that <function>nextval</> values are generated sequentially; with a <replaceable class="parameter">cache</replaceable>
273 setting greater than one you should only assume that the <function>nextval</> values
274 are all distinct, not that they are generated purely sequentially.
275 Also, <literal>last_value</> will reflect the latest value reserved by any backend,
276 whether or not it has yet been returned by <function>nextval</>.
277 Another consideration is that a <function>setval</> executed on such a sequence
278 will not be noticed by other backends until they have used up any
279 preallocated values they have cached.
283 <refsect2 id="R2-SQL-CREATESEQUENCE-3">
285 <date>1998-09-11</date>
292 Use <command>DROP SEQUENCE</command> to remove a sequence.
296 Sequences are based on <type>bigint</> arithmetic, so the range cannot
297 exceed the range of an eight-byte integer
298 (-9223372036854775808 to 9223372036854775807). On some older platforms,
299 there may be no compiler support for eight-byte integers, in which case
300 sequences use regular <type>integer</> arithmetic (range
301 -2147483648 to +2147483647).
305 When <replaceable class="parameter">cache</replaceable> is greater than
306 one, each backend uses its own cache to store preallocated numbers.
307 Numbers that are cached but not used in the current session will be
308 lost, resulting in <quote>holes</quote> in the sequence.
313 <refsect1 id="R1-SQL-CREATESEQUENCE-2">
318 Create an ascending sequence called <literal>serial</literal>, starting at 101:
321 CREATE SEQUENCE serial START 101;
324 Select the next number from this sequence:
326 SELECT nextval('serial');
334 Use this sequence in an INSERT:
336 INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
341 Update the sequence value after a COPY FROM:
344 COPY distributors FROM 'input_file';
345 SELECT setval('serial', max(id)) FROM distributors;
351 <refsect1 id="R1-SQL-CREATESEQUENCE-3">
356 <refsect2 id="R2-SQL-CREATESEQUENCE-4">
358 <date>1998-09-11</date>
365 <command>CREATE SEQUENCE</command> is a <productname>PostgreSQL</productname>
367 There is no <command>CREATE SEQUENCE</command> statement
368 in <acronym>SQL92</acronym>.
374 <!-- Keep this comment at the end of the file
379 sgml-minimize-attributes:nil
380 sgml-always-quote-attributes:t
383 sgml-parent-document:nil
384 sgml-default-dtd-file:"../reference.ced"
385 sgml-exposed-tags:nil
386 sgml-local-catalogs:"/usr/lib/sgml/catalog"
387 sgml-local-ecat-files:nil