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 upon 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 basically composed of integer attributes so in order 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.

Jun 14, 2023 at 10:57