Beginner LevelPostgreSQL
What is the difference between SERIAL and GENERATED ALWAYS AS IDENTITY columns?
❓ Interview Prompt: Compare legacy SERIAL (auto-created sequence table) vs modern GENERATED ALWAYS AS IDENTITY (ANSI SQL compliant).
PostgreSQL provides two approaches for auto-incrementing primary keys:
1. 'SERIAL' (Legacy):
Creates an implicit sequence table in the background. Does NOT prevent users from accidentally inserting explicit values or bypassing the sequence.
2. 'GENERATED ALWAYS AS IDENTITY' (Recommended - Postgres 10+):
Follows ANSI SQL standards. Enforces sequence incrementation and throws an error if an explicit ID insertion is attempted without 'OVERRIDING SYSTEM VALUE'!
Code Implementation Example
sql
-- Modern ANSI SQL Identity column (Recommended)
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title TEXT NOT NULL
);💡Defining auto-incrementing identity columns using GENERATED ALWAYS AS IDENTITY.
🎯 Interview Key Takeaway
Use GENERATED ALWAYS AS IDENTITY instead of SERIAL for SQL-standard, safe auto-incrementing columns.
#IDENTITY#SERIAL#Sequences#Beginner
Back to all PostgreSQL Questions→