PostgreSQL type decision
PostgreSQL SERIAL vs IDENTITY
Compare serial with SQL-standard generated identity columns and choose a default for new PostgreSQL tables.
Use generated identity columns for new tables. serial still works, but it is shorthand that creates a sequence and default rather than a property of the column.
- generated ... as identity
- New schemas and explicit control over generated values
- serial / bigserial
- Existing schemas and compatibility with older migrations
A concrete schema
create table orders (
id bigint generated always as identity primary key,
created_at timestamptz not null default now()
);When generated ... as identity is the better fit
New schemas and explicit control over generated values.
When serial / bigserial is the better fit
Existing schemas and compatibility with older migrations.
The tradeoff that matters
Identity columns are part of the SQL standard and express generation behavior in the column definition. GENERATED ALWAYS rejects manual values unless OVERRIDING SYSTEM VALUE is used; BY DEFAULT allows an explicit value.
A migration note
Changing a column type in a live database can rewrite the table, rebuild indexes, or break application assumptions. Inspect dependent foreign keys and run the conversion against production-sized data before scheduling the migration.
Questions that come up
Is generated ... as identity always better than serial / bigserial?
No. The choice follows the meaning of the data and the queries you need to run. A blanket rule hides the one requirement that should decide the column.
Can I change the type later?
Usually, but the migration may require an explicit USING expression and can lock or rewrite a large table. Test the exact ALTER TABLE statement on a recent copy first.
Where can I try the schema?
Use the PostgresGUI SQL editor for syntax work or the schema designer to model the table and export PostgreSQL SQL.