CREATE SEQUENCE

ÀâÌÀ

CREATE SEQUENCE¤Ï¡¢¿·¤·¤¤¥·¡¼¥±¥ó¥¹Èֹ楸¥§¥Í¥ì¡¼¥¿¤òºîÀ®¤·¤Þ¤¹¡£ ¶ñÂÎŪ¤Ë¤Ï¡¢¿·¤·¤¯name¤È¤¤¤¦Ì¾Á°¤ò»ý¤Ä¡¢1¹Ô¤À¤±¤ÎÆüì¤Ê¥Æ¡¼¥Ö¥ë¤ÎºîÀ®¤È½é´ü²½¤ò¹Ô¤¤¤Þ¤¹¡£ ¥·¡¼¥±¥ó¥¹¥¸¥§¥Í¥ì¡¼¥¿¤Ï¡¢¤³¤Î¥³¥Þ¥ó¥É¤ò¼Â¹Ô¤·¤¿¥æ¡¼¥¶¤Ë¤è¤Ã¤Æ½êÍ­¤µ¤ì¤Þ¤¹¡£

¥¹¥­¡¼¥Þ̾¤¬Í¿¤¨¤é¤ì¤Æ¤¤¤ë¾ì¹ç¡¢¤½¤Î¥·¡¼¥±¥ó¥¹¤Ï»ØÄꤵ¤ì¤¿¥¹¥­¡¼¥Þ¤ÇºîÀ®¤µ¤ì¤Þ¤¹¡£ ¥¹¥­¡¼¥Þ̾¤¬¤Ê¤±¤ì¤Ð¡¢¥·¡¼¥±¥ó¥¹¤Ï¸½ºß¤Î¥¹¥­¡¼¥Þ¤ÇºîÀ®¤µ¤ì¤Þ¤¹¡£ ¤Þ¤¿¡¢°ì»þ¥·¡¼¥±¥ó¥¹¤ÏÆÃÊ̤ʥ¹¥­¡¼¥Þ¤Ë¸ºß¤¹¤ë¤¿¤á¡¢°ì»þ¥·¡¼¥±¥ó¥¹¤ÎºîÀ®»þ¤Ë¥¹¥­¡¼¥Þ̾¤òÍ¿¤¨¤ë¤³¤È¤Ï¤Ç¤­¤Þ¤»¤ó¡£ ¥·¡¼¥±¥ó¥¹Ì¾¤Ï¡¢Æ±¤¸¥¹¥­¡¼¥ÞÆâ¤Î¾¤Î¥·¡¼¥±¥ó¥¹¡¢¥Æ¡¼¥Ö¥ë¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¡¢¥Ó¥å¡¼¤È¤Ï°Û¤Ê¤ë̾Á°¤Ë¤¹¤ëɬÍפ¬¤¢¤ê¤Þ¤¹¡£

¥·¡¼¥±¥ó¥¹¤òºîÀ®¤·¤¿¸å¡¢nextval¡¢currval¡¢setval´Ø¿ô¤ò»ÈÍѤ·¤Æ¥·¡¼¥±¥ó¥¹¤òÁàºî¤·¤Þ¤¹¡£ ¤³¤Î´Ø¿ô¤Ë¤Ä¤¤¤Æ¤Ï¹à9.12¤ò»²¾È¤·¤Æ¤¯¤À¤µ¤¤¡£

¥·¡¼¥±¥ó¥¹¤òľÀܹ¹¿·¤¹¤ë¤³¤È¤Ï¤Ç¤­¤Þ¤»¤ó¤¬¡¢°Ê²¼¤Î¤è¤¦¤ÊÌ䤤¹ç¤ï¤»¤Ï²Äǽ¤Ç¤¹¡£

SELECT * FROM name;

¤³¤ì¤ò»ÈÍѤ¹¤ë¤È¡¢¥·¡¼¥±¥ó¥¹¤Î¥Ñ¥é¥á¡¼¥¿¤È¸½ºß¤Î¾õÂÖ¤ò³Îǧ¤¹¤ë¤³¤È¤¬¤Ç¤­¤Þ¤¹¡£ Ãæ¤Ç¤â¡¢¥·¡¼¥±¥ó¥¹¤Îlast_value¥Õ¥£¡¼¥ë¥É¤ÏÁ´¤Æ¤Î¥»¥Ã¥·¥ç¥ó¤Ç³ä¤êÅö¤Æ¤é¤ì¤¿ºÇ¸å¤ÎÃͤò¼¨¤·¤Þ¤¹ ¡Ê¤â¤Á¤í¤ó¤³¤ÎÃͤϡ¢Â¾¤Î¥»¥Ã¥·¥ç¥ó¤Înextval¤Î¼Â¹Ô¤Ë¤è¤ê¡¢É½¼¨¤µ¤ì¤¿»þÅÀ¤Ç´û¤ËºÇ¿·¤Ç¤Ï¤Ê¤¤²ÄǽÀ­¤¬¤¢¤ê¤Þ¤¹¡Ë¡£

