<!--
doc/src/sgml/ref/create_sequence.sgml
PostgreSQL documentation
-->

<refentry id="sql-createsequence">
 <indexterm zone="sql-createsequence">
  <primary>CREATE SEQUENCE</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE SEQUENCE</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE SEQUENCE</refname>
  <refpurpose>define a new sequence generator</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable>
    [ AS <replaceable class="parameter">data_type</replaceable> ]
    [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
    [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
    [ [ NO ] CYCLE ]
    [ START [ WITH ] <replaceable class="parameter">start</replaceable> ]
    [ CACHE <replaceable class="parameter">cache</replaceable> ]
    [ OWNED BY { <replaceable class="parameter">table_name</replaceable>.<replaceable class="parameter">column_name</replaceable> | NONE } ]
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>CREATE SEQUENCE</command> creates a new sequence number
   generator.  This involves creating and initializing a new special
   single-row table with the name <replaceable
   class="parameter">name</replaceable>.  The generator will be
   owned by the user issuing the command.
  </para>

  <para>
   If a schema name is given then the sequence is created in the
   specified schema.  Otherwise it is created in the current schema.
   Temporary sequences exist in a special schema, so a schema name cannot be
   given when creating a temporary sequence.
   The sequence name must be distinct from the name of any other relation
   (table, sequence, index, view, materialized view, or foreign table) in
   the same schema.
  </para>

  <para>
   After a sequence is created, you use the functions
   <function>nextval</function>,
   <function>currval</function>, and
   <function>setval</function>
   to operate on the sequence.  These functions are documented in
   <xref linkend="functions-sequence"/>.
  </para>

  <para>
   Although you cannot update a sequence directly, you can use a query like:

<programlisting>
SELECT * FROM <replaceable>name</replaceable>;
</programlisting>

   to examine the parameters and current state of a sequence.  In particular,
   the <literal>last_value</literal> field of the sequence shows the last value
   allocated by any session.  (Of course, this value might be obsolete
   by the time it's printed, if other sessions are actively doing
   <function>nextval</function> calls.)
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
    <listitem>
     <para>
      If specified, the sequence object is created only for this
      session, and is automatically dropped on session exit.  Existing
      permanent sequences with the same name are not visible (in this
      session) while the temporary sequence exists, unless they are
      referenced with schema-qualified names.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>UNLOGGED</literal></term>
    <listitem>
     <para>
      If specified, the sequence is created as an unlogged sequence.  Changes
      to unlogged sequences are not written to the write-ahead log.  They are
      not crash-safe: an unlogged sequence is automatically reset to its
      initial state after a crash or unclean shutdown.  Unlogged sequences are
      also not replicated to standby servers.
     </para>

     <para>
      Unlike unlogged tables, unlogged sequences do not offer a significant
      performance advantage.