| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_sequence.sgml,v 1.45 2006/09/16 00:30:17 momjian Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-CREATESEQUENCE"> |
| <refmeta> |
| <refentrytitle id="sql-createsequence-title">CREATE SEQUENCE</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>CREATE SEQUENCE</refname> |
| <refpurpose>define a new sequence generator</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-createsequence"> |
| <primary>CREATE SEQUENCE</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <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> |
| <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 may not be |
| given when creating a temporary sequence. |
| The sequence name must be distinct from the name of any other sequence, |
| table, index, or view 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</> field of the sequence shows the last value |
| allocated by any session. (Of course, this value may 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> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">name</replaceable></term> |
| <listitem> |
| <para> |
| The name (optionally schema-qualified) of the sequence to be created. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <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> |
| |
| <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> |
| |
| <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> |
| |
| <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 may be no compiler support for eight-byte |
| integers, in which case sequences use regular <type>integer</> |
| arithmetic (range -2147483648 to +2147483647). |
| </para> |
| |
| <para> |
| Unexpected results may 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> |
| |
| <para> |
| Furthermore, although multiple sessions are guaranteed to allocate |
| distinct sequence values, the values may 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> |
| <title>Examples</title> |
| |
| <para> |
| Create an ascending sequence called <literal>serial</literal>, starting at 101: |
| <programlisting> |
| CREATE SEQUENCE serial START 101; |
| </programlisting> |
| </para> |
| |
| <para> |
| Select the next number from this sequence: |
| <programlisting> |
| SELECT nextval('serial'); |
| |
| nextval |
| --------- |
| 114 |
| </programlisting> |
| </para> |
| |
| <para> |
| Use this sequence in an <command>INSERT</command> command: |
| <programlisting> |
| INSERT INTO distributors VALUES (nextval('serial'), 'nothing'); |
| </programlisting> |
| </para> |
| |
| <para> |
| Update the sequence value after a <command>COPY FROM</command>: |
| <programlisting> |
| BEGIN; |
| COPY distributors FROM 'input_file'; |
| SELECT setval('serial', max(id)) FROM distributors; |
| END; |
| </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> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-altersequence" endterm="sql-altersequence-title"></member> |
| <member><xref linkend="sql-dropsequence" endterm="sql-dropsequence-title"></member> |
| </simplelist> |
| </refsect1> |
| |
| </refentry> |