CREATE SEQUENCE 中文man页面

NAME

CREATE SEQUENCE - 创建一个新的序列发生器

SYNOPSIS

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

DESCRIPTION 描述

CREATE SEQUENCE 将向当前数据库里增加一个新的序列号生成器。 包括创建和初始化一个新的名为 name的单行表。生成器将为使用此命令的用户所有。

 如果给出了一个模式名，那么该序列是在指定模式中创建的。 否则它会在当前模式中创建临时序列存在于一个特殊的模式中，因此如果创建一个临时序列的时候， 不能给出模式名。 序列名必需和同一模式中的其他序列，表，索引，或者视图不同。

 在序列创建后，你可以使用函数 nextval, currval, 和 setval 操作序列。这些函数在 ``Sequence-Manipulation Functions'' 中有详细文档。

 尽管你不能直接更新一个序列，但你可以使用象

SELECT * FROM name;

 检查一个序列的参数和当前状态。特别是序列的 last_value 字段显示了任意后端进程分配的最后的数值。 （当然，这些值在被打印出来的时候可能已经过时了 --- 如果其它进程正积极地使用 nextval。）  

PARAMETERS 参数

TEMPORARY or TEMP

 如果声明了这个修饰词，那么该序列对象只为这个会话创建， 并且在会话结束的时候自动删除。在临时序列存在的时候， 同名永久序列是不可见的（在同一会话里），除非它们是用模式修饰的名字引用的。

name

 将要创建的序列号名（可以用模式修饰）。

increment

INCREMENT BY increment 子句是可选的。一个正数将生成一个递增的序列， 一个负数将生成一个递减的序列。缺省值是一（1）。

minvalue

NO MINVALUE

 可选的子句 MINVALUE minvalue  决定一个序列可生成的最小值。 如果没有声明这个子句或者声明了 NO MINVALUE，那么就使用缺省。 缺省分别是递增序列为 1 递减为 -263-1。

maxvalue

NO MAXVALUE

 使用可选子句 MAXVALUE maxvalue  决定序列的最大值。 如果没有声明这个子句或者声明了 NO MAXVALUE，那么就使用缺省。 缺省的分别是递增为 263-1，递减为 -1。

start

 可选的 START WITH start  子句  使序列可以从任意位置开始。缺省初始值是递增序列为 minvalue  递减序列为 maxvalue.

cache

CACHE cache 选项使序列号预分配并且为快速访问存储在内存里面。 最小值（也是缺省值）是1（一次只能生成一个值, 也就是说没有缓存）这也是缺省。

CYCLE

NO CYCLE

 可选的CYCLE关键字可用于使序列到达 最大值（maxvalue） 或 最小值（minvalue）  时可复位并继续下去。如果达到极限，生成的下一个数据将分别是 最小值（minvalue） 或 最大值（maxvalue）。

 如果声明了可选的关键字 NO CYCLE， 那么在序列达到其最大值之后任何对 nextval 的调用都强返回一个错误。 如果既没有声明 CYCLE 也没有声明 NO CYCLE， 那么 NO CYCLE 是缺省。

NOTES 注意

 使用 DROP SEQUENCE 语句来删除序列。

 序列是基于 bigint 运算的，因此其范围不能超过八字节的整数范围（-9223372036854775808 到 9223372036854775807）。 在一些老一点的平台上可能没有对八字节整数的编译器支持， 这种情况下序列使用普通的 integer 运算（范围是 -2147483648 到 +2147483647）。

 如果 cache 设置大于一， 并且这个序列对象将被用于并发多会话的场合，那么可能会有不可预料的结果发生。 每个会话在一次访问序列对象的过程中将分配并缓存随后的序列值，并且相应增加序列对象的 last_value。 这样，同一个事务中的随后的 cache-1 次 nextval  将只是返回预先分配的数值，而不用动序列对象。因此，任何在一个会话中分配但是没有使用的数字都将在会话结尾丢失，导致序列里面出现"空洞"。

 另外，尽管系统保证为多个会话分配独立的序列值，但是如果考虑所有会话， 那么这个数值可能会丢失顺序。比如，如果 cache  设置为 10，那么会话 A 保留了 1..10 并且返回 nextval=1， 然后会话 B 可能会保留 11..20 然后在会话 A 生成 nextval=2 之前返回 nextval=11。因此，对于 cache 设置为一的情况，我们可以安全地假设 nextval 值是顺序生成的； 而如果把 cache 设置得大于一， 那么你只能假设 nextval 值总是唯一得，而不是完全顺序地生成。 同样，last_value 将反映任何会话保留的最后的数值，不管它是否曾被 nextval 返回。

 另外一个考虑是在这样的序列上执行的 setval 将不会被其它会话注意到，直到它们用光他们缓存的数值。  

EXAMPLES 例子

 创建一个叫 serial 的递增序列，从101开始：

CREATE SEQUENCE serial START 101;

 从此序列中选出下一个数字：

SELECT nextval('serial');
    
 nextval
---------
     114

 在一个 INSERT 中使用此序列：

INSERT INTO distributors VALUES (nextval('serial'), 'nothing');

 在一个 COPY FROM 后更新序列：

BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', max(id)) FROM distributors;
END;

#p#

NAME

CREATE SEQUENCE - define a new sequence generator

SYNOPSIS

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

DESCRIPTION

CREATE SEQUENCE creates a new sequence number generator. This involves creating and initializing a new special single-row table with the name name. 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 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.

After a sequence is created, you use the functions nextval, currval, and setval to operate on the sequence. These functions are documented in the section called ``Sequence-Manipulation Functions'' in the documentation.

Although you cannot update a sequence directly, you can use a query like

SELECT * FROM name;

to examine the parameters and current state of a sequence. In particular, the 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 nextval calls.)  

PARAMETERS

TEMPORARY or TEMP

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.

name

The name (optionally schema-qualified) of the sequence to be created.

increment

The optional clause INCREMENT BY increment specified, 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.

minvalue

NO MINVALUE

The optional clause MINVALUE minvalue determines the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE is specified, then defaults will be used. The defaults are 1 and -263-1 for ascending and descending sequences, respectively.

maxvalue

NO MAXVALUE

The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used. The defaults are 263-1 and -1 for ascending and descending sequences, respectively.

start

The optional clause START WITH start allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones.

cache

The optional clause CACHE cache 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.

CYCLE

NO CYCLE

The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively.

If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value will return an error. If neither CYCLE or NO CYCLE are specified, NO CYCLE is the default.

NOTES

Use DROP SEQUENCE to remove a sequence.

Sequences are based on 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 integer arithmetic (range -2147483648 to +2147483647).

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 sessions. Each session 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 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 ``holes'' in the sequence.

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 cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session 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 session, 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 sessions until they have used up any preallocated values they have cached.  

EXAMPLES

Create an ascending sequence called serial, starting at 101:

CREATE SEQUENCE serial START 101;

Select the next number from this sequence:

SELECT nextval('serial');
    
 nextval
---------
     114

Use this sequence in an INSERT command:

INSERT INTO distributors VALUES (nextval('serial'), 'nothing');

Update the sequence value after a COPY FROM:

BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', max(id)) FROM distributors;
END;