Debian Codegraph @

doc/src/sgml/ref/create_sequence.sgml

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