{"Entry":{"collection":"sql","key":"create-sequence","name":"CREATE SEQUENCE","aliases":["createsequence"],"metadata":{"aliases":["createsequence"],"changed_in":["7.2","7.4","8.2","9.2","9.5","10","15","18"],"changes":[{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-createsequence.htm","to_file":"sql-createsequence.html"},"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","usage","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT increment ]"],"removed":[]},"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","notes","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]","[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]","[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]"],"removed":["CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT increment ]"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["parameters","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ OWNED BY { table.column | NONE } ]"],"removed":[]},"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.0"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.1"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ OWNED BY { table_name.column_name | NONE } ]"],"removed":["[ OWNED BY { table.column | NONE } ]"]},"to":"9.2"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT [ BY ] increment ]"],"removed":[]},"to":"9.5"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ AS data_type ]"],"removed":[]},"to":"10"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name"],"removed":[]},"to":"15"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["[ [ NO ] CYCLE ]","[ START [ WITH ] start ]"],"removed":["[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]"]},"to":"18"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"19"}],"content_hash":"475134eaae1b249e28e9f743372f41c40dbd057e65c290a1166efffd43ce76cf","editorial":{},"first_version":"6.4","group":"table","imported_at":"2026-09-30T17:43:35.956764+08:00","last_version":"20","name":"CREATE SEQUENCE","object":"SEQUENCE","position":5,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new sequence generator","purpose_zh":"","related":["alter-sequence","drop-sequence"],"slug":"create-sequence","source_rev":"a709ab85","synopsis":"CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name\n[ AS data_type ]\n[ INCREMENT [ BY ] increment ]\n[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]\n[ [ NO ] CYCLE ]\n[ START [ WITH ] start ]\n[ CACHE cache ]\n[ OWNED BY { table_name.column_name | NONE } ]","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-sequence","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-sequence","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATESEQUENCE","file":"sql-createsequence.html","lang":"en","name":"CREATE SEQUENCE","purpose":"define a new sequence generator","purpose_zh":"","related":["alter-sequence","drop-sequence"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE SEQUENCE\u003c/code\u003e creates a new sequence number generator. This involves creating and initializing a new special single-row table with the name \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e. The generator will be owned by the user issuing the command.\u003c/p\u003e\u003cp\u003eIf 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 cannot be given when creating a temporary sequence. The sequence name must be distinct from the name of any other relation (table, sequence, index, view, materialized view, or foreign table) in the same schema.\u003c/p\u003e\u003cp\u003eAfter a sequence is created, you use the functions \u003ccode class=\"function\"\u003enextval\u003c/code\u003e, \u003ccode class=\"function\"\u003ecurrval\u003c/code\u003e, and \u003ccode class=\"function\"\u003esetval\u003c/code\u003e to operate on the sequence. These functions are documented in \u003ca href=\"/docs/18/functions-sequence.html\" title=\"9.17. Sequence Manipulation Functions\"\u003eSection 9.17\u003c/a\u003e.\u003c/p\u003e\u003cp\u003eAlthough you cannot update a sequence directly, you can use a query like:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT * FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\u003cp\u003eto examine the parameters and current state of a sequence. In particular, the \u003ccode class=\"literal\"\u003elast_value\u003c/code\u003e field of the sequence shows the last value allocated by any session. (Of course, this value might be obsolete by the time it's printed, if other sessions are actively doing \u003ccode class=\"function\"\u003enextval\u003c/code\u003e calls.)\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTEMPORARY\u003c/code\u003e or \u003ccode class=\"literal\"\u003eTEMP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIf 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.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eUNLOGGED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIf specified, the sequence is created as an unlogged sequence. Changes to unlogged sequences are not written to the write-ahead log. They are not crash-safe: an unlogged sequence is automatically reset to its initial state after a crash or unclean shutdown. Unlogged sequences are also not replicated to standby servers.\u003c/p\u003e\u003cp\u003eUnlike unlogged tables, unlogged sequences do not offer a significant performance advantage. This option is mainly intended for sequences associated with unlogged tables via identity columns or serial columns. In those cases, it usually wouldn't make sense to have the sequence WAL-logged and replicated but not its associated table.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eDo not throw an error if a relation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing relation is anything like the sequence that would have been created — it might not even be a sequence.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of the sequence to be created.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe optional clause \u003ccode class=\"literal\"\u003eAS \u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e specifies the data type of the sequence. Valid types are \u003ccode class=\"literal\"\u003esmallint\u003c/code\u003e, \u003ccode class=\"literal\"\u003einteger\u003c/code\u003e, and \u003ccode class=\"literal\"\u003ebigint\u003c/code\u003e. \u003ccode class=\"literal\"\u003ebigint\u003c/code\u003e is the default. The data type determines the default minimum and maximum values of the sequence.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eincrement\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe optional clause \u003ccode class=\"literal\"\u003eINCREMENT BY \u003cem class=\"replaceable\"\u003e\u003ccode\u003eincrement\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 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.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO MINVALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe optional clause \u003ccode class=\"literal\"\u003eMINVALUE \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e determines the minimum value a sequence can generate. If this clause is not supplied or \u003ccode class=\"option\"\u003eNO MINVALUE\u003c/code\u003e is specified, then defaults will be used. The default for an ascending sequence is 1. The default for a descending sequence is the minimum value of the data type.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO MAXVALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe optional clause \u003ccode class=\"literal\"\u003eMAXVALUE \u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e determines the maximum value for the sequence. If this clause is not supplied or \u003ccode class=\"option\"\u003eNO MAXVALUE\u003c/code\u003e is specified, then default values will be used. The default for an ascending sequence is the maximum value of the data type. The default for a descending sequence is -1.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCYCLE\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO CYCLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eCYCLE\u003c/code\u003e option allows the sequence to wrap around when the \u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e or \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e or \u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e, respectively.\u003c/p\u003e\u003cp\u003eIf \u003ccode class=\"literal\"\u003eNO CYCLE\u003c/code\u003e is specified, any calls to \u003ccode class=\"function\"\u003enextval\u003c/code\u003e after the sequence has reached its maximum value will return an error. If neither \u003ccode class=\"literal\"\u003eCYCLE\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNO CYCLE\u003c/code\u003e are specified, \u003ccode class=\"literal\"\u003eNO CYCLE\u003c/code\u003e is the default.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe optional clause \u003ccode class=\"literal\"\u003eSTART WITH \u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e allows the sequence to begin anywhere. The default starting value is \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e for ascending sequences and \u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e for descending ones.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe optional clause \u003ccode class=\"literal\"\u003eCACHE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 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.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOWNED BY\u003c/code\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOWNED BY NONE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eOWNED BY\u003c/code\u003e 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. \u003ccode class=\"literal\"\u003eOWNED BY NONE\u003c/code\u003e, the default, specifies that there is no such association.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eUse \u003ccode class=\"command\"\u003eDROP SEQUENCE\u003c/code\u003e to remove a sequence.\u003c/p\u003e\u003cp\u003eSequences are based on \u003ccode class=\"type\"\u003ebigint\u003c/code\u003e arithmetic, so the range cannot exceed the range of an eight-byte integer (-9223372036854775808 to 9223372036854775807).\u003c/p\u003e\u003cp\u003eBecause \u003ccode class=\"function\"\u003enextval\u003c/code\u003e and \u003ccode class=\"function\"\u003esetval\u003c/code\u003e calls are never rolled back, sequence objects cannot be used if \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003egapless\u003c/span\u003e”\u003c/span\u003e assignment of sequence numbers is needed. It is possible to build gapless assignment by using exclusive locking of a table containing a counter; but this solution is much more expensive than sequence objects, especially if many transactions need sequence numbers concurrently.\u003c/p\u003e\u003cp\u003eUnexpected results might be obtained if a \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e 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 \u003ccode class=\"literal\"\u003elast_value\u003c/code\u003e accordingly. Then, the next \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e-1 uses of \u003ccode class=\"function\"\u003enextval\u003c/code\u003e 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 \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eholes\u003c/span\u003e”\u003c/span\u003e in the sequence.\u003c/p\u003e\u003cp\u003eFurthermore, although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e setting of 10, session A might reserve values 1..10 and return \u003ccode class=\"function\"\u003enextval\u003c/code\u003e=1, then session B might reserve values 11..20 and return \u003ccode class=\"function\"\u003enextval\u003c/code\u003e=11 before session A has generated \u003ccode class=\"function\"\u003enextval\u003c/code\u003e=2. Thus, with a \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e setting of one it is safe to assume that \u003ccode class=\"function\"\u003enextval\u003c/code\u003e values are generated sequentially; with a \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e setting greater than one you should only assume that the \u003ccode class=\"function\"\u003enextval\u003c/code\u003e values are all distinct, not that they are generated purely sequentially. Also, \u003ccode class=\"literal\"\u003elast_value\u003c/code\u003e will reflect the latest value reserved by any session, whether or not it has yet been returned by \u003ccode class=\"function\"\u003enextval\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eAnother consideration is that a \u003ccode class=\"function\"\u003esetval\u003c/code\u003e executed on such a sequence will not be noticed by other sessions until they have used up any preallocated values they have cached.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eCreate an ascending sequence called \u003ccode class=\"literal\"\u003eserial\u003c/code\u003e, starting at 101:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE SEQUENCE serial START 101;\n\u003c/pre\u003e\u003cp\u003eSelect the next number from this sequence:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT nextval('serial');\n\n nextval\n---------\n     101\n\u003c/pre\u003e\u003cp\u003eSelect the next number from this sequence:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT nextval('serial');\n\n nextval\n---------\n     102\n\u003c/pre\u003e\u003cp\u003eUse this sequence in an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e command:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors VALUES (nextval('serial'), 'nothing');\n\u003c/pre\u003e\u003cp\u003eUpdate the sequence value after a \u003ccode class=\"command\"\u003eCOPY FROM\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN;\nCOPY distributors FROM 'input_file';\nSELECT setval('serial', max(id)) FROM distributors;\nEND;\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE SEQUENCE\u003c/code\u003e conforms to the \u003cacronym\u003eSQL\u003c/acronym\u003e standard, with the following exceptions:\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eObtaining the next value is done using the \u003ccode class=\"function\"\u003enextval()\u003c/code\u003e function instead of the standard's \u003ccode class=\"command\"\u003eNEXT VALUE FOR\u003c/code\u003e expression.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eOWNED BY\u003c/code\u003e clause is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension.\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-sequence/?v=18\" title=\"ALTER SEQUENCE\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER SEQUENCE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-sequence/?v=18\" title=\"DROP SEQUENCE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP SEQUENCE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    [ AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e ]\n    [ INCREMENT [ BY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eincrement\u003c/code\u003e\u003c/em\u003e ]\n    [ MINVALUE \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e | NO MINVALUE ] [ MAXVALUE \u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e | NO MAXVALUE ]\n    [ [ NO ] CYCLE ]\n    [ START [ WITH ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e ]\n    [ CACHE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e ]\n    [ OWNED BY { \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e | NONE } ]","synopsis_text":"CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name\n[ AS data_type ]\n[ INCREMENT [ BY ] increment ]\n[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]\n[ [ NO ] CYCLE ]\n[ START [ WITH ] start ]\n[ CACHE cache ]\n[ OWNED BY { table_name.column_name | NONE } ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-sequence","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE SEQUENCE","Summary":"定义一个新的序列发生器","BodyHTML":"\u003cpre\u003eCREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name\n[ AS data_type ]\n[ INCREMENT [ BY ] increment ]\n[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]\n[ [ NO ] CYCLE ]\n[ START [ WITH ] start ]\n[ CACHE cache ]\n[ OWNED BY { table_name.column_name | NONE } ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE SEQUENCE\u003c/code\u003e创建一个新的序列号发生器。这会创建并初始化一个名为\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e的特殊单行表。该发生器归发出该命令的用户所有。\u003c/p\u003e\u003cp\u003e如果给定了模式名，则序列会在指定模式中创建。否则，它会在当前模式中创建。临时序列存在于一个特殊模式中，因此创建临时序列时不能指定模式名。序列名必须不同于同一模式中任何其他关系（表、序列、索引、视图、物化视图或外部表）的名称。\u003c/p\u003e\u003cp\u003e创建序列后，可以使用函数 \u003ccode\u003enextval\u003c/code\u003e、\u003ccode\u003ecurrval\u003c/code\u003e以及 \u003ccode\u003esetval\u003c/code\u003e来操作该序列。这些函数的文档见\u003ca href=\"/docs/18/functions-sequence.html\" rel=\"nofollow\"\u003e第 9.17 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e虽然不能直接更新序列，但可以使用如下查询：\u003c/p\u003e\u003cpre\u003eSELECT * FROM \u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\u003cp\u003e来查看序列的参数和当前状态。特别是，序列的 \u003ccode\u003elast_value\u003c/code\u003e字段会显示任一会话最近分配的值。（当然，如果其他会话正在调用\u003ccode\u003enextval\u003c/code\u003e，那么到该值被打印出来时，它可能已经过时。）\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTEMPORARY\u003c/code\u003e 或 \u003ccode\u003eTEMP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定该项，则该序列对象只为当前会话创建，并会在会话退出时自动删除。临时序列存在期间，已有的同名永久序列在本会话中不可见，除非使用模式限定名来引用它们。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eUNLOGGED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定该项，则该序列会被创建为不记录 WAL 的序列。对不记录 WAL 的序列的更改不会写入预写式日志。它们不是崩溃安全的：在崩溃或非正常关闭之后，不记录 WAL 的序列会自动重置为其初始状态。不记录 WAL 的序列也不会复制到备库。\u003c/p\u003e\u003cp\u003e与不记录 WAL 的表不同，不记录 WAL 的序列并不会带来显著的性能优势。此选项主要用于通过标识列或 serial 列与不记录 WAL 的表关联的序列。在这些情况下，只对序列做 WAL 记录和复制，而不对其关联表这样做，通常没有意义。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果同名关系已经存在，则不抛出错误。在这种情况下会发出一个提示。注意，这并不保证现有关系与本应创建出的序列有任何相似之处 — 它甚至可能根本不是序列。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的序列的名称（可以是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode\u003eAS \u003cem\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 指定序列的数据类型。有效类型是 \u003ccode\u003esmallint\u003c/code\u003e、\u003ccode\u003einteger\u003c/code\u003e 和\u003ccode\u003ebigint\u003c/code\u003e。默认值是\u003ccode\u003ebigint\u003c/code\u003e。数据类型决定了序列的默认最小和最大值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eincrement\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode\u003eINCREMENT BY \u003cem\u003e\u003ccode\u003eincrement\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e指定为了生成新值，要把哪个值加到当前序列值上。正值使序列递增，负值使序列递减。默认值为 1。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003eNO MINVALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode\u003eMINVALUE \u003cem\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e确定序列可生成的最小值。如果未提供此子句，或者指定了 \u003ccode\u003eNO MINVALUE\u003c/code\u003e，则使用默认值。升序序列的默认值为 1。降序序列的默认值为该数据类型的最小值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003eNO MAXVALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode\u003eMAXVALUE \u003cem\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e确定序列的最大值。如果未提供此子句，或者指定了 \u003ccode\u003eNO MAXVALUE\u003c/code\u003e，则使用默认值。升序序列的默认值是该数据类型的最大值。降序序列的默认值是 -1。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eCYCLE\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003eNO CYCLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eCYCLE\u003c/code\u003e选项允许序列在升序序列达到\u003cem\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e、或降序序列达到\u003cem\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e时分别回绕。如果达到该限制，下一个生成的数字将分别是\u003cem\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e或\u003cem\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003cp\u003e如果指定\u003ccode\u003eNO CYCLE\u003c/code\u003e，则当序列达到其限值后，任何对 \u003ccode\u003enextval\u003c/code\u003e的调用都会返回错误。如果既未指定 \u003ccode\u003eCYCLE\u003c/code\u003e也未指定\u003ccode\u003eNO CYCLE\u003c/code\u003e，则默认使用\u003ccode\u003eNO CYCLE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode\u003eSTART WITH \u003cem\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 允许序列从任意值开始。升序序列的默认起始值为\u003cem\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e，降序序列的默认起始值为 \u003cem\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode\u003eCACHE \u003cem\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e指定要预分配并存储在内存中的序列号数量，以便更快访问。最小值为 1（一次只能生成一个值，即不使用缓存），默认值也为 1。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eOWNED BY\u003c/code\u003e \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003eOWNED BY NONE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eOWNED BY\u003c/code\u003e选项使该序列与一个特定表列关联起来，这样如果该列（或整张表）被删除，该序列也会自动删除。指定的表必须与该序列具有相同的拥有者，并且位于同一模式中。默认的 \u003ccode\u003eOWNED BY NONE\u003c/code\u003e表示不存在这种关联。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e使用\u003ccode\u003eDROP SEQUENCE\u003c/code\u003e删除序列。\u003c/p\u003e\u003cp\u003e序列基于\u003ccode\u003ebigint\u003c/code\u003e算术，因此其范围不能超过八字节整数的范围（-9223372036854775808 到 9223372036854775807）。\u003c/p\u003e\u003cp\u003e由于\u003ccode\u003enextval\u003c/code\u003e和\u003ccode\u003esetval\u003c/code\u003e调用从不回滚，如果需要对序列号进行\u003cspan\u003e“\u003cspan\u003e无间隙\u003c/span\u003e”\u003c/span\u003e分配，就不能使用序列对象。可以通过对包含计数器的表加排他锁来构造无间隙分配；但这种方案比序列对象昂贵得多，尤其是在许多事务需要并发获取序列号时。\u003c/p\u003e\u003cp\u003e如果对一个将由多个会话并发使用的序列对象设置了大于 1 的\u003cem\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e，则可能得到意想不到的结果。每个会话会在一次访问该序列对象时分配并缓存连续的序列值，并相应增加该序列对象的\u003ccode\u003elast_value\u003c/code\u003e。随后，该会话中接下来的 \u003cem\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e-1 次\u003ccode\u003enextval\u003c/code\u003e 调用会直接返回预分配的值，而不接触序列对象。因此，任何在会话中已分配但未使用的数字都会在会话结束时丢失，从而在序列中造成\u003cspan\u003e“\u003cspan\u003e空洞\u003c/span\u003e”\u003c/span\u003e。\u003c/p\u003e\u003cp\u003e此外，虽然多个会话保证会分配到互不相同的序列值，但从所有会话整体来看，这些值的生成顺序可能会乱序。例如，当\u003cem\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e设置为 10 时，会话 A 可能保留值 1..10 并返回\u003ccode\u003enextval\u003c/code\u003e=1；然后会话 B 可能保留值 11..20 并返回\u003ccode\u003enextval\u003c/code\u003e=11，而此时会话 A 尚未生成 \u003ccode\u003enextval\u003c/code\u003e=2。因此，当\u003cem\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e设置为 1 时，可以安全地假定 \u003ccode\u003enextval\u003c/code\u003e值按顺序生成；当\u003cem\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e设置大于 1 时，只应假定 \u003ccode\u003enextval\u003c/code\u003e值彼此不同，而不应假定它们严格按顺序生成。此外，\u003ccode\u003elast_value\u003c/code\u003e会反映任一会话最近保留的值，不管该值是否已经由\u003ccode\u003enextval\u003c/code\u003e返回。\u003c/p\u003e\u003cp\u003e另一个需要注意的问题是，在这种序列上执行的\u003ccode\u003esetval\u003c/code\u003e 不会立刻被其他会话察觉，直到它们用完自己缓存的所有预分配值为止。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e创建一个名为\u003ccode\u003eserial\u003c/code\u003e的升序序列，从 101 开始：\u003c/p\u003e\u003cpre\u003eCREATE SEQUENCE serial START 101;\n\u003c/pre\u003e\u003cp\u003e从该序列中取出下一个数字：\u003c/p\u003e\u003cpre\u003eSELECT nextval(\u0026#39;serial\u0026#39;);\n\n nextval\n---------\n     101\n\u003c/pre\u003e\u003cp\u003e从该序列中取出下一个数字：\u003c/p\u003e\u003cpre\u003eSELECT nextval(\u0026#39;serial\u0026#39;);\n\n nextval\n---------\n     102\n\u003c/pre\u003e\u003cp\u003e在\u003ccode\u003eINSERT\u003c/code\u003e命令中使用该序列：\u003c/p\u003e\u003cpre\u003eINSERT INTO distributors VALUES (nextval(\u0026#39;serial\u0026#39;), \u0026#39;nothing\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e在一次\u003ccode\u003eCOPY FROM\u003c/code\u003e之后更新序列值：\u003c/p\u003e\u003cpre\u003eBEGIN;\nCOPY distributors FROM \u0026#39;input_file\u0026#39;;\nSELECT setval(\u0026#39;serial\u0026#39;, max(id)) FROM distributors;\nEND;\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE SEQUENCE\u003c/code\u003e符合\u003cacronym\u003eSQL\u003c/acronym\u003e 标准，但下列情况除外：\u003c/p\u003e\u003cdiv\u003e\u003cul\u003e\u003cli\u003e\u003cp\u003e获取下一个值是使用\u003ccode\u003enextval()\u003c/code\u003e函数，而不是标准的 \u003ccode\u003eNEXT VALUE FOR\u003c/code\u003e表达式完成的。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e\u003ccode\u003eOWNED BY\u003c/code\u003e子句是\u003cspan\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-sequence/?v=18\" title=\"ALTER SEQUENCE\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER SEQUENCE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-sequence/?v=18\" title=\"DROP SEQUENCE\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP SEQUENCE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"3ac9d434c8c58ea5a138af82dcdf6ad919139f6c5924331a104877603fe2f591","Payload":{"purpose_zh":"定义一个新的序列发生器","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE SEQUENCE\u003c/code\u003e创建一个新的序列号发生器。这会创建并初始化一个名为\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e的特殊单行表。该发生器归发出该命令的用户所有。\u003c/p\u003e\u003cp\u003e如果给定了模式名，则序列会在指定模式中创建。否则，它会在当前模式中创建。临时序列存在于一个特殊模式中，因此创建临时序列时不能指定模式名。序列名必须不同于同一模式中任何其他关系（表、序列、索引、视图、物化视图或外部表）的名称。\u003c/p\u003e\u003cp\u003e创建序列后，可以使用函数 \u003ccode class=\"function\"\u003enextval\u003c/code\u003e、\u003ccode class=\"function\"\u003ecurrval\u003c/code\u003e以及 \u003ccode class=\"function\"\u003esetval\u003c/code\u003e来操作该序列。这些函数的文档见\u003ca href=\"/docs/18/functions-sequence.html\" title=\"9.17. 序列操作函数\"\u003e第 9.17 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e虽然不能直接更新序列，但可以使用如下查询：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT * FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e;\n\u003c/pre\u003e\u003cp\u003e来查看序列的参数和当前状态。特别是，序列的 \u003ccode class=\"literal\"\u003elast_value\u003c/code\u003e字段会显示任一会话最近分配的值。（当然，如果其他会话正在调用\u003ccode class=\"function\"\u003enextval\u003c/code\u003e，那么到该值被打印出来时，它可能已经过时。）\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTEMPORARY\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eTEMP\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定该项，则该序列对象只为当前会话创建，并会在会话退出时自动删除。临时序列存在期间，已有的同名永久序列在本会话中不可见，除非使用模式限定名来引用它们。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eUNLOGGED\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定该项，则该序列会被创建为不记录 WAL 的序列。对不记录 WAL 的序列的更改不会写入预写式日志。它们不是崩溃安全的：在崩溃或非正常关闭之后，不记录 WAL 的序列会自动重置为其初始状态。不记录 WAL 的序列也不会复制到备库。\u003c/p\u003e\u003cp\u003e与不记录 WAL 的表不同，不记录 WAL 的序列并不会带来显著的性能优势。此选项主要用于通过标识列或 serial 列与不记录 WAL 的表关联的序列。在这些情况下，只对序列做 WAL 记录和复制，而不对其关联表这样做，通常没有意义。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果同名关系已经存在，则不抛出错误。在这种情况下会发出一个提示。注意，这并不保证现有关系与本应创建出的序列有任何相似之处 — 它甚至可能根本不是序列。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要创建的序列的名称（可以是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode class=\"literal\"\u003eAS \u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 指定序列的数据类型。有效类型是 \u003ccode class=\"literal\"\u003esmallint\u003c/code\u003e、\u003ccode class=\"literal\"\u003einteger\u003c/code\u003e 和\u003ccode class=\"literal\"\u003ebigint\u003c/code\u003e。默认值是\u003ccode class=\"literal\"\u003ebigint\u003c/code\u003e。数据类型决定了序列的默认最小和最大值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eincrement\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode class=\"literal\"\u003eINCREMENT BY \u003cem class=\"replaceable\"\u003e\u003ccode\u003eincrement\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e指定为了生成新值，要把哪个值加到当前序列值上。正值使序列递增，负值使序列递减。默认值为 1。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO MINVALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode class=\"literal\"\u003eMINVALUE \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e确定序列可生成的最小值。如果未提供此子句，或者指定了 \u003ccode class=\"option\"\u003eNO MINVALUE\u003c/code\u003e，则使用默认值。升序序列的默认值为 1。降序序列的默认值为该数据类型的最小值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO MAXVALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode class=\"literal\"\u003eMAXVALUE \u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e确定序列的最大值。如果未提供此子句，或者指定了 \u003ccode class=\"option\"\u003eNO MAXVALUE\u003c/code\u003e，则使用默认值。升序序列的默认值是该数据类型的最大值。降序序列的默认值是 -1。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCYCLE\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eNO CYCLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eCYCLE\u003c/code\u003e选项允许序列在升序序列达到\u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e、或降序序列达到\u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e时分别回绕。如果达到该限制，下一个生成的数字将分别是\u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e或\u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003cp\u003e如果指定\u003ccode class=\"literal\"\u003eNO CYCLE\u003c/code\u003e，则当序列达到其限值后，任何对 \u003ccode class=\"function\"\u003enextval\u003c/code\u003e的调用都会返回错误。如果既未指定 \u003ccode class=\"literal\"\u003eCYCLE\u003c/code\u003e也未指定\u003ccode class=\"literal\"\u003eNO CYCLE\u003c/code\u003e，则默认使用\u003ccode class=\"literal\"\u003eNO CYCLE\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode class=\"literal\"\u003eSTART WITH \u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 允许序列从任意值开始。升序序列的默认起始值为\u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e，降序序列的默认起始值为 \u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e可选子句\u003ccode class=\"literal\"\u003eCACHE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e指定要预分配并存储在内存中的序列号数量，以便更快访问。最小值为 1（一次只能生成一个值，即不使用缓存），默认值也为 1。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOWNED BY\u003c/code\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOWNED BY NONE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eOWNED BY\u003c/code\u003e选项使该序列与一个特定表列关联起来，这样如果该列（或整张表）被删除，该序列也会自动删除。指定的表必须与该序列具有相同的拥有者，并且位于同一模式中。默认的 \u003ccode class=\"literal\"\u003eOWNED BY NONE\u003c/code\u003e表示不存在这种关联。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e使用\u003ccode class=\"command\"\u003eDROP SEQUENCE\u003c/code\u003e删除序列。\u003c/p\u003e\u003cp\u003e序列基于\u003ccode class=\"type\"\u003ebigint\u003c/code\u003e算术，因此其范围不能超过八字节整数的范围（-9223372036854775808 到 9223372036854775807）。\u003c/p\u003e\u003cp\u003e由于\u003ccode class=\"function\"\u003enextval\u003c/code\u003e和\u003ccode class=\"function\"\u003esetval\u003c/code\u003e调用从不回滚，如果需要对序列号进行\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e无间隙\u003c/span\u003e”\u003c/span\u003e分配，就不能使用序列对象。可以通过对包含计数器的表加排他锁来构造无间隙分配；但这种方案比序列对象昂贵得多，尤其是在许多事务需要并发获取序列号时。\u003c/p\u003e\u003cp\u003e如果对一个将由多个会话并发使用的序列对象设置了大于 1 的\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e，则可能得到意想不到的结果。每个会话会在一次访问该序列对象时分配并缓存连续的序列值，并相应增加该序列对象的\u003ccode class=\"literal\"\u003elast_value\u003c/code\u003e。随后，该会话中接下来的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e-1 次\u003ccode class=\"function\"\u003enextval\u003c/code\u003e 调用会直接返回预分配的值，而不接触序列对象。因此，任何在会话中已分配但未使用的数字都会在会话结束时丢失，从而在序列中造成\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e空洞\u003c/span\u003e”\u003c/span\u003e。\u003c/p\u003e\u003cp\u003e此外，虽然多个会话保证会分配到互不相同的序列值，但从所有会话整体来看，这些值的生成顺序可能会乱序。例如，当\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e设置为 10 时，会话 A 可能保留值 1..10 并返回\u003ccode class=\"function\"\u003enextval\u003c/code\u003e=1；然后会话 B 可能保留值 11..20 并返回\u003ccode class=\"function\"\u003enextval\u003c/code\u003e=11，而此时会话 A 尚未生成 \u003ccode class=\"function\"\u003enextval\u003c/code\u003e=2。因此，当\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e设置为 1 时，可以安全地假定 \u003ccode class=\"function\"\u003enextval\u003c/code\u003e值按顺序生成；当\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e设置大于 1 时，只应假定 \u003ccode class=\"function\"\u003enextval\u003c/code\u003e值彼此不同，而不应假定它们严格按顺序生成。此外，\u003ccode class=\"literal\"\u003elast_value\u003c/code\u003e会反映任一会话最近保留的值，不管该值是否已经由\u003ccode class=\"function\"\u003enextval\u003c/code\u003e返回。\u003c/p\u003e\u003cp\u003e另一个需要注意的问题是，在这种序列上执行的\u003ccode class=\"function\"\u003esetval\u003c/code\u003e 不会立刻被其他会话察觉，直到它们用完自己缓存的所有预分配值为止。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e创建一个名为\u003ccode class=\"literal\"\u003eserial\u003c/code\u003e的升序序列，从 101 开始：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE SEQUENCE serial START 101;\n\u003c/pre\u003e\u003cp\u003e从该序列中取出下一个数字：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT nextval('serial');\n\n nextval\n---------\n     101\n\u003c/pre\u003e\u003cp\u003e从该序列中取出下一个数字：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT nextval('serial');\n\n nextval\n---------\n     102\n\u003c/pre\u003e\u003cp\u003e在\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e命令中使用该序列：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors VALUES (nextval('serial'), 'nothing');\n\u003c/pre\u003e\u003cp\u003e在一次\u003ccode class=\"command\"\u003eCOPY FROM\u003c/code\u003e之后更新序列值：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eBEGIN;\nCOPY distributors FROM 'input_file';\nSELECT setval('serial', max(id)) FROM distributors;\nEND;\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE SEQUENCE\u003c/code\u003e符合\u003cacronym\u003eSQL\u003c/acronym\u003e 标准，但下列情况除外：\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e获取下一个值是使用\u003ccode class=\"function\"\u003enextval()\u003c/code\u003e函数，而不是标准的 \u003ccode class=\"command\"\u003eNEXT VALUE FOR\u003c/code\u003e表达式完成的。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eOWNED BY\u003c/code\u003e子句是\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-sequence/?v=18\" title=\"ALTER SEQUENCE\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER SEQUENCE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-sequence/?v=18\" title=\"DROP SEQUENCE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP SEQUENCE\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    [ AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003edata_type\u003c/code\u003e\u003c/em\u003e ]\n    [ INCREMENT [ BY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eincrement\u003c/code\u003e\u003c/em\u003e ]\n    [ MINVALUE \u003cem class=\"replaceable\"\u003e\u003ccode\u003eminvalue\u003c/code\u003e\u003c/em\u003e | NO MINVALUE ] [ MAXVALUE \u003cem class=\"replaceable\"\u003e\u003ccode\u003emaxvalue\u003c/code\u003e\u003c/em\u003e | NO MAXVALUE ]\n    [ [ NO ] CYCLE ]\n    [ START [ WITH ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e ]\n    [ CACHE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecache\u003c/code\u003e\u003c/em\u003e ]\n    [ OWNED BY { \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e | NONE } ]","synopsis_text":"CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name\n[ AS data_type ]\n[ INCREMENT [ BY ] increment ]\n[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]\n[ [ NO ] CYCLE ]\n[ START [ WITH ] start ]\n[ CACHE cache ]\n[ OWNED BY { table_name.column_name | NONE } ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}