NAME

CREATE SEQUENCE - 創(chuàng)建一個新的序列發(fā)生器

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 將向當前數(shù)據(jù)庫里增加一個新的序列號生成器。 包括創(chuàng)建和初始化一個新的名為 name的單行表。生成器將為使用此命令的用戶所有。

 如果給出了一個模式名，那么該序列是在指定模式中創(chuàng)建的。 否則它會在當前模式中創(chuàng)建臨時序列存在于一個特殊的模式中，因此如果創(chuàng)建一個臨時序列的時候， 不能給出模式名。 序列名必需和同一模式中的其他序列，表，索引，或者視圖不同。

 在序列創(chuàng)建后，你可以使用函數(shù) nextval, currval, 和 setval 操作序列。這些函數(shù)在 ``Sequence-Manipulation Functions'' 中有詳細文檔。

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

SELECT * FROM name;

 檢查一個序列的參數(shù)和當前狀態(tài)。特別是序列的 last_value 字段顯示了任意后端進程分配的最后的數(shù)值。 （當然，這些值在被打印出來的時候可能已經(jīng)過時了 --- 如果其它進程正積極地使用 nextval。）  

PARAMETERS 參數(shù)

TEMPORARY or TEMP

 如果聲明了這個修飾詞，那么該序列對象只為這個會話創(chuàng)建， 并且在會話結(jié)束的時候自動刪除。在臨時序列存在的時候， 同名永久序列是不可見的（在同一會話里），除非它們是用模式修飾的名字引用的。

name

 將要創(chuàng)建的序列號名（可以用模式修飾）。

increment

INCREMENT BY increment 子句是可選的。一個正數(shù)將生成一個遞增的序列， 一個負數(shù)將生成一個遞減的序列。缺省值是一（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 選項使序列號預分配并且為快速訪問存儲在內(nèi)存里面。 最小值（也是缺省值）是1（一次只能生成一個值, 也就是說沒有緩存）這也是缺省。

CYCLE

NO CYCLE

 可選的CYCLE關(guān)鍵字可用于使序列到達 最大值（maxvalue） 或 最小值（minvalue）  時可復位并繼續(xù)下去。如果達到極限，生成的下一個數(shù)據(jù)將分別是 最小值（minvalue） 或 最大值（maxvalue）。

 如果聲明了可選的關(guān)鍵字 NO CYCLE， 那么在序列達到其最大值之后任何對 nextval 的調(diào)用都強返回一個錯誤。 如果既沒有聲明 CYCLE 也沒有聲明 NO CYCLE， 那么 NO CYCLE 是缺省。

NOTES 注意

 使用 DROP SEQUENCE 語句來刪除序列。

 序列是基于 bigint 運算的，因此其范圍不能超過八字節(jié)的整數(shù)范圍（-9223372036854775808 到 9223372036854775807）。 在一些老一點的平臺上可能沒有對八字節(jié)整數(shù)的編譯器支持， 這種情況下序列使用普通的 integer 運算（范圍是 -2147483648 到 +2147483647）。

 如果 cache 設(shè)置大于一， 并且這個序列對象將被用于并發(fā)多會話的場合，那么可能會有不可預料的結(jié)果發(fā)生。 每個會話在一次訪問序列對象的過程中將分配并緩存隨后的序列值，并且相應增加序列對象的 last_value。 這樣，同一個事務中的隨后的 cache-1 次 nextval  將只是返回預先分配的數(shù)值，而不用動序列對象。因此，任何在一個會話中分配但是沒有使用的數(shù)字都將在會話結(jié)尾丟失，導致序列里面出現(xiàn)"空洞"。

 另外，盡管系統(tǒng)保證為多個會話分配獨立的序列值，但是如果考慮所有會話， 那么這個數(shù)值可能會丟失順序。比如，如果 cache  設(shè)置為 10，那么會話 A 保留了 1..10 并且返回 nextval=1， 然后會話 B 可能會保留 11..20 然后在會話 A 生成 nextval=2 之前返回 nextval=11。因此，對于 cache 設(shè)置為一的情況，我們可以安全地假設(shè) nextval 值是順序生成的； 而如果把 cache 設(shè)置得大于一， 那么你只能假設(shè) nextval 值總是唯一得，而不是完全順序地生成。 同樣，last_value 將反映任何會話保留的最后的數(shù)值，不管它是否曾被 nextval 返回。

 另外一個考慮是在這樣的序列上執(zhí)行的 setval 將不會被其它會話注意到，直到它們用光他們緩存的數(shù)值。  

EXAMPLES 例子

 創(chuàng)建一個叫 serial 的遞增序列，從101開始：

CREATE SEQUENCE serial START 101;

 從此序列中選出下一個數(shù)字：

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;