Identity Columns & Sequences

Every table needs a reliable way to generate unique identifiers for each row. You have already used SERIAL throughout this course — but there is more to the story. PostgreSQL has a more modern approach called identity columns, and under the hood both SERIAL and identity columns are powered by an object called a sequence. Understanding how sequences work gives you full control over how IDs are generated, what numbers they produce, and how to fix things when they go wrong.

How SERIAL Works Under the Hood

When you define a column as SERIAL, PostgreSQL does three things automatically behind the scenes. It creates a sequence object, sets the column default to pull the next value from that sequence, and marks the column as NOT NULL. It is convenient shorthand — but it is not a true data type. It is just syntax sugar for a sequence-backed integer column.

-- What you write:
CREATE TABLE products (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

-- What PostgreSQL actually creates behind the scenes:
CREATE SEQUENCE products_id_seq
  START WITH 1 INCREMENT BY 1;

CREATE TABLE products (
  id   INTEGER NOT NULL DEFAULT nextval('products_id_seq') PRIMARY KEY,
  name TEXT NOT NULL
);

-- You can see the sequence PostgreSQL created
\ds

SERIAL vs BIGSERIAL vs SMALLSERIAL

PostgreSQL has three sizes of serial. The difference is only in the underlying integer type — and therefore how high the auto-increment counter can go before running out of numbers.

For most tables in most applications, SERIAL is perfectly fine — 2.1 billion rows is a very high ceiling. Use BIGSERIAL for tables that will accumulate millions of rows quickly, like event logs, analytics events, or transaction records.

Identity Columns — The Modern Approach

PostgreSQL 10 introduced identity columns as the SQL-standard replacement for SERIAL. They are more explicit, more predictable, and give you finer control over whether users can override the generated value. The PostgreSQL documentation recommends using identity columns in new projects going forward.

-- GENERATED ALWAYS AS IDENTITY — PostgreSQL always generates the value
-- User cannot manually insert or override the ID
CREATE TABLE customers (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name VARCHAR(50)  NOT NULL,
  email      VARCHAR(100) NOT NULL UNIQUE
);

-- GENERATED BY DEFAULT AS IDENTITY — PostgreSQL generates by default
-- But user CAN override with a manual value if needed
CREATE TABLE products (
  id       INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  price    NUMERIC(10,2) NOT NULL
);

-- Insert without specifying id — auto generated
INSERT INTO customers (first_name, email)
VALUES ('Alice', 'alice@example.com');

SELECT * FROM customers;

ALWAYS vs BY DEFAULT

GENERATED ALWAYS AS IDENTITY is strict — if you try to manually insert a value into the ID column, PostgreSQL rejects it. This is the safest choice when you want to guarantee that IDs are always system-generated and never tampered with manually. GENERATED BY DEFAULT AS IDENTITY is more flexible — PostgreSQL generates the ID by default but you can override it with a specific value when needed. This is useful during data migrations where you need to preserve original IDs from another system.

-- ALWAYS — trying to insert a manual ID fails
INSERT INTO customers (id, first_name, email)
VALUES (99, 'Bob', 'bob@example.com');   -- error

-- Override ALWAYS with OVERRIDING SYSTEM VALUE when truly needed
INSERT INTO customers (id, first_name, email)
OVERRIDING SYSTEM VALUE
VALUES (99, 'Bob', 'bob@example.com');   -- works

-- BY DEFAULT — manual ID works without any special syntax
INSERT INTO products (id, name, price)
VALUES (999, 'Special Import', 99.99);   -- works fine

Sequences — Working with Them Directly

A sequence is a standalone database object that generates a series of numbers. You can create sequences manually and use them independently of any table — for example to share a single number series across multiple tables, or to generate reference numbers for invoices and order confirmations.

-- Create a sequence manually
CREATE SEQUENCE invoice_number_seq
  START WITH 10000     -- first number generated
  INCREMENT BY 1       -- add 1 each time
  MINVALUE 10000       -- lowest allowed value
  MAXVALUE 9999999     -- highest allowed value
  NO CYCLE;            -- stop and error when maxvalue is reached (CYCLE would restart)

-- Get the next value from the sequence
SELECT nextval('invoice_number_seq');   -- returns 10000
SELECT nextval('invoice_number_seq');   -- returns 10001

-- See the current value without advancing the sequence
SELECT currval('invoice_number_seq');   -- returns 10001

-- Set the sequence to a specific value
SELECT setval('invoice_number_seq', 20000);   -- next call returns 20001

-- Use a sequence as a column default
CREATE TABLE invoices (
  id           INTEGER DEFAULT nextval('invoice_number_seq') PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total_amount NUMERIC(10,2)
);

Resetting a Sequence

A common need is resetting a sequence after a TRUNCATE — so that IDs start from 1 again. You can do this with setval or with ALTER SEQUENCE.

-- Truncate and reset manually using setval
TRUNCATE TABLE customers;
SELECT setval('customers_id_seq', 1, false);   -- false means NEXT call returns 1

-- Or use ALTER SEQUENCE to restart
ALTER SEQUENCE customers_id_seq RESTART WITH 1;

-- For identity columns use TRUNCATE with RESTART IDENTITY
TRUNCATE TABLE customers RESTART IDENTITY;

-- Check a sequence's current state
SELECT * FROM customers_id_seq;

Gaps in Sequences — Why They Happen and Why That Is Fine

You will notice that auto-increment IDs are sometimes not perfectly consecutive — you might see 1, 2, 3, 7, 8 with 4, 5, 6 missing. This is completely normal and expected behaviour. Sequences advance even when a transaction is rolled back. If an INSERT fails or is rolled back, the sequence number it consumed is gone — sequences are designed this way deliberately for performance and concurrency reasons.

The key point is that IDs are meant to be unique identifiers, not sequential numbering with no gaps. Never write application logic that assumes consecutive IDs or counts on there being no gaps. If you need gapless sequential numbering — like for legal invoice numbering — that requires a different approach using a dedicated counter table inside a transaction.

Altering and Dropping a Sequence

-- Change sequence properties
ALTER SEQUENCE invoice_number_seq
  INCREMENT BY 5        -- now increments by 5 each time
  MAXVALUE 99999999;    -- raise the ceiling

-- Rename a sequence
ALTER SEQUENCE invoice_number_seq RENAME TO inv_num_seq;

-- Drop a sequence (only if no column depends on it)
DROP SEQUENCE inv_num_seq;

-- Drop with CASCADE if a column default uses it
DROP SEQUENCE inv_num_seq CASCADE;

SERIAL vs Identity Columns — Which to Use

Next up: Inserting data into your tables — every form of INSERT you will use in real projects.