SERIAL / GENERATED ALWAYS AS IDENTITY

Level 2 — Core Data Types & Constraints The column definitions in PostgreSQL used to automatically generate sequential, unique integer values (like 1, 2, 3) for primary key columns when new rows are inserted.

1. Prerequisites

• INTEGER / BIGINT / SMALLINT — The numeric types assigned to identity columns.

2. Term Category

Constraint (Auto-Incrementing Sequence Specifier): GENERATED ALWAYS AS IDENTITY (and legacy SERIAL) links a column to an auto-incrementing sequence object for surrogate primary key generation.

3. Explanation

Environment Context

• PostgreSQL Core (Internally creates a background database object called a Sequence (pg_class sequence type) to manage the increments atomically).

(1) Design Motivation — "Why did we design this?"

Every row in a database table needs a unique identifier (Primary Key) so you can reference it without confusion.

If your application had to generate these IDs manually:

1. Race Conditions: If two users register at the same millisecond, both application threads might query SELECT MAX(id) FROM users, see 100, add 1, and attempt to write 101 for both users, causing a crash.
2. Performance overhead: Querying the database to find the maximum ID before every single insert adds extra work.

To solve this, database designers created auto-incrementing integer systems managed directly by the database engine. In PostgreSQL, there are two ways to write this: the legacy SERIAL shorthand and the modern standard IDENTITY syntax.

(2) The Two Approaches

1. Legacy: SERIAL (Postgres Shorthand)

SERIAL is not a real data type. It is a macro shortcut. When you define a column as SERIAL, Postgres silently:

1. Creates an independent sequence generator object.
2. Sets the column type to INTEGER.
3. Sets the column's default value to fetch the next number from that sequence (nextval()).

/* Legacy Shorthand */
CREATE TABLE old_users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50)
);

2. Modern Standard: GENERATED ALWAYS AS IDENTITY

Introduced in SQL:2003 and supported in modern Postgres (version 10+), this is the official SQL standard approach. It binds the sequence directly to the column, making it cleaner and safer.

• GENERATED ALWAYS: Prevents users from manually inserting their own ID values (which would put the sequence out of sync!).
• GENERATED BY DEFAULT: Generates IDs automatically but allows manual overrides if needed (useful for importing data).

/* Modern Standard */
CREATE TABLE new_users (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(50)
);

(3) Reality Metaphor

Imagine a busy deli counter:

• Customers need unique sequence numbers to know their turn.
• Instead of customers trying to guess the next number, the shop installs a ticket dispenser machine on the wall.
• When a customer arrives (row is inserted), they pull a ticket, and the machine automatically feeds them the next number in order (101, 102, 103).
• If you use GENERATED ALWAYS, the security guard prevents customers from writing their own numbers on scrap paper, keeping the line clean.

(4) Code Examples

Inserting into Identity Tables

You simply omit the identity column from your insert parameters; Postgres handles it:

CREATE TABLE items (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(100)
);

-- Insert rows without specifying the ID!
INSERT INTO items (title) VALUES ('Keyboard'), ('Mouse');

SELECT * FROM items;
-- Output:
-- id |  title
-- ---+----------
-- 1  | Keyboard
-- 2  | Mouse

Manual Insert Block Failure

If you use GENERATED ALWAYS, attempting to override the sequence fails:

-- This query crashes!
INSERT INTO items (id, title) VALUES (99, 'Monitor');
-- ERROR: cannot insert into column "id"
-- DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.

4. Common Mistakes & Pitfalls

Mistake 1: Manual overrides on SERIAL tables causing future duplicate key crashes

The mistake: Manually inserting a row with a custom ID (like id = 50) into a table using SERIAL, and then attempting to run standard default inserts later.

Why it's wrong: The legacy SERIAL sequence counter does not monitor what values you write manually. If the sequence is currently at 5, and you insert id = 6 manually, the next default insert will fetch 6 from the sequence, clash with your manual entry, and crash with a duplicate key value violates unique constraint error.

Fix: Avoid manual overrides on auto-incrementing columns. If you must use overrides (like in data migration scripts), use GENERATED BY DEFAULT AS IDENTITY and reset the sequence afterward.

Mistake 2: Using Legacy SERIAL Instead of Modern SQL Standard GENERATED ALWAYS AS IDENTITY

The mistake: Using SERIAL in new PostgreSQL 10+ database schemas.

Why it's wrong: Legacy SERIAL creates an independent sequence object with loose table permissions. SQL standard GENERATED ALWAYS AS IDENTITY prevents manual insertion overrides and manages sequences cleanly.

Incorrect:

id SERIAL PRIMARY KEY -- Legacy Postgres extension

Fix:

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY -- Standard SQL identity

Mistake 3: Overriding GENERATED ALWAYS AS IDENTITY Values Without OVERRIDING SYSTEM VALUE

The mistake: Executing INSERT INTO users (id, name) VALUES (10, 'Alice'); on GENERATED ALWAYS AS IDENTITY column.

Why it's wrong: GENERATED ALWAYS rejects manual primary key inputs unless OVERRIDING SYSTEM VALUE is specified.

Incorrect:

INSERT INTO users (id, name) VALUES (10, 'Alice'); -- ❌ Error: cannot insert into column id!

Fix:

INSERT INTO users (id, name) OVERRIDING SYSTEM VALUE VALUES (10, 'Alice');

5. Practice Exercises

Exercise 1: Using Identity Columns (GENERATED ALWAYS AS IDENTITY)

Scenario: Create a users table with an identity column that prevents manual override inserts unless explicit override flags are supplied.

Requirements:

1. Use id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY.

CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username TEXT NOT NULL
);

-- Insert relying on identity generation
INSERT INTO users (username) VALUES ('alice');

Exercise 2: Allowing Manual Overrides with GENERATED BY DEFAULT AS IDENTITY

Scenario: Create a table allowing client applications to supply custom primary keys during bulk data imports while auto-generating keys otherwise.

Requirements:

1. Use id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY.

CREATE TABLE legacy_imports (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  data_payload TEXT NOT NULL
);

-- Manual ID insert succeeds without errors!
INSERT INTO legacy_imports (id, data_payload) VALUES (999, 'Custom Key');

Exercise 3: Inspecting and Resetting Sequence Counters

Scenario: Reset an identity sequence counter to match the maximum existing id value after a bulk import.

Requirements:

1. Execute SELECT setval(pg_get_serial_sequence('users', 'id'), MAX(id)) FROM users.

SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE(MAX(id), 1)
) FROM users;

6. Related Terms

• INTEGER / BIGINT / SMALLINT — The underlying types.
• PRIMARY KEY — The constraint typically bound to identity columns.

7. Key Takeaways

• Auto-increment columns generate sequential unique values for rows.
• SERIAL is a legacy, non-standard PostgreSQL-specific shorthand macro.
• GENERATED ALWAYS AS IDENTITY is the modern ANSI-SQL standard.
• GENERATED ALWAYS protects table integrity by blocking manual ID overrides.
• Use BIGINT for identity columns on tables expected to grow beyond 2 billion rows.