3.28. Sequences
Sequences are incremental number generators mainly used to fill out primary key columns, but they can be used in several manners depending on the user's need. Internally, in PostgreSQL, sequences are single-row tables that can be handled by three specific functions: nextval, currval, and setval. As stated by the docs the function nextval generates the next value for the sequence based on its current state. The second, currval returns the current value of the sequence. Finally, the function setval is used to modify the value of the sequence. These objects are composed of integer attributes so to avoid overflow errors take care to use values that do not exceed the limits of smallint, integer, or bigint data types.
| Attribute | Description |
|---|---|
Default values | Fill the input fields with the default values according to the integer type of the sequence. |
Cyclic | Indicates that the sequence will start over when it reaches the maximum or minimum values. When reaching the maximum the sequence will wrap around and start to generate values from the minimum value. By the same logic, reaching its minimum value the sequence will generate new values starting from its maximum. |
Start | This optional value indicates from which number the values must be generated. |
Increment | Defines the value used as a step of the sequence. |
Minimum | Minimum numeric limit for the sequence. |
Maximum | Maximum numeric limit for the sequence. |
Cache | Defines how many sequence numbers must be preallocated in the memory for fast access. The minimum value of 1 is the same as no cache. |
Owner Col. | Specifies the column which is associated with the sequence. In case of column or table dropping the sequence will be dropped as well. |
References
Sequence DDL
https://www.postgresql.org/docs/current/static/sql-createsequence.html
Sequence functions
https://www.postgresql.org/docs/current/static/functions-sequence.html
Numeric data types
https://www.postgresql.org/docs/current/static/datatype-numeric.html