PostgreSQL: Descending sequence and CYCLE sequence

Introduction

In PostgreSQL, sequences are special schemas that generate ordered integers. They are often used to create unique identifiers for records. We’ll explore how to create a sequence that descends and how to make a sequence cycle when it reaches its limits.

Understanding Sequences

A sequence in PostgreSQL is a user-defined schema object that generates a sequence of numerical values according to a specified incrementation pattern. Sequences are most commonly used for creating unique identifiers for rows within a database table. By default, sequences increment by 1, starting at 1, but PostgreSQL allows significant flexibility.

Creating a Basic Ascending Sequence

CREATE SEQUENCE public.my_sequence;

This creates a default ascending sequence starting at 1 and increasing by 1 each time a value is requested.

Descending Sequences in PostgreSQL

To create a descending sequence, you’ll need to use the INCREMENT BY option with a negative value. You can also set a MINVALUE that the sequence should not go below.

CREATE SEQUENCE public.descending_sequence
 INCREMENT BY -1
 MINVALUE 1
 START WITH 100;

This creates a sequence that starts at 100 and decrements by 1 until it reaches the set minimum value of 1.

Using a Descending Sequence

SELECT nextval('public.descending_sequence');

Each call to nextval will decrement the sequence. When it hits the minimum value, you’ll get an error unless you configure the sequence to cycle.

CYCLE Sequences

CYCLE sequences are designed to reset to a predefined value and restart the incrementation pattern once they reach their maximum or minimum value.

Creating a CYCLE Sequence

CREATE SEQUENCE public.cycle_sequence
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 10
 START WITH 1
 CYCLE;

The CYCLING attribute of the sequence tells PostgreSQL to go back to the minimum value after the maximum value is reached.

Testing the CYCLE Sequence

SELECT nextval('public.cycle_sequence');

If you keep calling nextval, once the sequence hits its maxvalue of 10, it will reset to minvalue of 1.

Advanced Sequence Operations

Altering Sequences

You can modify an existing sequence to add CYCLE functionality.

ALTER SEQUENCE public.descending_sequence CYCLE;

This alteration makes the above created descending_sequence cycle when it hits its minvalue.

Setting Limits and Caching

PostgreSQL also allows setting cache size to preallocate sequence numbers for performance and determining NO CYCLE behavior for further customization.

CREATE SEQUENCE public.advanced_sequence
  INCREMENT BY 2
  START WITH 20
  MAXVALUE 100
  MINVALUE 20
  CACHE 10
  NO CYCLE;

This will allocate 10 numbers at a time for efficiency but will not cycle after reaching the maxvalue.

Conclusion

Sequences are powerful tools within PostgreSQL for generating unique identifiers and controlling data input. Understanding how to create and manipulate both descending and CYCLE sequences provides additional control over your database’s behavior and helps prevent errors. By carefully configuring your sequences, you can ensure your databases remain efficient and error-free.