1<!--
2doc/src/sgml/ref/create_sequence.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="SQL-CREATESEQUENCE">
7 <indexterm zone="sql-createsequence">
8  <primary>CREATE SEQUENCE</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>CREATE SEQUENCE</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>CREATE SEQUENCE</refname>
19  <refpurpose>define a new sequence generator</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23<synopsis>
24CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
25    [ AS <replaceable class="parameter">data_type</replaceable> ]
26    [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
27    [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
28    [ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
29    [ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
30</synopsis>
31 </refsynopsisdiv>
32
33 <refsect1>
34  <title>Description</title>
35
36  <para>
37   <command>CREATE SEQUENCE</command> creates a new sequence number
38   generator.  This involves creating and initializing a new special
39   single-row table with the name <replaceable
40   class="parameter">name</replaceable>.  The generator will be
41   owned by the user issuing the command.
42  </para>
43
44  <para>
45   If a schema name is given then the sequence is created in the
46   specified schema.  Otherwise it is created in the current schema.
47   Temporary sequences exist in a special schema, so a schema name cannot be
48   given when creating a temporary sequence.
49   The sequence name must be distinct from the name of any other sequence,
50   table, index, view, or foreign table in the same schema.
51  </para>
52
53  <para>
54   After a sequence is created, you use the functions
55   <function>nextval</function>,
56   <function>currval</function>, and
57   <function>setval</function>
58   to operate on the sequence.  These functions are documented in
59   <xref linkend="functions-sequence">.
60  </para>
61
62  <para>
63   Although you cannot update a sequence directly, you can use a query like:
64
65<programlisting>
66SELECT * FROM <replaceable>name</replaceable>;
67</programlisting>
68
69   to examine the parameters and current state of a sequence.  In particular,
70   the <literal>last_value</> field of the sequence shows the last value
71   allocated by any session.  (Of course, this value might be obsolete
72   by the time it's printed, if other sessions are actively doing
73   <function>nextval</> calls.)
74  </para>
75 </refsect1>
76
77 <refsect1>
78  <title>Parameters</title>
79
80  <variablelist>
81   <varlistentry>
82    <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
83    <listitem>
84     <para>
85      If specified, the sequence object is created only for this
86      session, and is automatically dropped on session exit.  Existing
87      permanent sequences with the same name are not visible (in this
88      session) while the temporary sequence exists, unless they are
89      referenced with schema-qualified names.
90     </para>
91    </listitem>
92   </varlistentry>
93
94   <varlistentry>
95    <term><literal>IF NOT EXISTS</literal></term>
96    <listitem>
97     <para>
98      Do not throw an error if a relation with the same name already exists.
99      A notice is issued in this case. Note that there is no guarantee that
100      the existing relation is anything like the sequence that would have
101      been created - it might not even be a sequence.
102     </para>
103    </listitem>
104   </varlistentry>
105
106   <varlistentry>
107    <term><replaceable class="parameter">name</replaceable></term>
108    <listitem>
109     <para>
110      The name (optionally schema-qualified) of the sequence to be created.
111     </para>
112    </listitem>
113   </varlistentry>
114
115   <varlistentry>
116    <term><replaceable class="parameter">data_type</replaceable></term>
117    <listitem>
118     <para>
119      The optional
120      clause <literal>AS <replaceable class="parameter">data_type</replaceable></literal>
121      specifies the data type of the sequence.  Valid types are
122      <literal>smallint</literal>, <literal>integer</literal>,
123      and <literal>bigint</literal>.  <literal>bigint</literal> is the
124      default.  The data type determines the default minimum and maximum
125      values of the sequence.
126     </para>
127    </listitem>
128   </varlistentry>
129
130   <varlistentry>
131    <term><replaceable class="parameter">increment</replaceable></term>
132    <listitem>
133     <para>
134      The optional clause <literal>INCREMENT BY <replaceable
135      class="parameter">increment</replaceable></literal> specifies
136      which value is added to the current sequence value to create a
137      new value.  A positive value will make an ascending sequence, a
138      negative one a descending sequence.  The default value is 1.
139     </para>
140    </listitem>
141   </varlistentry>
142
143   <varlistentry>
144    <term><replaceable class="parameter">minvalue</replaceable></term>
145    <term><literal>NO MINVALUE</literal></term>
146    <listitem>
147     <para>
148      The optional clause <literal>MINVALUE <replaceable
149      class="parameter">minvalue</replaceable></literal> determines
150      the minimum value a sequence can generate. If this clause is not
151      supplied or <option>NO MINVALUE</option> is specified, then
152      defaults will be used.  The default for an ascending sequence is 1.  The
153      default for a descending sequence is the minimum value of the data type.
154     </para>
155    </listitem>
156   </varlistentry>
157
158   <varlistentry>
159    <term><replaceable class="parameter">maxvalue</replaceable></term>
160    <term><literal>NO MAXVALUE</literal></term>
161    <listitem>
162     <para>
163      The optional clause <literal>MAXVALUE <replaceable
164      class="parameter">maxvalue</replaceable></literal> determines
165      the maximum value for the sequence. If this clause is not
166      supplied or <option>NO MAXVALUE</option> is specified, then
167      default values will be used.  The default for an ascending sequence is
168      the maximum value of the data type.  The default for a descending
169      sequence is -1.
170     </para>
171    </listitem>
172   </varlistentry>
173
174   <varlistentry>
175    <term><replaceable class="parameter">start</replaceable></term>
176    <listitem>
177     <para>
178      The optional clause <literal>START WITH <replaceable
179      class="parameter">start</replaceable> </literal> allows the
180      sequence to begin anywhere.  The default starting value is
181      <replaceable class="parameter">minvalue</replaceable> for
182      ascending sequences and <replaceable
183      class="parameter">maxvalue</replaceable> for descending ones.
184     </para>
185    </listitem>
186   </varlistentry>
187
188   <varlistentry>
189    <term><replaceable class="parameter">cache</replaceable></term>
190    <listitem>
191     <para>
192      The optional clause <literal>CACHE <replaceable
193      class="parameter">cache</replaceable></literal> specifies how
194      many sequence numbers are to be preallocated and stored in
195      memory for faster access. The minimum value is 1 (only one value
196      can be generated at a time, i.e., no cache), and this is also the
197      default.
198     </para>
199    </listitem>
200   </varlistentry>
201
202   <varlistentry>
203    <term><literal>CYCLE</literal></term>
204    <term><literal>NO CYCLE</literal></term>
205    <listitem>
206     <para>
207      The <literal>CYCLE</literal> option allows the sequence to wrap
208      around when the <replaceable
209      class="parameter">maxvalue</replaceable> or <replaceable
210      class="parameter">minvalue</replaceable> has been reached by an
211      ascending or descending sequence respectively. If the limit is
212      reached, the next number generated will be the <replaceable
213      class="parameter">minvalue</replaceable> or <replaceable
214      class="parameter">maxvalue</replaceable>, respectively.
215     </para>
216
217     <para>
218      If <literal>NO CYCLE</literal> is specified, any calls to
219      <function>nextval</function> after the sequence has reached its
220      maximum value will return an error.  If neither
221      <literal>CYCLE</literal> or <literal>NO CYCLE</literal> are
222      specified, <literal>NO CYCLE</literal> is the default.
223     </para>
224    </listitem>
225   </varlistentry>
226
227   <varlistentry>
228    <term><literal>OWNED BY</literal> <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable></term>
229    <term><literal>OWNED BY NONE</literal></term>
230    <listitem>
231     <para>
232      The <literal>OWNED BY</literal> option causes the sequence to be
233      associated with a specific table column, such that if that column
234      (or its whole table) is dropped, the sequence will be automatically
235      dropped as well.  The specified table must have the same owner and be in
236      the same schema as the sequence.
237      <literal>OWNED BY NONE</literal>, the default, specifies that there
238      is no such association.
239     </para>
240    </listitem>
241   </varlistentry>
242  </variablelist>
243 </refsect1>
244
245 <refsect1>
246  <title>Notes</title>
247
248  <para>
249   Use <command>DROP SEQUENCE</command> to remove a sequence.
250  </para>
251
252  <para>
253   Sequences are based on <type>bigint</> arithmetic, so the range
254   cannot exceed the range of an eight-byte integer
255   (-9223372036854775808 to 9223372036854775807).
256  </para>
257
258  <para>
259   Because <function>nextval</> and <function>setval</> calls are never
260   rolled back, sequence objects cannot be used if <quote>gapless</>
261   assignment of sequence numbers is needed.  It is possible to build
262   gapless assignment by using exclusive locking of a table containing a
263   counter; but this solution is much more expensive than sequence
264   objects, especially if many transactions need sequence numbers
265   concurrently.
266  </para>
267
268  <para>
269   Unexpected results might be obtained if a <replaceable
270   class="parameter">cache</replaceable> setting greater than one is
271   used for a sequence object that will be used concurrently by
272   multiple sessions.  Each session will allocate and cache successive
273   sequence values during one access to the sequence object and
274   increase the sequence object's <literal>last_value</> accordingly.
275   Then, the next <replaceable class="parameter">cache</replaceable>-1
276   uses of <function>nextval</> within that session simply return the
277   preallocated values without touching the sequence object.  So, any
278   numbers allocated but not used within a session will be lost when
279   that session ends, resulting in <quote>holes</quote> in the
280   sequence.
281  </para>
282
283  <para>
284   Furthermore, although multiple sessions are guaranteed to allocate
285   distinct sequence values, the values might be generated out of
286   sequence when all the sessions are considered.  For example, with
287   a <replaceable class="parameter">cache</replaceable> setting of 10,
288   session A might reserve values 1..10 and return
289   <function>nextval</function>=1, then session B might reserve values
290   11..20 and return <function>nextval</function>=11 before session A
291   has generated <literal>nextval</literal>=2.  Thus, with a
292   <replaceable class="parameter">cache</replaceable> setting of one
293   it is safe to assume that <function>nextval</> values are generated
294   sequentially; with a <replaceable
295   class="parameter">cache</replaceable> setting greater than one you
296   should only assume that the <function>nextval</> values are all
297   distinct, not that they are generated purely sequentially.  Also,
298   <literal>last_value</> will reflect the latest value reserved by
299   any session, whether or not it has yet been returned by
300   <function>nextval</>.
301  </para>
302
303  <para>
304   Another consideration is that a <function>setval</> executed on
305   such a sequence will not be noticed by other sessions until they
306   have used up any preallocated values they have cached.
307  </para>
308 </refsect1>
309
310 <refsect1>
311  <title>Examples</title>
312
313  <para>
314   Create an ascending sequence called <literal>serial</literal>, starting at 101:
315<programlisting>
316CREATE SEQUENCE serial START 101;
317</programlisting>
318  </para>
319
320  <para>
321   Select the next number from this sequence:
322<programlisting>
323SELECT nextval('serial');
324
325 nextval
326---------
327     101
328</programlisting>
329  </para>
330
331  <para>
332   Select the next number from this sequence:
333<programlisting>
334SELECT nextval('serial');
335
336 nextval
337---------
338     102
339</programlisting>
340  </para>
341
342  <para>
343   Use this sequence in an <command>INSERT</command> command:
344<programlisting>
345INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
346</programlisting>
347  </para>
348
349  <para>
350   Update the sequence value after a <command>COPY FROM</command>:
351<programlisting>
352BEGIN;
353COPY distributors FROM 'input_file';
354SELECT setval('serial', max(id)) FROM distributors;
355END;
356</programlisting></para>
357 </refsect1>
358
359 <refsect1>
360  <title>Compatibility</title>
361
362  <para>
363   <command>CREATE SEQUENCE</command> conforms to the <acronym>SQL</acronym>
364   standard, with the following exceptions:
365   <itemizedlist>
366    <listitem>
367     <para>
368      Obtaining the next value is done using the <function>nextval()</>
369      function instead of the standard's <command>NEXT VALUE FOR</command>
370      expression.
371     </para>
372    </listitem>
373    <listitem>
374     <para>
375      The <literal>OWNED BY</> clause is a <productname>PostgreSQL</>
376      extension.
377     </para>
378    </listitem>
379   </itemizedlist></para>
380 </refsect1>
381
382 <refsect1>
383  <title>See Also</title>
384
385  <simplelist type="inline">
386   <member><xref linkend="sql-altersequence"></member>
387   <member><xref linkend="sql-dropsequence"></member>
388  </simplelist>
389 </refsect1>
390
391</refentry>
392