How to make a sequence in postgres

2023-06-20

Docssequencehowto

Postgres has inbuilt capability to generate and keep track of sequences. These are usually used for generating some form of key and associating that key with a column. Intuitively, they are similar to Python iterators in the sense you can generate and then consume values with next() like syntax.

#Example

You can create a sequence with:

CREATE SEQUENCE sequence_name;

and then associate that sequence with:

CREATE TABLE table_name (
  id INTEGER DEFAULT nextval('sequence_name'),
  -- ... other things
);

#Customizing the sequence

You can control how the sequence generates with:

CREATE SEQUENCE my_sequence
    AS integer
    INCREMENT 42
    MINVALUE 84
    MAXVALUE 12345678
    START 10
    CACHE 100
    NO CYCLE;
  • START specifies the starting value for the sequence.
  • INCREMENT specifies the amount by which the sequence value increases (or decreases).
  • MINVALUE and MAXVALUE define the minimum and maximum values the sequence can generate.
  • CACHE sets how many sequence values are stored in memory before access
  • CYCLE allows the sequence to wrap around to the minimum value when it reaches the maximum value (or vice versa). Note when CYCLE is not set, the nextval() will error when reaching MAXVALUE + 1

#How to decrement a sequence

Use INCREMENT -1

CREATE SEQUENCE decreasing_sequence INCREMENT -1

#How to use a sequence in a SELECT statement?

See How to use a sequence in a SELECT statement