codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE SEQUENCE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createschema.html" title="CREATE SCHEMA" /><link rel="next" href="sql-createserver.html" title="CREATE SERVER" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE SEQUENCE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createschema.html" title="CREATE SCHEMA">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createserver.html" title="CREATE SERVER">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATESEQUENCE"><div class="titlepage"></div><a id="id-1.9.3.81.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE SEQUENCE</span></h2><p>CREATE SEQUENCE — define a new sequence generator</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] <em class="replaceable"><code>name</code></em>4    [ AS <em class="replaceable"><code>data_type</code></em> ]5    [ INCREMENT [ BY ] <em class="replaceable"><code>increment</code></em> ]6    [ MINVALUE <em class="replaceable"><code>minvalue</code></em> | NO MINVALUE ] [ MAXVALUE <em class="replaceable"><code>maxvalue</code></em> | NO MAXVALUE ]7    [ START [ WITH ] <em class="replaceable"><code>start</code></em> ] [ CACHE <em class="replaceable"><code>cache</code></em> ] [ [ NO ] CYCLE ]8    [ OWNED BY { <em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em> | NONE } ]9</pre></div><div class="refsect1" id="id-1.9.3.81.5"><h2>Description</h2><p>10   <code class="command">CREATE SEQUENCE</code> creates a new sequence number11   generator.  This involves creating and initializing a new special12   single-row table with the name <em class="replaceable"><code>name</code></em>.  The generator will be13   owned by the user issuing the command.14  </p><p>15   If a schema name is given then the sequence is created in the16   specified schema.  Otherwise it is created in the current schema.17   Temporary sequences exist in a special schema, so a schema name cannot be18   given when creating a temporary sequence.19   The sequence name must be distinct from the name of any other relation20   (table, sequence, index, view, materialized view, or foreign table) in21   the same schema.22  </p><p>23   After a sequence is created, you use the functions24   <code class="function">nextval</code>,25   <code class="function">currval</code>, and26   <code class="function">setval</code>27   to operate on the sequence.  These functions are documented in28   <a class="xref" href="functions-sequence.html" title="9.17. Sequence Manipulation Functions">Section 9.17</a>.29  </p><p>30   Although you cannot update a sequence directly, you can use a query like:31 32</p><pre class="programlisting">33SELECT * FROM <em class="replaceable"><code>name</code></em>;34</pre><p>35 36   to examine the parameters and current state of a sequence.  In particular,37   the <code class="literal">last_value</code> field of the sequence shows the last value38   allocated by any session.  (Of course, this value might be obsolete39   by the time it's printed, if other sessions are actively doing40   <code class="function">nextval</code> calls.)41  </p></div><div class="refsect1" id="id-1.9.3.81.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">TEMPORARY</code> or <code class="literal">TEMP</code></span></dt><dd><p>42      If specified, the sequence object is created only for this43      session, and is automatically dropped on session exit.  Existing44      permanent sequences with the same name are not visible (in this45      session) while the temporary sequence exists, unless they are46      referenced with schema-qualified names.47     </p></dd><dt><span class="term"><code class="literal">UNLOGGED</code></span></dt><dd><p>48      If specified, the sequence is created as an unlogged sequence.  Changes49      to unlogged sequences are not written to the write-ahead log.  They are50      not crash-safe: an unlogged sequence is automatically reset to its51      initial state after a crash or unclean shutdown.  Unlogged sequences are52      also not replicated to standby servers.53     </p><p>54      Unlike unlogged tables, unlogged sequences do not offer a significant55      performance advantage.  This option is mainly intended for sequences56      associated with unlogged tables via identity columns or serial columns.57      In those cases, it usually wouldn't make sense to have the sequence58      WAL-logged and replicated but not its associated table.59     </p></dd><dt><span class="term"><code class="literal">IF NOT EXISTS</code></span></dt><dd><p>60      Do not throw an error if a relation with the same name already exists.61      A notice is issued in this case. Note that there is no guarantee that62      the existing relation is anything like the sequence that would have63      been created — it might not even be a sequence.64     </p></dd><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>65      The name (optionally schema-qualified) of the sequence to be created.66     </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>67      The optional68      clause <code class="literal">AS <em class="replaceable"><code>data_type</code></em></code>69      specifies the data type of the sequence.  Valid types are70      <code class="literal">smallint</code>, <code class="literal">integer</code>,71      and <code class="literal">bigint</code>.  <code class="literal">bigint</code> is the72      default.  The data type determines the default minimum and maximum73      values of the sequence.74     </p></dd><dt><span class="term"><em class="replaceable"><code>increment</code></em></span></dt><dd><p>75      The optional clause <code class="literal">INCREMENT BY <em class="replaceable"><code>increment</code></em></code> specifies76      which value is added to the current sequence value to create a77      new value.  A positive value will make an ascending sequence, a78      negative one a descending sequence.  The default value is 1.79     </p></dd><dt><span class="term"><em class="replaceable"><code>minvalue</code></em><br /></span><span class="term"><code class="literal">NO MINVALUE</code></span></dt><dd><p>80      The optional clause <code class="literal">MINVALUE <em class="replaceable"><code>minvalue</code></em></code> determines81      the minimum value a sequence can generate. If this clause is not82      supplied or <code class="option">NO MINVALUE</code> is specified, then83      defaults will be used.  The default for an ascending sequence is 1.  The84      default for a descending sequence is the minimum value of the data type.85     </p></dd><dt><span class="term"><em class="replaceable"><code>maxvalue</code></em><br /></span><span class="term"><code class="literal">NO MAXVALUE</code></span></dt><dd><p>86      The optional clause <code class="literal">MAXVALUE <em class="replaceable"><code>maxvalue</code></em></code> determines87      the maximum value for the sequence. If this clause is not88      supplied or <code class="option">NO MAXVALUE</code> is specified, then89      default values will be used.  The default for an ascending sequence is90      the maximum value of the data type.  The default for a descending91      sequence is -1.92     </p></dd><dt><span class="term"><em class="replaceable"><code>start</code></em></span></dt><dd><p>93      The optional clause <code class="literal">START WITH <em class="replaceable"><code>start</code></em> </code> allows the94      sequence to begin anywhere.  The default starting value is95      <em class="replaceable"><code>minvalue</code></em> for96      ascending sequences and <em class="replaceable"><code>maxvalue</code></em> for descending ones.97     </p></dd><dt><span class="term"><em class="replaceable"><code>cache</code></em></span></dt><dd><p>98      The optional clause <code class="literal">CACHE <em class="replaceable"><code>cache</code></em></code> specifies how99      many sequence numbers are to be preallocated and stored in100      memory for faster access. The minimum value is 1 (only one value101      can be generated at a time, i.e., no cache), and this is also the102      default.103     </p></dd><dt><span class="term"><code class="literal">CYCLE</code><br /></span><span class="term"><code class="literal">NO CYCLE</code></span></dt><dd><p>104      The <code class="literal">CYCLE</code> option allows the sequence to wrap105      around when the <em class="replaceable"><code>maxvalue</code></em> or <em class="replaceable"><code>minvalue</code></em> has been reached by an106      ascending or descending sequence respectively. If the limit is107      reached, the next number generated will be the <em class="replaceable"><code>minvalue</code></em> or <em class="replaceable"><code>maxvalue</code></em>, respectively.108     </p><p>109      If <code class="literal">NO CYCLE</code> is specified, any calls to110      <code class="function">nextval</code> after the sequence has reached its111      maximum value will return an error.  If neither112      <code class="literal">CYCLE</code> or <code class="literal">NO CYCLE</code> are113      specified, <code class="literal">NO CYCLE</code> is the default.114     </p></dd><dt><span class="term"><code class="literal">OWNED BY</code> <em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em><br /></span><span class="term"><code class="literal">OWNED BY NONE</code></span></dt><dd><p>115      The <code class="literal">OWNED BY</code> option causes the sequence to be116      associated with a specific table column, such that if that column117      (or its whole table) is dropped, the sequence will be automatically118      dropped as well.  The specified table must have the same owner and be in119      the same schema as the sequence.120      <code class="literal">OWNED BY NONE</code>, the default, specifies that there121      is no such association.122     </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.81.7"><h2>Notes</h2><p>123   Use <code class="command">DROP SEQUENCE</code> to remove a sequence.124  </p><p>125   Sequences are based on <code class="type">bigint</code> arithmetic, so the range126   cannot exceed the range of an eight-byte integer127   (-9223372036854775808 to 9223372036854775807).128  </p><p>129   Because <code class="function">nextval</code> and <code class="function">setval</code> calls are never130   rolled back, sequence objects cannot be used if <span class="quote">“<span class="quote">gapless</span>”</span>131   assignment of sequence numbers is needed.  It is possible to build132   gapless assignment by using exclusive locking of a table containing a133   counter; but this solution is much more expensive than sequence134   objects, especially if many transactions need sequence numbers135   concurrently.136  </p><p>137   Unexpected results might be obtained if a <em class="replaceable"><code>cache</code></em> setting greater than one is138   used for a sequence object that will be used concurrently by139   multiple sessions.  Each session will allocate and cache successive140   sequence values during one access to the sequence object and141   increase the sequence object's <code class="literal">last_value</code> accordingly.142   Then, the next <em class="replaceable"><code>cache</code></em>-1143   uses of <code class="function">nextval</code> within that session simply return the144   preallocated values without touching the sequence object.  So, any145   numbers allocated but not used within a session will be lost when146   that session ends, resulting in <span class="quote">“<span class="quote">holes</span>”</span> in the147   sequence.148  </p><p>149   Furthermore, although multiple sessions are guaranteed to allocate150   distinct sequence values, the values might be generated out of151   sequence when all the sessions are considered.  For example, with152   a <em class="replaceable"><code>cache</code></em> setting of 10,153   session A might reserve values 1..10 and return154   <code class="function">nextval</code>=1, then session B might reserve values155   11..20 and return <code class="function">nextval</code>=11 before session A156   has generated <code class="function">nextval</code>=2.  Thus, with a157   <em class="replaceable"><code>cache</code></em> setting of one158   it is safe to assume that <code class="function">nextval</code> values are generated159   sequentially; with a <em class="replaceable"><code>cache</code></em> setting greater than one you160   should only assume that the <code class="function">nextval</code> values are all161   distinct, not that they are generated purely sequentially.  Also,162   <code class="literal">last_value</code> will reflect the latest value reserved by163   any session, whether or not it has yet been returned by164   <code class="function">nextval</code>.165  </p><p>166   Another consideration is that a <code class="function">setval</code> executed on167   such a sequence will not be noticed by other sessions until they168   have used up any preallocated values they have cached.169  </p></div><div class="refsect1" id="id-1.9.3.81.8"><h2>Examples</h2><p>170   Create an ascending sequence called <code class="literal">serial</code>, starting at 101:171</p><pre class="programlisting">172CREATE SEQUENCE serial START 101;173</pre><p>174  </p><p>175   Select the next number from this sequence:176</p><pre class="programlisting">177SELECT nextval('serial');178 179 nextval180---------181     101182</pre><p>183  </p><p>184   Select the next number from this sequence:185</p><pre class="programlisting">186SELECT nextval('serial');187 188 nextval189---------190     102191</pre><p>192  </p><p>193   Use this sequence in an <code class="command">INSERT</code> command:194</p><pre class="programlisting">195INSERT INTO distributors VALUES (nextval('serial'), 'nothing');196</pre><p>197  </p><p>198   Update the sequence value after a <code class="command">COPY FROM</code>:199</p><pre class="programlisting">200BEGIN;201COPY distributors FROM 'input_file';202SELECT setval('serial', max(id)) FROM distributors;203END;204</pre></div><div class="refsect1" id="id-1.9.3.81.9"><h2>Compatibility</h2><p>205   <code class="command">CREATE SEQUENCE</code> conforms to the <acronym class="acronym">SQL</acronym>206   standard, with the following exceptions:207   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>208      Obtaining the next value is done using the <code class="function">nextval()</code>209      function instead of the standard's <code class="command">NEXT VALUE FOR</code>210      expression.211     </p></li><li class="listitem"><p>212      The <code class="literal">OWNED BY</code> clause is a <span class="productname">PostgreSQL</span>213      extension.214     </p></li></ul></div></div><div class="refsect1" id="id-1.9.3.81.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-altersequence.html" title="ALTER SEQUENCE"><span class="refentrytitle">ALTER SEQUENCE</span></a>, <a class="xref" href="sql-dropsequence.html" title="DROP SEQUENCE"><span class="refentrytitle">DROP SEQUENCE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createschema.html" title="CREATE SCHEMA">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createserver.html" title="CREATE SERVER">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE SCHEMA </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE SERVER</td></tr></table></div></body></html>