<!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.19 2001/09/03 12:57:49 petere Exp $
-Postgres documentation
+doc/src/sgml/ref/create_sequence.sgml
+PostgreSQL documentation
-->
<refentry id="SQL-CREATESEQUENCE">
<refmeta>
- <refentrytitle id="sql-createsequence-title">
- CREATE SEQUENCE
- </refentrytitle>
+ <refentrytitle>CREATE SEQUENCE</refentrytitle>
+ <manvolnum>7</manvolnum>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
+
<refnamediv>
- <refname>
- CREATE SEQUENCE
- </refname>
- <refpurpose>
- define a new sequence
- </refpurpose>
- </refnamediv>
+ <refname>CREATE SEQUENCE</refname>
+ <refpurpose>define a new sequence generator</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-createsequence">
+ <primary>CREATE SEQUENCE</primary>
+ </indexterm>
+
<refsynopsisdiv>
- <refsynopsisdivinfo>
- <date>1999-07-20</date>
- </refsynopsisdivinfo>
- <synopsis>
-CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">seqname</replaceable> [ INCREMENT <replaceable class="parameter">increment</replaceable> ]
- [ MINVALUE <replaceable class="parameter">minvalue</replaceable> ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> ]
- [ START <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ CYCLE ]
- </synopsis>
-
- <refsect2 id="R2-SQL-CREATESEQUENCE-1">
- <refsect2info>
- <date>1998-09-11</date>
- </refsect2info>
- <title>
- Inputs
- </title>
- <para>
-
- <variablelist>
- <varlistentry>
- <term>TEMPORARY or TEMP</term>
- <listitem>
- <para>
- If specified, the sequence 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.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">seqname</replaceable></term>
- <listitem>
- <para>
- The name of a sequence to be created.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">increment</replaceable></term>
- <listitem>
- <para>
- The
- <option>INCREMENT <replaceable class="parameter">increment</replaceable></option>
- clause is optional. A positive value will make an
- ascending sequence, a negative one a descending sequence.
- The default value is one (1).
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">minvalue</replaceable></term>
- <listitem>
- <para>
- The optional clause <option>MINVALUE
- <replaceable class="parameter">minvalue</replaceable></option>
- determines the minimum value
- a sequence can generate. The defaults are 1 and -2^63-1 for
- ascending and descending sequences, respectively.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">maxvalue</replaceable></term>
- <listitem>
- <para>
- The optional clause <option>MAXVALUE
- <replaceable class="parameter">maxvalue</replaceable></option>
- determines the maximum
- value for the sequence. The defaults are 2^63-1 and -1 for
- ascending and descending sequences, respectively.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">start</replaceable></term>
- <listitem>
- <para>
- The optional <option>START
- <replaceable class="parameter">start</replaceable>
- clause</option> enables the sequence to begin anywhere.
- The default starting value is
- <replaceable class="parameter">minvalue</replaceable>
- for ascending sequences and
- <replaceable class="parameter">maxvalue</replaceable>
- for descending ones.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">cache</replaceable></term>
- <listitem>
- <para>
- The <option>CACHE <replaceable class="parameter">cache</replaceable></option> option
- enables sequence numbers to be preallocated
- and stored in memory for faster access. The minimum
- value is 1 (only one value can be generated at a time, i.e., no cache)
- and this is also the default.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term>CYCLE</term>
- <listitem>
- <para>
- The optional CYCLE keyword may be used to enable the sequence
- to wrap around when the
- <replaceable class="parameter">maxvalue</replaceable> or
- <replaceable class="parameter">minvalue</replaceable> has been
- reached by
- an ascending or descending sequence respectively. If the limit is
- reached, the next number generated will be the
- <replaceable class="parameter">minvalue</replaceable> or
- <replaceable class="parameter">maxvalue</replaceable>,
- respectively.
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
- </para>
- </refsect2>
-
- <refsect2 id="R2-SQL-CREATESEQUENCE-2">
- <refsect2info>
- <date>1998-09-11</date>
- </refsect2info>
- <title>
- Outputs
- </title>
- <para>
-
- <variablelist>
- <varlistentry>
- <term><computeroutput>
-CREATE
- </computeroutput></term>
- <listitem>
- <para>
- Message returned if the command is successful.
- </para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><computeroutput>
-ERROR: Relation '<replaceable class="parameter">seqname</replaceable>' already exists
- </computeroutput></term>
- <listitem>
- <para>
- If the sequence specified already exists.
- </para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><computeroutput>
-ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">start</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
- </computeroutput></term>
- <listitem>
- <para>
- If the specified starting value is out of range.
- </para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><computeroutput>
-ERROR: DefineSequence: START value (<replaceable class="parameter">start</replaceable>) can't be < MINVALUE (<replaceable class="parameter">min</replaceable>)
- </computeroutput></term>
- <listitem>
- <para>
- If the specified starting value is out of range.
- </para>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><computeroutput>
-ERROR: DefineSequence: MINVALUE (<replaceable class="parameter">min</replaceable>) can't be >= MAXVALUE (<replaceable class="parameter">max</replaceable>)
- </computeroutput></term>
- <listitem>
- <para>
- If the minimum and maximum values are inconsistent.
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
- </para>
- </refsect2>
+<synopsis>
+CREATE [ TEMPORARY | TEMP ] SEQUENCE <replaceable class="parameter">name</replaceable> [ INCREMENT [ BY ] <replaceable class="parameter">increment</replaceable> ]
+ [ MINVALUE <replaceable class="parameter">minvalue</replaceable> | NO MINVALUE ] [ MAXVALUE <replaceable class="parameter">maxvalue</replaceable> | NO MAXVALUE ]
+ [ START [ WITH ] <replaceable class="parameter">start</replaceable> ] [ CACHE <replaceable class="parameter">cache</replaceable> ] [ [ NO ] CYCLE ]
+ [ OWNED BY { <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable> | NONE } ]
+</synopsis>
</refsynopsisdiv>
- <refsect1 id="R1-SQL-CREATESEQUENCE-1">
- <refsect1info>
- <date>1998-09-11</date>
- </refsect1info>
- <title>
- Description
- </title>
+ <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>
- <command>CREATE SEQUENCE</command> will enter a new sequence number generator
- into the current data base. This involves creating and initializing a
- new single-row
- table with the name <replaceable class="parameter">seqname</replaceable>.
- The generator will be owned by the user issuing the command.
+ 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 sequence,
+ table, index, view, or foreign table in the same schema.
</para>
<para>
- After a sequence is created, you may use the function
- <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function>
- to get a new number from the sequence.
- The function
- <function>currval('<replaceable class="parameter">seqname</replaceable>')</function>
- may be used to determine the number returned by the last call to
- <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function>
- for the specified sequence in the current session.
- The function
- <function>setval('<replaceable class="parameter">seqname</replaceable>',
- <replaceable class="parameter">newvalue</replaceable>)</function>
- may be used to set the current value of the specified sequence.
- The next call to
- <function>nextval('<replaceable class="parameter">seqname</replaceable>')</function>
- will return the given value plus the sequence increment.
+ 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>
- Use a query like
+ 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</> 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</> 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>
- <programlisting>
-SELECT * FROM <replaceable>seqname</replaceable>;
- </programlisting>
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (optionally schema-qualified) of the sequence to be created.
+ </para>
+ </listitem>
+ </varlistentry>
- to examine the parameters of a sequence.
+ <varlistentry>
+ <term><replaceable class="parameter">increment</replaceable></term>
+ <listitem>
+ <para>
+ The optional clause <literal>INCREMENT BY <replaceable
+ class="parameter">increment</replaceable></literal> specifies
+ which value is added to the current sequence value to create a
+ new value. A positive value will make an ascending sequence, a
+ negative one a descending sequence. The default value is 1.
+ </para>
+ </listitem>
+ </varlistentry>
- As an alternative to fetching the
- parameters from the original definition as above, you can use
+ <varlistentry>
+ <term><replaceable class="parameter">minvalue</replaceable></term>
+ <term><literal>NO MINVALUE</literal></term>
+ <listitem>
+ <para>
+ The optional clause <literal>MINVALUE <replaceable
+ class="parameter">minvalue</replaceable></literal> determines
+ the minimum value a sequence can generate. If this clause is not
+ supplied or <option>NO MINVALUE</option> is specified, then
+ defaults will be used. The defaults are 1 and
+ -2<superscript>63</>-1 for ascending and descending sequences,
+ respectively.
+ </para>
+ </listitem>
+ </varlistentry>
- <programlisting>
-SELECT last_value FROM <replaceable>seqname</replaceable>;
- </programlisting>
+ <varlistentry>
+ <term><replaceable class="parameter">maxvalue</replaceable></term>
+ <term><literal>NO MAXVALUE</literal></term>
+ <listitem>
+ <para>
+ The optional clause <literal>MAXVALUE <replaceable
+ class="parameter">maxvalue</replaceable></literal> determines
+ the maximum value for the sequence. If this clause is not
+ supplied or <option>NO MAXVALUE</option> is specified, then
+ default values will be used. The defaults are
+ 2<superscript>63</>-1 and -1 for ascending and descending
+ sequences, respectively.
+ </para>
+ </listitem>
+ </varlistentry>
- to obtain the last value allocated by any backend.
+ <varlistentry>
+ <term><replaceable class="parameter">start</replaceable></term>
+ <listitem>
+ <para>
+ The optional clause <literal>START WITH <replaceable
+ class="parameter">start</replaceable> </literal> allows the
+ sequence to begin anywhere. The default starting value is
+ <replaceable class="parameter">minvalue</replaceable> for
+ ascending sequences and <replaceable
+ class="parameter">maxvalue</replaceable> for descending ones.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">cache</replaceable></term>
+ <listitem>
+ <para>
+ The optional clause <literal>CACHE <replaceable
+ class="parameter">cache</replaceable></literal> specifies how
+ many sequence numbers are to be preallocated and stored in
+ memory for faster access. The minimum value is 1 (only one value
+ can be generated at a time, i.e., no cache), and this is also the
+ default.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>CYCLE</literal></term>
+ <term><literal>NO CYCLE</literal></term>
+ <listitem>
+ <para>
+ The <literal>CYCLE</literal> option allows the sequence to wrap
+ around when the <replaceable
+ class="parameter">maxvalue</replaceable> or <replaceable
+ class="parameter">minvalue</replaceable> has been reached by an
+ ascending or descending sequence respectively. If the limit is
+ reached, the next number generated will be the <replaceable
+ class="parameter">minvalue</replaceable> or <replaceable
+ class="parameter">maxvalue</replaceable>, respectively.
+ </para>
+
+ <para>
+ If <literal>NO CYCLE</literal> is specified, any calls to
+ <function>nextval</function> after the sequence has reached its
+ maximum value will return an error. If neither
+ <literal>CYCLE</literal> or <literal>NO CYCLE</literal> are
+ specified, <literal>NO CYCLE</literal> is the default.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>OWNED BY</literal> <replaceable class="parameter">table</replaceable>.<replaceable class="parameter">column</replaceable></term>
+ <term><literal>OWNED BY NONE</literal></term>
+ <listitem>
+ <para>
+ The <literal>OWNED BY</literal> option causes the sequence to be
+ associated with a specific table column, such that if that column
+ (or its whole table) is dropped, the sequence will be automatically
+ dropped as well. The specified table must have the same owner and be in
+ the same schema as the sequence.
+ <literal>OWNED BY NONE</literal>, the default, specifies that there
+ is no such association.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Use <command>DROP SEQUENCE</command> to remove a sequence.
+ </para>
+
+ <para>
+ Sequences are based on <type>bigint</> arithmetic, so the range
+ cannot exceed the range of an eight-byte integer
+ (-9223372036854775808 to 9223372036854775807). On some older
+ platforms, there might be no compiler support for eight-byte
+ integers, in which case sequences use regular <type>integer</>
+ arithmetic (range -2147483648 to +2147483647).
</para>
<para>
- To avoid blocking of concurrent transactions
- that obtain numbers from the same sequence, a nextval operation
- is never rolled back; that is, once a value has been fetched it is
- considered used, even if the transaction that did the nextval later
- aborts. This means that aborted transactions may leave unused "holes"
- in the sequence of assigned values. setval operations are never
- rolled back, either.
+ Unexpected results might be obtained if a <replaceable
+ class="parameter">cache</replaceable> setting greater than one is
+ used for a sequence object that will be used concurrently by
+ multiple sessions. Each session will allocate and cache successive
+ sequence values during one access to the sequence object and
+ increase the sequence object's <literal>last_value</> accordingly.
+ Then, the next <replaceable class="parameter">cache</replaceable>-1
+ uses of <function>nextval</> within that session simply return the
+ preallocated values without touching the sequence object. So, any
+ numbers allocated but not used within a session will be lost when
+ that session ends, resulting in <quote>holes</quote> in the
+ sequence.
</para>
- <caution>
- <para>
- Unexpected results may be obtained if a cache setting greater than one
- is used for a sequence object that will be used concurrently by multiple
- backends. Each backend will allocate and cache successive sequence values
- during one access to the sequence object and increase the sequence
- object's last_value accordingly. Then, the next cache-1 uses of nextval
- within that backend simply return the preallocated values without touching
- the shared object. So, numbers allocated but not used in the current session
- will be lost. Furthermore, although multiple backends are guaranteed to
- allocate distinct sequence values, the values may be generated out of
- sequence when all the backends are considered. (For example, with a cache
- setting of 10, backend A might reserve values 1..10 and return nextval=1,
- then
- backend B might reserve values 11..20 and return nextval=11 before backend
- A has generated nextval=2.) Thus, with a cache setting of one it is safe
- to assume that nextval values are generated sequentially; with a cache
- setting greater than one you should only assume that the nextval values
- are all distinct, not that they are generated purely sequentially.
- Also, last_value will reflect the latest value reserved by any backend,
- whether or not it has yet been returned by nextval.
- Another consideration is that a setval executed on such a sequence
- will not be noticed by other backends until they have used up any
- preallocated values they have cached.
- </para>
- </caution>
-
- <refsect2 id="R2-SQL-CREATESEQUENCE-3">
- <refsect2info>
- <date>1998-09-11</date>
- </refsect2info>
- <title>
- Notes
- </title>
- <para>
- Use <command>DROP SEQUENCE</command> to remove a sequence.
- </para>
- <para>
- Each backend uses its own cache to store preallocated numbers.
- Numbers that are cached but not used in the current session will be
- lost, resulting in "holes" in the sequence.
- </para>
- </refsect2>
+ <para>
+ Furthermore, although multiple sessions are guaranteed to allocate
+ distinct sequence values, the values might be generated out of
+ sequence when all the sessions are considered. For example, with
+ a <replaceable class="parameter">cache</replaceable> setting of 10,
+ session A might reserve values 1..10 and return
+ <function>nextval</function>=1, then session B might reserve values
+ 11..20 and return <function>nextval</function>=11 before session A
+ has generated <literal>nextval</literal>=2. Thus, with a
+ <replaceable class="parameter">cache</replaceable> setting of one
+ it is safe to assume that <function>nextval</> values are generated
+ sequentially; with a <replaceable
+ class="parameter">cache</replaceable> setting greater than one you
+ should only assume that the <function>nextval</> values are all
+ distinct, not that they are generated purely sequentially. Also,
+ <literal>last_value</> will reflect the latest value reserved by
+ any session, whether or not it has yet been returned by
+ <function>nextval</>.
+ </para>
+
+ <para>
+ Another consideration is that a <function>setval</> executed on
+ such a sequence will not be noticed by other sessions until they
+ have used up any preallocated values they have cached.
+ </para>
</refsect1>
- <refsect1 id="R1-SQL-CREATESEQUENCE-2">
- <title>
- Usage
- </title>
+ <refsect1>
+ <title>Examples</title>
+
<para>
Create an ascending sequence called <literal>serial</literal>, starting at 101:
- </para>
- <programlisting>
+<programlisting>
CREATE SEQUENCE serial START 101;
- </programlisting>
+</programlisting>
+ </para>
+
+ <para>
+ Select the next number from this sequence:
+<programlisting>
+SELECT nextval('serial');
+
+ nextval
+---------
+ 101
+</programlisting>
+ </para>
+
<para>
Select the next number from this sequence:
-SELECT NEXTVAL ('serial');
-
-nextval
--------
- 114
- </programlisting>
+<programlisting>
+SELECT nextval('serial');
+
+ nextval
+---------
+ 102
+</programlisting>
</para>
+
<para>
- Use this sequence in an INSERT:
-INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing');
- </programlisting>
+ Use this sequence in an <command>INSERT</command> command:
+<programlisting>
+INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
+</programlisting>
</para>
<para>
- Set the sequence value after a COPY FROM:
- <programlisting>
-CREATE FUNCTION distributors_id_max() RETURNS INT4
- AS 'SELECT max(id) FROM distributors'
- LANGUAGE 'sql';
+ Update the sequence value after a <command>COPY FROM</command>:
+<programlisting>
BEGIN;
- COPY distributors FROM 'input_file';
- SELECT setval('serial', distributors_id_max());
+COPY distributors FROM 'input_file';
+SELECT setval('serial', max(id)) FROM distributors;
END;
- </programlisting>
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <command>CREATE SEQUENCE</command> conforms to the <acronym>SQL</acronym>
+ standard, with the following exceptions:
+ <itemizedlist>
+ <listitem>
+ <para>
+ The standard's <literal>AS <data type></literal> expression is not
+ supported.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Obtaining the next value is done using the <function>nextval()</>
+ function instead of the standard's <command>NEXT VALUE FOR</command>
+ expression.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The <literal>OWNED BY</> clause is a <productname>PostgreSQL</>
+ extension.
+ </para>
+ </listitem>
+ </itemizedlist>
</para>
</refsect1>
- <refsect1 id="R1-SQL-CREATESEQUENCE-3">
- <title>
- Compatibility
- </title>
-
- <refsect2 id="R2-SQL-CREATESEQUENCE-4">
- <refsect2info>
- <date>1998-09-11</date>
- </refsect2info>
- <title>
- SQL92
- </title>
-
- <para>
- <command>CREATE SEQUENCE</command> is a <productname>Postgres</productname>
- language extension.
- There is no <command>CREATE SEQUENCE</command> statement
- in <acronym>SQL92</acronym>.
- </para>
- </refsect2>
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-altersequence"></member>
+ <member><xref linkend="sql-dropsequence"></member>
+ </simplelist>
</refsect1>
-</refentry>
-<!-- Keep this comment at the end of the file
-Local variables:
-mode: sgml
-sgml-omittag:nil
-sgml-shorttag:t
-sgml-minimize-attributes:nil
-sgml-always-quote-attributes:t
-sgml-indent-step:1
-sgml-indent-data:t
-sgml-parent-document:nil
-sgml-default-dtd-file:"../reference.ced"
-sgml-exposed-tags:nil
-sgml-local-catalogs:"/usr/lib/sgml/catalog"
-sgml-local-ecat-files:nil
-End:
--->
+</refentry>
projects / postgresql / blobdiff