PostgreSQL: Sequence with START WITH, INCREMENT, MINVALUE and MAXVALUE

Introduction

PostgreSQL sequences are special kinds of databases objects that generate sequences of numerical values according to specified parameters. They are commonly used for creating unique identifiers for rows in a table. In this tutorial, we’ll explore how to create a sequence with the START WITH, INCREMENT, MINVALUE, and MAXVALUE options.

Understanding Sequences

In PostgreSQL, a sequence is a database object that generates a sequence of numbers according to a specified set of rules. This can be extremely helpful for generating unique identifiers for database records. Sequences in PostgreSQL provide several options for customization. Before diving into creating a sequence, let’s briefly touch on these options:

• START WITH: Specifies the initial value of the sequence.
• INCREMENT BY: Determines the interval between sequence numbers.
• MINVALUE: Sets the minimum value for the sequence. If not set, the default is 1 for ascending sequences and the minimum value of the data type for descending.
• MAXVALUE: Establishes the maximum value of the sequence. If not set, the default is the maximum value of the data type for ascending sequences and -1 for descending.
• CACHE: Indicates how many sequence numbers are preallocated and stored in memory for faster access.

Creating a Basic Sequence

Let’s start by creating a basic sequence. This can be done using the CREATE SEQUENCE command:

CREATE SEQUENCE user_id_seq;

This sequence starts at 1 and increments by 1, which are the default values. To retrieve the next value from the sequence, we would use:

SELECT nextval('user_id_seq');

Customizing a Sequence with START WITH and INCREMENT

To start the sequence with a different value and customize the interval between numbers, use the START WITH and INCREMENT BY options:

CREATE SEQUENCE custom_id_seq
    START WITH 100
    INCREMENT BY 10;

This sequence will start at 100 and will increment by 10 each time a new value is requested.

Setting a Minimum Value

If you plan to have a specific minimum value you can set, use the MINVALUE option:

CREATE SEQUENCE minval_id_seq
    MINVALUE 50;

With this option, 50 will become the lowest value the sequence can return.

Setting a Maximum Value

Similarly, if you have an upper limit for the IDs, MAXVALUE can be used:

CREATE SEQUENCE maxval_id_seq
    MAXVALUE 1000;

The sequence will not generate any values higher than 1000.

Advanced Sequence Options

PostgreSQL allows for even more finetuning:

• CYCLE / NO CYCLE: Determines whether the sequence should restart from MINVALUE after reaching MAXVALUE or stop generating new numbers.
• CACHE: Defines how many values to preallocate for faster retrieval.
• ORDER: Guarantees the numbers are returned in the order in which they were created.

Here’s an example incorporating these advanced options:

CREATE SEQUENCE advanced_id_seq
    START WITH 100
    INCREMENT BY 5
    MINVALUE 50
    MAXVALUE 500
    CYCLE
    CACHE 10
    ORDER;

This creates a sequence that cycles back to 50 after reaching 500, caching 10 values at a time, and ensuring they are delivered in order.

Cycling Sequences

When the sequence nears its MAXVALUE, you might want it to reset back to the MINVALUE rather than just stopping. The CYCLE option handles this case:

CREATE SEQUENCE cycling_id_seq
    MINVALUE 1
    MAXVALUE 10
    CYCLE;

SELECT nextval('cycling_id_seq'); --This will start the sequence back at 1 after 10.

Best Practices for Using Sequences

• Avoid using sequences when a simple auto-generated field like SERIAL would suffice.
• Do not use CYCLE unless absolutely necessary as it might lead to duplicate values which could cause issues with unique constraints.
• Use CACHE carefully. Though it can improve performance, in a distributed system it might cause issues when combined with replication and failover.

Conclusion

PostgreSQL sequences are powerful tools for generating unique identifiers in a robust and customizable fashion. In this tutorial, we discussed how to create a sequence and tailor it to your needs using the START WITH, INCREMENT BY, MINVALUE, and MAXVALUE parameters, among others. Remember to use sequences wisely and consider the impact of each option on your database’s integrity and performance.