¥Ñ¥é¥á¡¼¥¿

Ãí¼á

¥·¡¼¥±¥ó¥¹¤òºï½ü¤¹¤ë¤Ë¤ÏDROP SEQUENCE¤ò»ÈÍѤ·¤Æ¤¯¤À¤µ¤¤¡£

¥·¡¼¥±¥ó¥¹¤Ïbigint±é»»¤Ë´ð¤Å¤¤¤Æ¤¤¤Þ¤¹¡£ ¤½¤Î¤¿¤á¡¢8¥Ð¥¤¥ÈÀ°¿ô¤ÎÈÏ°Ï¡Ê-9223372036854775808¤«¤é9223372036854775807¤Þ¤Ç¡Ë¤ò±Û¤¨¤ë¤³¤È¤Ï¤Ç¤­¤Þ¤»¤ó¡£ Èæ³ÓŪ¸Å¤¤¥×¥é¥Ã¥È¥Õ¥©¡¼¥à¤Ç¤Ï¡¢8¥Ð¥¤¥ÈÀ°¿ô¤ò¥µ¥Ý¡¼¥È¤¹¤ë¥³¥ó¥Ñ¥¤¥é¤¬¤Ê¤¤¤³¤È¤¬¤¢¤ê¤Þ¤¹¡£ ¤½¤Î¾ì¹ç¡¢¥·¡¼¥±¥ó¥¹¤Ë¤ÏÄ̾ï¤Îinteger±é»»¤¬»ÈÍѤµ¤ì¤Þ¤¹¡Ê¤³¤Î¾ì¹ç¡¢ÈϰϤÏ-2147483648¤«¤é+2147483647¤Þ¤Ç¡Ë¡£

¥·¡¼¥±¥ó¥¹¥ª¥Ö¥¸¥§¥¯¥È¤Îcache¤È¤·¤Æ1¤è¤êÂ礭¤ÊÃͤòÀßÄꤷ¤¿¾ì¹ç¡¢¤½¤Î¥·¡¼¥±¥ó¥¹¤òÊ£¿ô¤Î¥»¥Ã¥·¥ç¥ó¤ÇƱ»þ¤Ë»ÈÍѤ¹¤ë¤È¡¢Í½ÁÛ³°¤Î·ë²Ì¤Ë¤Ê¤ë²ÄǽÀ­¤¬¤¢¤ê¤Þ¤¹¡£ ³Æ¥»¥Ã¥·¥ç¥ó¤Ï¡¢¥·¡¼¥±¥ó¥¹¥ª¥Ö¥¸¥§¥¯¥È¤Ø¤Î1²ó¤Î¥¢¥¯¥»¥¹¤Î´Ö¤Ë¡¢Ï¢Â³¤¹¤ë¥·¡¼¥±¥ó¥¹Ãͤò¼èÆÀ¤·¡¢¥­¥ã¥Ã¥·¥å¤·¤Þ¤¹¡£ ¤½¤·¤Æ¡¢¥­¥ã¥Ã¥·¥å¤·¤¿¿ô¤Ë±þ¤¸¤Æ¡¢¥·¡¼¥±¥ó¥¹¥ª¥Ö¥¸¥§¥¯¥È¤Îlast_value¤òÁý²Ã¤µ¤»¤Þ¤¹¡£ ¤³¤Î¾ì¹ç¡¢¤½¤Î¥»¥Ã¥·¥ç¥ó¤Ï¡¢¤½¤Î¸å¤Îcache-1²ó¤ËÂФ·¤Æ¤Ï¡¢¤¢¤é¤«¤¸¤á¼èÆÀºÑ¤ß¤Î¥·¡¼¥±¥ó¥¹ÃͤòÊÖ¤·¡¢¥·¡¼¥±¥ó¥¹¥ª¥Ö¥¸¥§¥¯¥È¤òÊѹ¹¤·¤Þ¤»¤ó¡£ ¥»¥Ã¥·¥ç¥ó¤Ë³ä¤êÅö¤Æ¤é¤ì¤¿¤¬»ÈÍѤµ¤ì¤Ê¤«¤Ã¤¿¥·¡¼¥±¥ó¥¹ÈÖ¹æ¤Ï¡¢¥»¥Ã¥·¥ç¥ó¤Î½ªÎ»»þ¤ËÁ´¤Æ¼º¤ï¤ì¤ë¤¿¤á¡¢·ë²Ì¤È¤·¤Æ¥·¡¼¥±¥ó¥¹¤Ë"·ê"¤¬¤Ç¤­¤Þ¤¹¡£

