OWNED BY NONE, the default, specifies that there is no such association. The specified table must have the same owner and be in the same schema as the sequence. The OWNED BY 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. If neither CYCLE or NO CYCLE are specified, NO CYCLE is the default. If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value will return an error. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively. The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default. The optional clause CACHE cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The default starting value is minvalue for ascending sequences and maxvalue for descending ones. The optional clause START WITH start allows the sequence to begin anywhere. The default for a descending sequence is -1. ![]() The default for an ascending sequence is the maximum value of the data type. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used. The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. The default for a descending sequence is the minimum value of the data type. The default for an ascending sequence is 1. If this clause is not supplied or NO MINVALUE is specified, then defaults will be used. The optional clause MINVALUE minvalue determines the minimum value a sequence can generate. A positive value will make an ascending sequence, a negative one a descending sequence. The optional clause INCREMENT BY increment specifies which value is added to the current sequence value to create a new value. The data type determines the default minimum and maximum values of the sequence. Valid types are smallint, integer, and bigint. The optional clause AS data_type specifies the data type of the sequence. The name (optionally schema-qualified) of the sequence to be created. 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. IF NOT EXISTSĭo not throw an error if a relation with the same name already exists. In those cases, it usually wouldn't make sense to have the sequence WAL-logged and replicated but not its associated table. This option is mainly intended for sequences associated with unlogged tables via identity columns or serial columns. Unlike unlogged tables, unlogged sequences do not offer a significant performance advantage. ![]() Unlogged sequences are also not replicated to standby servers. They are not crash-safe: an unlogged sequence is automatically reset to its initial state after a crash or unclean shutdown. Changes to unlogged sequences are not written to the write-ahead log. If specified, the sequence is created as an unlogged sequence. 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. If specified, the sequence object is created only for this session, and is automatically dropped on session exit. (Of course, this value might be obsolete by the time it's printed, if other sessions are actively doing nextval calls.) In particular, the last_value field of the sequence shows the last value allocated by any session. ![]() To examine the parameters and current state of a sequence. These functions are documented in Section 9.17.Īlthough you cannot update a sequence directly, you can use a query like: 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.Īfter a sequence is created, you use the functions nextval, currval, and setval to operate on the sequence. Temporary sequences exist in a special schema, so a schema name cannot be given when creating a temporary sequence. Otherwise it is created in the current schema. ![]() If a schema name is given then the sequence is created in the specified schema. The generator will be owned by the user issuing the command. This involves creating and initializing a new special single-row table with the name name. CREATE SEQUENCE creates a new sequence number generator.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |