3.28. Sequences

Sequences are incremental number generators mainly used to fill out primary key columns, but they can be used in several ways 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.

12345678

Attributes
#	Field	Description
1	Default values	Fills the fields below with the default values of the chosen serial type (smallserial, serial, or bigserial): start, increment, and cache 1, minimum 0, and the maximum value of the corresponding integer type. User defined restores the values configured in the sequence.
2	Start	The number from which the values are generated. The default value is 1.
3	Minimum	The minimum value the sequence can generate.
4	Maximum	The maximum value the sequence can generate.
5	Cache	Defines how many sequence numbers are preallocated in memory for fast access. The minimum value, 1, is the same as no cache.
6	Increment	Defines the value added to the current value to generate the next one (the step of the sequence).
7	Owner column	The column associated with the sequence. If the column or its table is dropped, the sequence is dropped as well.
8	Cyclic sequence	When checked, the sequence starts over after reaching its maximum (or minimum) value instead of raising an error.

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