¤µ¤é¤Ë¡¢Ê£¿ô¤Î¥»¥Ã¥·¥ç¥ó¤Ë¤Ï¸ÄÊ̤Υ·¡¼¥±¥ó¥¹Ãͤ¬³ä¤êÅö¤Æ¤é¤ì¤ë¤³¤È¤¬Êݾڤµ¤ì¤Æ¤¤¤Þ¤¹¤¬¡¢Á´¤Æ¤Î¥»¥Ã¥·¥ç¥ó¤¬Âº½Å¤µ¤ì¤ë¤È¡¢¥·¡¼¥±¥ó¥¹Ãͤ¬½çÈÖÄ̤ê¤Ë¤Ê¤é¤Ê¤¤¤³¤È¤¬¤¢¤ê¤Þ¤¹¡£ Î㤨¤Ð¡¢cache¤¬10¤Î¾ì¹ç¤ò¹Í¤¨¤Þ¤¹¡£ ¥»¥Ã¥·¥ç¥óA¤Ç¤Ï1¤«¤é10¤Þ¤Ç¤ò³ÎÊݤ·¡¢nextval=1¤òÊÖ¤·¤Þ¤¹¡£ ¥»¥Ã¥·¥ç¥óB¤Ç¤Ï¡¢¥»¥Ã¥·¥ç¥óA¤¬nextval=2¤òÊÖ¤¹Á°¤Ë¡¢11¤«¤é20¤ò³ÎÊݤ·¡¢nextval=11¤òÊÖ¤·¤Þ¤¹¡£ ¤·¤¿¤¬¤Ã¤Æ¡¢cache¤ò1¤ËÀßÄꤷ¤¿¾ì¹ç¤Ïnextval¤¬Ï¢Â³¤·¤¿ÃͤǤ¢¤ë¤È¹Í¤¨¤Æ¤âÌäÂꤢ¤ê¤Þ¤»¤ó¤¬¡¢cache¤ò1¤è¤êÂ礭¤ÊÃͤËÀßÄꤷ¤¿¾ì¹ç¤Ï¡¢nextval¤ÎÃͤ¬Á´¤Æ°Û¤Ê¤ë¤³¤È¤Î¤ß¤¬Êݾڤµ¤ì¡¢Ï¢Â³¤·¤¿ÃͤǤ¢¤ë¤³¤È¤ÏÊݾڤµ¤ì¤Þ¤»¤ó¡£ ¤Þ¤¿¡¢last_value¤Ï¡¢Ãͤ¬nextval¤Ë¤è¤Ã¤ÆÊÖ¤µ¤ì¤¿¤«¤É¤¦¤«¤Ë´Ø·¸¤Ê¤¯¡¢¤¤¤º¤ì¤«¤Î¥»¥Ã¥·¥ç¥ó¤Ë¤è¤Ã¤Æ³ÎÊݤµ¤ì¤¿ºÇ¸å¤ÎÃͤȤʤê¤Þ¤¹¡£

¤³¤Î¾¡¢¤³¤Î¤è¤¦¤Ê¥·¡¼¥±¥ó¥¹¤ËÂФ·¤Æsetval¤¬¼Â¹Ô¤µ¤ì¤Æ¤â¡¢Â¾¤Î¥»¥Ã¥·¥ç¥ó¤Ï¡¢¤½¤ì¤¾¤ì¤¬¥­¥ã¥Ã¥·¥å¤·¤¿¼èÆÀºÑ¤ß¤ÎÃͤòÁ´¤Æ»È¤¤²Ì¤¿¤¹¤Þ¤Ç¡¢¤½¤ì¤¬¤ï¤«¤é¤Ê¤¤¤³¤È¤â¹Íθ¤¹¤Ù¤­ÌäÂê¤Ç¤¹¡£

Îã

101¤«¤é»Ï¤Þ¤ëserial¤È¤¤¤¦Ì¾Á°¤Î¾º½ç¥·¡¼¥±¥ó¥¹¤òºîÀ®¤·¤Þ¤¹¡£

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;

¸ß´¹À­

°Ê²¼¤ÎÎã³°¤ò½ü¤­¡¢CREATE SEQUENCE¤Ïɸ½àSQL¤Ë½¾¤¤¤Þ¤¹¡£

• ɸ½à¤Ë¤ª¤±¤ëAS <data type>¼°¤Ï¥µ¥Ý¡¼¥È¤µ¤ì¤Æ¤¤¤Þ¤»¤ó¡£

• ¼¡¤ÎÃͤò¼è¤ê½Ð¤¹¤Ë¤Ï¡¢É¸½à¤ÎNEXT VALUE FOR¼°¤Ç¤Ï¤Ê¤¯nextval()´Ø¿ô¤ò»ÈÍѤ·¤Þ¤¹¡£