Sequences, SERIAL, and GENERATED IDENTITY Columns

Most tables need a column that auto-generates a unique, incrementing number for each new row — a surrogate primary key. PostgreSQL implements this using sequence objects: standalone counters that hand out the next number on demand, independent of any table.

Over the years PostgreSQL has offered two ways to attach a sequence to a column: the older SERIAL pseudo-type, and the SQL-standard GENERATED AS IDENTITY syntax introduced in PostgreSQL 10. Modern code should prefer identity columns, but you will see SERIAL constantly in existing codebases and tutorials.

Why it matters

Almost every table needs an id that the database assigns by itself. PostgreSQL has an older way of doing this, SERIAL, and a newer standard one, identity columns. Existing schemas are full of the first and new ones should use the second, so you need to recognise both and know how they differ.

SERIAL and BIGSERIAL (Legacy)

SERIAL is shorthand that PostgreSQL expands, behind the scenes, into an INTEGER column, a backing sequence (e.g. tablename_columnname_seq), and a DEFAULT nextval('tablename_columnname_seq'). BIGSERIAL does the same with BIGINT. It works, but it is not a true type — it is a convenience macro, and because the sequence is a separate object, ownership and dependency management can get confusing (for example, dumping/restoring or copying schemas can lose the link between column and sequence if done manually).

GENERATED ... AS IDENTITY (Modern)

The SQL-standard approach declares intent explicitly: id INTEGER GENERATED ALWAYS AS IDENTITY or GENERATED BY DEFAULT AS IDENTITY. ALWAYS means the database controls the value and rejects an explicit INSERT into that column unless you use OVERRIDING SYSTEM VALUE; BY DEFAULT allows an explicit value to be provided, falling back to the sequence otherwise. Identity columns are the recommended modern approach because they are properly tracked as part of the column's definition by PostgreSQL's catalog and tooling, rather than being an implicit side effect.

Working with the Underlying Sequence

Whichever syntax you use, you can inspect and control the sequence directly: currval() returns the last value obtained in the current session, nextval() advances and returns the next value, and setval() resets the counter (useful after bulk-loading data with explicit IDs). Sequences are also useful standalone, unattached to any column, whenever you need a globally increasing counter.

Example

-- Legacy style
CREATE TABLE legacy_customers (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Modern, SQL-standard style
CREATE TABLE customers (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO customers (name) VALUES ('Meera Nair'), ('Aditya Rao');
SELECT * FROM customers;

CREATE TABLE
CREATE TABLE
INSERT 0 2
 id |    name
----+-------------
  1 | Meera Nair
  2 | Aditya Rao

Exercise

This exercise uses PostgreSQL identity columns, so it needs a PostgreSQL server. Create a customers table whose id is a BIGINT identity column that the database always generates, and which is the primary key. Insert two customers without giving an id, returning the id each time.

Expected result: the two inserts return 1 and 2. An insert that supplies its own id would be rejected.

-- TODO: customers (id generated always as identity, name)

-- TODO: insert 'Asha' and return the generated id

-- TODO: insert 'Ravi' and return the generated id

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

INSERT INTO customers (name) VALUES ('Asha') RETURNING id;

INSERT INTO customers (name) VALUES ('Ravi') RETURNING id;

Quick quiz

Interview questions