Sequences, SERIAL & IDENTITY

Auto-incrementing primary keys are fundamental to most database designs. PostgreSQL provides three ways to achieve this: raw sequences, the legacy SERIAL pseudo-type, and the modern SQL-standard GENERATED AS IDENTITY. Understanding the differences helps you choose the right approach for new designs.

Sequences

A sequence is a database object that generates unique numbers. All auto-increment mechanisms in PostgreSQL ultimately use sequences under the hood.

Creating and Using Sequences

-- Create a sequence
CREATE SEQUENCE order_id_seq
    START WITH  1000    -- first value
    INCREMENT BY 1      -- step size
    MINVALUE    1000
    MAXVALUE    9999999
    NO CYCLE;           -- error if max is reached (vs CYCLE to wrap around)

-- Get the next value (advances the sequence)
SELECT NEXTVAL('order_id_seq');  -- 1000
SELECT NEXTVAL('order_id_seq');  -- 1001
SELECT NEXTVAL('order_id_seq');  -- 1002

-- Get the current value (does NOT advance the sequence)
-- Only valid after NEXTVAL has been called in the same session
SELECT CURRVAL('order_id_seq');  -- 1002

-- Manually set the sequence to a specific value
SELECT SETVAL('order_id_seq', 5000);           -- next call returns 5001
SELECT SETVAL('order_id_seq', 5000, false);    -- next call returns 5000

-- Use in an INSERT
INSERT INTO orders (id, customer_id) VALUES (NEXTVAL('order_id_seq'), 42);

Sequence Options

CREATE SEQUENCE custom_seq
    AS INTEGER          -- data type (SMALLINT, INTEGER, or BIGINT)
    START WITH 1
    INCREMENT BY 5      -- count by 5
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 100           -- pre-allocate 100 values (faster for high-traffic)
    NO CYCLE;

Gaps in Sequences

Sequences are NOT transactional — a NEXTVAL is consumed even if the transaction rolls back:

BEGIN;
INSERT INTO orders (customer_id) VALUES (1);
-- order gets id = 1000
ROLLBACK;  -- order is cancelled...

INSERT INTO orders (customer_id) VALUES (2);
-- ...but next order gets id = 1001, NOT 1000. Gap exists.

This is intentional and expected. Gaps in auto-increment IDs are normal. Never assume IDs are consecutive.

SERIAL — Legacy Auto-Increment

SERIAL is a PostgreSQL shorthand (a "pseudo-type") that automatically:

1. Creates a sequence
2. Sets the column default to NEXTVAL('sequence_name')
3. Marks the column as NOT NULL

-- This:
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER
);

-- Is exactly equivalent to this:
CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (
    id          INTEGER NOT NULL DEFAULT NEXTVAL('orders_id_seq'),
    customer_id INTEGER
);
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;
ALTER TABLE orders ADD PRIMARY KEY (id);

SERIAL Variants

CREATE TABLE user_activity (
    event_id BIGSERIAL PRIMARY KEY,  -- use BIGSERIAL for high-volume tables
    user_id  INTEGER,
    event    TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

The SERIAL Drawback

Because SERIAL is just a sequence with a default, the column is not truly "generated by the database" — applications can bypass it:

-- This works and overrides the sequence! Can cause conflicts later.
INSERT INTO orders (id, customer_id) VALUES (999, 1);

GENERATED AS IDENTITY — Modern Standard

GENERATED AS IDENTITY was introduced in PostgreSQL 10 and is the SQL-standard way to define auto-increment columns. It has two sub-modes:

GENERATED ALWAYS AS IDENTITY

The database always generates the value. You cannot INSERT or UPDATE with an explicit value unless you use OVERRIDING SYSTEM VALUE:

CREATE TABLE customers (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE
);

-- This works — ID is auto-generated
INSERT INTO customers (name, email) VALUES ('Alice', 'alice@example.com');

-- This fails! Cannot insert explicit value
INSERT INTO customers (id, name) VALUES (99, 'Bob');
-- ERROR: cannot insert a non-DEFAULT value into column "id"

-- Override only when truly needed (e.g., data migration)
INSERT INTO customers (id, name) VALUES (99, 'Bob') OVERRIDING SYSTEM VALUE;

GENERATED BY DEFAULT AS IDENTITY

The database generates the value by default, but explicit values are allowed:

CREATE TABLE products (
    id      INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name    TEXT,
    price   NUMERIC(10,2)
);

-- Auto-generated
INSERT INTO products (name, price) VALUES ('Widget', 9.99);

-- Explicit ID also allowed (useful for data migrations)
INSERT INTO products (id, name, price) VALUES (500, 'Special Item', 99.99);

Customizing IDENTITY Sequences

CREATE TABLE events (
    id INTEGER GENERATED ALWAYS AS IDENTITY (
        START WITH  1000
        INCREMENT BY 1
        CACHE       50
    ) PRIMARY KEY,
    event_name TEXT
);

Managing IDENTITY Sequences

-- Change the sequence behavior after creation
ALTER TABLE events ALTER COLUMN id RESTART WITH 2000;
ALTER TABLE events ALTER COLUMN id SET INCREMENT BY 5;

-- Resync after bulk insert with explicit IDs
ALTER TABLE customers ALTER COLUMN id RESTART WITH (SELECT MAX(id) + 1 FROM customers);

Choosing Between SERIAL and IDENTITY

Inspecting Sequences

-- List all sequences in the database
SELECT sequence_name, data_type, start_value, increment
FROM information_schema.sequences
WHERE sequence_schema = 'public';

-- See sequence details (psql)
\d orders_id_seq

-- Get the current value of a sequence
SELECT last_value, is_called FROM orders_id_seq;
-- is_called = false means NEXTVAL hasn't been called yet (last_value is the start value)

Getting Last Inserted ID

-- Most reliable: use RETURNING (see DML chapter)
INSERT INTO orders (customer_id) VALUES (1) RETURNING id;

-- LASTVAL() — returns the most recently generated value in THIS SESSION
-- (from any sequence, whichever NEXTVAL was called most recently)
INSERT INTO orders (customer_id) VALUES (1);
SELECT LASTVAL();  -- returns the ID just generated

-- CURRVAL('sequence_name') — the last value from a SPECIFIC sequence in this session
SELECT CURRVAL('orders_id_seq');