SERIAL and IDENTITY Columns

Objectives

By the end of this lesson, you should be able to:

• Use SERIAL to create an auto-incrementing column
• Use GENERATED ... AS IDENTITY as the modern alternative
• Explain the difference between GENERATED ALWAYS and GENERATED BY DEFAULT

💡 Why this matters: A surrogate primary key (covered in the last two lessons) is only convenient if you don’t have to manually track the next number yourself. Both SERIAL and IDENTITY solve that, PostgreSQL assigns the next value automatically.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

SERIAL: The Classic Approach

id SERIAL PRIMARY KEY

SERIAL is really an INTEGER under the hood, backed by a sequence PostgreSQL creates automatically, that hands out the next number every time a row is inserted without id being specified:

INSERT INTO employees_identity (first_name) VALUES ('Erin'), ('Jordan');
SELECT * FROM employees_identity;

 id | first_name
----+-------------
  1 | Erin
  2 | Jordan

SERIAL has been the standard way to do this in PostgreSQL for a long time, and it still works fine, but it predates the SQL standard’s own way of expressing the same idea.

IDENTITY: The SQL-Standard Approach

id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

This does the same job as SERIAL, auto-incrementing on every insert, but using standard SQL syntax rather than a PostgreSQL-specific shortcut. GENERATED ALWAYS is strict, it refuses to let an INSERT specify a value for id directly:

INSERT INTO employees_identity (id, first_name) VALUES (99, 'Maya');

ERROR: cannot insert a non-DEFAULT value into column "id"

GENERATED BY DEFAULT AS IDENTITY is the more permissive version, it still auto-generates a value when none is given, but allows an explicit value to override it when needed (useful when migrating existing data with known ids, for example):

id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

INSERT INTO employees_identity2 (id, first_name) VALUES (99, 'Maya');

 id | first_name
----+-------------
 99 | Maya

Which One to Use

Both work, and both are common in real codebases, SERIAL shows up constantly in existing PostgreSQL projects, GENERATED ... AS IDENTITY is the more modern, standards-aligned choice, and generally recommended for new tables. This course uses SERIAL for brevity in most examples, but either is a correct answer.

Try It

1. Create a table with an id column using SERIAL PRIMARY KEY, insert two rows without specifying id, and confirm the values assigned.
2. Create a table with an id column using GENERATED ALWAYS AS IDENTITY, and confirm that trying to insert an explicit id value raises an error.
3. Explain, in your own words, the difference between GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY.

Recap

• SERIAL is PostgreSQL’s classic auto-incrementing integer type, backed by a sequence.
• GENERATED ALWAYS AS IDENTITY is the SQL-standard equivalent, and refuses an explicit value.
• GENERATED BY DEFAULT AS IDENTITY auto-generates a value by default, but still allows an explicit one when needed.

Next lesson: removing a table entirely with DROP TABLE, or just emptying it with TRUNCATE TABLE.