Sequences

About

A sequence in PostgreSQL is a special database object that generates a monotonically increasing (or decreasing) series of unique numeric values.

It can be customized (step, min, max, etc.)

Sequences are not rolledback in transactions, once you call nextval(), the value is gone forever, even if your transaction fails.

This is by design — so that sequence numbers are always unique even under concurrency.

Creating

CREATE SEQUENCE IF NOT EXISTS seq_name;

-- Specify the type of sequence (smallint | int | bigint)
CREATE SEQUENCE IF NOT EXISTS seq_name AS bigint
    START WITH 1000
    INCREMENT BY 10
    MINVALUE 1000
    MAXVALUE 1000000
    CACHE 100
    -- When the value reaches the maxvalue or minvalue it starts over
    CYCLE
;

Advance sequence

SELECT nextval('seq_name');

Get current value

SELECT currval('seq_name');

Set sequence to specific value

SELECT setval('seq_name', value);

Deleting

DROP SEQUENCE seq_name;

List all sequences

SELECT relname sequence_name
FROM pg_class
WHERE
    relkind = 'S'
;

Sharing Sequence

Share sequence between tables like:

CREATE SEQUENCE seq_name START WITH 100

CREATE TABLE apples (
    fruit_id INT DEFAULT nextval('seq_name') NOT NULL
)

CREATE TABLE mangoes (
    fruit_id INT DEFAULT nextval('seq_name') NOT NULL
);