PostgreSQL Sequences

A sequence is a database object that generates a series of unique numeric values. Each time a sequence is called, it returns the next number in the series. Sequences are the mechanism behind auto-incrementing primary keys — when a column is defined as SERIAL, PostgreSQL creates a sequence automatically behind the scenes.

Why Sequences?

Generating unique IDs for rows is a fundamental database requirement. Without a reliable way to produce unique numbers, two concurrent inserts could end up with the same ID. Sequences solve this problem at the database level: each call to a sequence returns a value that has never been returned before and will never be returned again.

How SERIAL Works Internally

When a column is defined as SERIAL, PostgreSQL performs three steps automatically:

1. Creates a sequence object (e.g., employees_id_seq)
2. Sets the column's default value to nextval('employees_id_seq')
3. Marks the sequence as "owned by" that column — dropping the column drops the sequence too

This is convenient but hides the underlying sequence. Understanding sequences directly gives much more control.

Creating a Sequence Manually

Basic Syntax

CREATE SEQUENCE sequence_name;

With Options

CREATE SEQUENCE order_number_seq
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 9999999
    NO CYCLE;

Sequence Functions

-- Create a sequence
CREATE SEQUENCE invoice_seq START WITH 1 INCREMENT BY 1;

-- Get the next value
SELECT nextval('invoice_seq');  -- Returns: 1
SELECT nextval('invoice_seq');  -- Returns: 2
SELECT nextval('invoice_seq');  -- Returns: 3

-- Get the current value (same session only)
SELECT currval('invoice_seq');  -- Returns: 3

Using a Sequence as a Column Default

CREATE SEQUENCE employee_id_seq START WITH 100 INCREMENT BY 5;

CREATE TABLE employees (
    id INT DEFAULT nextval('employee_id_seq') PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO employees (name) VALUES ('Alice');
INSERT INTO employees (name) VALUES ('Bob');

SELECT * FROM employees;
-- id: 100, 105

IDs are generated in steps of 5, starting from 100.

The GENERATED Clause (Modern Alternative to SERIAL)

PostgreSQL 10 introduced the SQL-standard GENERATED clause, which is preferred over SERIAL in new code:

CREATE TABLE products (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

GENERATED ALWAYS AS IDENTITY creates an identity column. PostgreSQL always generates the value — attempting to insert a manual value will fail. Use GENERATED BY DEFAULT AS IDENTITY to allow manual overrides:

CREATE TABLE products (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100)
);

-- Manual insert is allowed with BY DEFAULT
INSERT INTO products (id, name) VALUES (500, 'Special Product');

Resetting a Sequence

After deleting and re-inserting data, the sequence may need to be reset to avoid large ID gaps:

-- Reset to start from 1
ALTER SEQUENCE employees_id_seq RESTART WITH 1;

-- Or use setval
SELECT setval('employees_id_seq', 1, false);
-- false means the next call to nextval returns 1 (not 2)

Listing Sequences

-- In psql
\ds

-- Or query information_schema
SELECT sequence_name FROM information_schema.sequences
WHERE sequence_schema = 'public';

Dropping a Sequence

DROP SEQUENCE invoice_seq;

-- If the sequence is owned by a column, drop that too
DROP SEQUENCE invoice_seq CASCADE;

Gap Behavior in Sequences

An important property of sequences is that they are non-transactional for performance reasons. If a transaction uses nextval and is then rolled back, the sequence value is NOT returned — it is permanently consumed. This means gaps in ID sequences are normal and expected. For example, IDs might be 1, 2, 5, 6 if rows 3 and 4 were inserted in transactions that were later rolled back.

Gaps in IDs are not a problem for database integrity. A sequence's purpose is uniqueness, not continuity.

Example: Custom Invoice Numbering

CREATE SEQUENCE invoice_number_seq
    START WITH 10000
    INCREMENT BY 1
    NO CYCLE;

CREATE TABLE invoices (
    invoice_number INT DEFAULT nextval('invoice_number_seq') PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    amount NUMERIC(10, 2),
    issued_date DATE DEFAULT CURRENT_DATE
);

INSERT INTO invoices (customer_name, amount) VALUES ('Alice Corp', 5000.00);
INSERT INTO invoices (customer_name, amount) VALUES ('Bob Ltd', 12500.00);

SELECT * FROM invoices;
-- invoice_number: 10000, 10001

Key Points

• A sequence generates unique, incrementing numbers suitable for use as primary keys.
• SERIAL is a shorthand that creates a sequence automatically; GENERATED AS IDENTITY is the modern SQL-standard equivalent.
• Use nextval() to get the next value, currval() to retrieve the last value used in the current session.
• Sequences are non-transactional — rolled-back transactions do not return consumed values, causing gaps.
• Gaps in sequences are normal and do not indicate data loss.
• ALTER SEQUENCE RESTART resets a sequence to a specified starting value.