🌱BeginnerConstraints
SERIAL and IDENTITY (Auto-increment)
Generate auto-incrementing integer primary keys using SERIAL or GENERATED ALWAYS AS IDENTITY.
Overview
Auto-incrementing columns generate a new unique integer value automatically for each inserted row. PostgreSQL has two mechanisms: the legacy SERIAL pseudo-types (SMALLSERIAL, SERIAL, BIGSERIAL) and the SQL-standard GENERATED AS IDENTITY syntax added in PostgreSQL 10.
Both use sequences under the hood. The main difference: IDENTITY columns are more standards-compliant and give you tighter control GENERATED ALWAYS AS IDENTITY prevents manual insertion of values (protecting the sequence), while GENERATED BY DEFAULT AS IDENTITY allows overriding with explicit values when needed. New code should prefer IDENTITY over SERIAL.
Key Points
- •SERIAL = SMALLINT SERIAL, SERIAL (int4), BIGSERIAL (int8) legacy, still widely used
- •GENERATED ALWAYS AS IDENTITY SQL standard, preferred in new code
- •GENERATED BY DEFAULT AS IDENTITY allows explicit override
- •Both use a sequence under the hood (nextval)
- •Get the inserted id: INSERT ... RETURNING id
- •BIGSERIAL or BIGINT IDENTITY for tables expected to have billions of rows
Examples
SERIAL vs IDENTITY
-- Legacy SERIAL approach
CREATE TABLE users_legacy (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL
);
-- SQL Standard IDENTITY (preferred)
CREATE TABLE users (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL
);
-- IDENTITY with explicit start and increment
CREATE TABLE order_numbers (
id INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1000 INCREMENT BY 1) PRIMARY KEY,
notes TEXT
);Get the generated id back
INSERT INTO users (email) VALUES ('alice@example.com') RETURNING id;
-- Returns: id = 1 (or whatever next value is)
-- Get current sequence value without inserting
SELECT lastval();Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4