SERIAL vs IDENTITY columns

Both draw numbers from a sequence, but they are set up differently.

-- Older style
create table notices_old (id serial primary key, title text);

-- Modern, SQL-standard style (PostgreSQL 10+)
create table notices (
    id    bigint generated always as identity primary key,
    title text not null
);
insert into notices (title) values ('Exam schedule') returning id;

SERIAL is shorthand that creates a separate sequence and a DEFAULT nextval(...). Its drawbacks: anyone can insert explicit ids and push the column out of step with the sequence; permissions and ownership of the sequence must be managed separately; and copying the table structure can leave it pointing at the old sequence.

IDENTITY is part of the column definition and SQL-standard. GENERATED ALWAYS rejects manual values unless you write OVERRIDING SYSTEM VALUE; GENERATED BY DEFAULT allows them. Use bigint for tables that can grow large.

Either way, gaps are normal: a rolled-back insert still consumes a number, so never rely on ids being consecutive. Use RETURNING id to get the new key in the same statement.