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