CREATE SEQUENCE
目录
CREATE SEQUENCE
NAMESYNOPSIS
DESCRIPTION æè¿°
PARAMETERS åæ°
NOTES 注æ
EXAMPLES ä¾å
COMPATIBILITY å¼å®¹æ§
è¯è
è·
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;
COMPATIBILITY å¼å®¹æ§
CREATE SEQUENCE æ¯ PostgreSQL è¯è¨æ©å±ã å¨ SQL æ åé没æ CREATE SEQUENCE è¯å¥ã
è¯è
Postgresql ä¸æç½ç« ä½ä¼å¹³ <laser@pgsqldb.org>
è·
æ¬é¡µé¢ä¸æçç±ä¸æ
man
æå页计åæä¾ã
ä¸æ man
æå页计åï¼https://github.com/man-pages-zh/manpages-zh