# PostgreSQL serial vs identity: Which to Use, and How to Convert Old serial Columns

> Use `GENERATED ALWAYS AS IDENTITY` for new tables. `serial` is a shortcut for a separate sequence plus a default, so the column and its counter drift apart: an explicit id breaks the next insert, a widened key still stops at 2,147,483,647, and a copied table shares the counter. An existing `serial` key converts to identity in a few milliseconds, with no table rewrite.

Source: https://schemity.com/blog/postgres-serial-vs-identity/

For a new PostgreSQL table, use an identity column: `id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY`. `serial` still works, but it is not a type. It is a shortcut that creates a separate sequence and a column default, and the two can drift apart in ways an identity column does not allow.

Many production schemas still have `serial` keys, because the frameworks that created them used it, and two of the big ones still do. Below are the five ways `serial` misbehaves, each run on PostgreSQL 18.3 in a throwaway container, and the conversion, which is cheaper than most people expect.

## What serial actually creates

The [PostgreSQL manual](https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL) says the serial types "are not true types, but merely a notational convenience". `id serial` becomes three things: a sequence `users_id_seq AS integer`, a column `id integer NOT NULL DEFAULT nextval('users_id_seq')`, and an `OWNED BY` link so the sequence is dropped with the column. Identity columns arrived in [PostgreSQL 10](https://www.postgresql.org/docs/release/10.0/), described in the release notes as "similar to `SERIAL` columns, but are SQL standard compliant".

The framework you use decides which one you have:

| Framework | What it creates for an auto-increment key on PostgreSQL |
| --- | --- |
| Django 4.1 and later | Identity column, `GENERATED BY DEFAULT`. The [4.1 release notes](https://docs.djangoproject.com/en/5.2/releases/4.1/): `AutoField`, `BigAutoField` and `SmallAutoField` "are now created as identity columns rather than serial columns with sequences" |
| Rails (Active Record) | `bigserial primary key`, the PostgreSQL adapter's default primary key type in [the current source](https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb) |
| Prisma | `SERIAL`, `SMALLSERIAL` or `BIGSERIAL` for `@default(autoincrement())`, in the [Postgres renderer](https://github.com/prisma/prisma-engines/blob/main/schema-engine/connectors/sql-schema-connector/src/flavour/postgres/renderer.rs) of its migration engine |

`\d users` tells you which one a table has. A `serial` key shows `nextval('users_id_seq'::regclass)` as its default. An identity key shows `generated always as identity` or `generated by default as identity`.

## Serial vs identity in Postgres: which should you use?

Identity, for every new table. The differences only show up when someone does something slightly unusual, which is why `serial` lasts so long in old schemas. Each row below was reproduced on 18.3:

| What happens | `serial` | `GENERATED ALWAYS AS IDENTITY` |
| --- | --- | --- |
| An `INSERT` supplies `id = 1` | Accepted. The next generated id is also 1 and fails with `duplicate key value violates unique constraint "users_pkey"` | Refused: `cannot insert a non-DEFAULT value into column "id"`, with the hint `Use OVERRIDING SYSTEM VALUE to override` |
| An app role has `INSERT` on the table only | `permission denied for sequence users_id_seq` | The insert works |
| `CREATE TABLE copy (LIKE users INCLUDING ALL)` | The copy's default calls `users_id_seq`, so both tables draw from one counter | The copy gets its own sequence |
| The key is widened to `bigint` | The sequence stays `AS integer` and fails at 2,147,483,647 | The sequence becomes `bigint` with the column |
| Someone tries to remove the default | `DROP DEFAULT` works and the column stops numbering | Refused: `Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead` |

`GENERATED BY DEFAULT AS IDENTITY` is the in-between form. It fixes the permissions, copy and widening rows, but accepts a supplied id the way `serial` does, and the same `duplicate key value` error came back in the same test. Use `ALWAYS` unless a loader must write ids, and when one must, `INSERT ... OVERRIDING SYSTEM VALUE` says so in the statement. `pg_dump` handles it already: its `--inserts` output writes `INSERT INTO ... OVERRIDING SYSTEM VALUE VALUES (...)`, and its default `COPY` output loads explicit ids into an `ALWAYS` column without complaint.

## The serial overflow that survives the bigint migration

The widening row is the one that costs an outage. A table outgrows `integer`, someone runs the usual schema migration, `ALTER TABLE users ALTER COLUMN id TYPE bigint`, and the column now holds values up to 9,223,372,036,854,775,807. The sequence does not. `serial` created it `AS integer`, the `ALTER` did not touch it, and on 18.3 the next insert after 2,147,483,647 failed with:

```
ERROR:  nextval: reached maximum value of sequence "users_id_seq" (2147483647)
```

The fix is one more statement, `ALTER SEQUENCE users_id_seq AS bigint`, and it is easy to miss because `\d users` shows `bigint` and looks done. An identity column needs nothing: after the same `ALTER`, `pg_sequences` showed its sequence as `bigint` with a maximum of 9,223,372,036,854,775,807, and the insert at 2,147,483,648 succeeded.

The column change itself is the expensive part either way. `integer` to `bigint` rewrites the table: 761 ms for 1,000,000 rows here, under an `ACCESS EXCLUSIVE` lock, and every foreign key column pointing at it needs the same change before ids pass 2,147,483,647. If a view reads the key, PostgreSQL refuses the `ALTER` outright until [the view is dropped and created again](https://schemity.com/blog/postgres-alter-column-type-used-by-view/).

## A foreign key declared as serial fills itself in

The same shortcut causes a quieter bug in child tables. Copy the parent's column type into a foreign key and you get this:

```sql
CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id serial REFERENCES users (id),
    note text
);
```

`user_id` now has its own sequence and a `nextval` default. An `INSERT INTO orders (note) VALUES ('forgot user_id')` should fail on the `NOT NULL` that `serial` implies. Instead it succeeded and returned `user_id = 1`, and the foreign key passed because user 1 exists. Each later insert that forgets the column attaches the order to user 2, then 3, until it reaches an id with no user and the foreign key finally fails. A foreign key column takes the plain type under the parent's key, `integer` for `serial` and `bigint` for `bigserial`, with `NOT NULL` when the relationship is mandatory. Never the serial itself.

## How do you convert a serial column to identity?

Without rewriting the table. The conversion swaps the default for an identity property and carries the old counter over, so it touches the catalogue, not the rows. Here on an `invoices` table created with `id serial`:

```sql
BEGIN;
ALTER TABLE invoices ALTER COLUMN id DROP DEFAULT;
ALTER SEQUENCE invoices_id_seq RENAME TO invoices_id_seq_old;
ALTER TABLE invoices ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;
SELECT setval('invoices_id_seq', last_value, is_called) FROM invoices_id_seq_old;
DROP SEQUENCE invoices_id_seq_old;
COMMIT;
```

On a 1,000,000-row table whose top ten rows had been deleted, the transaction took about 3 ms, the table's data file was the same one before and after, and the next insert got `id = 1000001`. Copying `last_value` rather than `max(id)` is deliberate: `max(id)` would hand out the deleted ids 999,991 to 1,000,000 again, and anything that still refers to them, a log line, an export, another system, would now point at a new row. The `setval` names the new sequence directly, because `pg_get_serial_sequence` can still return the renamed old one, which stays owned by the column until it is dropped. The new sequence takes the old name, `invoices_id_seq`.

Four things to check first:

- **The lock.** The transaction holds `ACCESS EXCLUSIVE` on the table. It is short, but it waits behind every running query, and everything else waits behind it. Set `lock_timeout` so a long report cannot turn a 3 ms change into a queue.
- **A shared sequence.** If a `LIKE ... INCLUDING ALL` copy or another table also calls the sequence, `DROP SEQUENCE` fails with `cannot drop sequence invoices_id_seq_old because other objects depend on it`, its `DETAIL` names every default using it, and the transaction rolls back. That is how you find the copies.
- **Grants on the sequence.** They are dropped with the old sequence. Inserts no longer need them, but a role that calls `currval('invoices_id_seq')` gets `permission denied for sequence invoices_id_seq` until you grant it again. `INSERT ... RETURNING id` avoids `currval` altogether.
- **Writers that supply ids.** Seed scripts and fixtures that insert explicit ids fail against `ALWAYS`. Add `OVERRIDING SYSTEM VALUE` to them, or convert to `BY DEFAULT` instead.

Converting does not change the column's type, so an `integer` key converted to identity still overflows at 2,147,483,647. If the table is heading there, widen it separately, and plan for the rewrite.

## How Schemity handles serial and identity keys

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. When you [connect to PostgreSQL](https://schemity.com/doc/connect-postgresql/), each column's default is drawn on the canvas, so every `serial` column shows `nextval` beside it and an identity column shows nothing. That makes the legacy keys easy to see, and the bug above easier still: a `nextval` on a foreign key column is a `serial` that should have been an `integer`.

![Schemity's canvas reading the orders and users tables from PostgreSQL: users.id is INTEGER with the default nextval, orders.user_id is an INTEGER foreign key that also shows nextval, orders.id is a BIGINT identity key with no default shown, and orders.note is TEXT with a NULL default and the N marker for a nullable column](https://schemity.com/images/blog/postgres-serial-vs-identity/canvas-serial-defaults.webp)

When you design the next one, a table drawn with a single integer primary key and no default is created as an identity column. The planned SQL reads `"id" INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY`, or `BIGINT` with a `bigint` key. Drawing a [relationship](https://schemity.com/doc/relationships/) never copies the parent key's default into the new foreign key column, and a parent typed `SERIAL` or `BIGSERIAL` in a design gives the column `INTEGER` or `BIGINT`, so the diagram does not reproduce the self-filling foreign key.

![Schemity's canvas with a new contacts table drafted beside the live orders and users tables, drawn with a dashed border because it is not yet in the database: the relation from users to contacts gave contacts.user_id the type INTEGER with no default and no N marker, while orders.user_id, read from the database, still shows INTEGER with nextval](https://schemity.com/images/blog/postgres-serial-vs-identity/relation-from-serial-parent.webp)

Widening a `serial` key in the diagram from `INTEGER` to `BIGINT` plans the `ALTER COLUMN ... TYPE BIGINT` and, after it, `ALTER SEQUENCE users_id_seq AS BIGINT`, so the counter is widened in the same migration as the column. [Impact analysis](https://schemity.com/doc/impact-analysis/) reports the change before anything runs: the table is rewritten, reads and writes are held back while it happens, and the tables whose foreign keys reference the key are listed, since their columns need widening too. Until they are, [lint](https://schemity.com/doc/schema-lint/) reports each one as "Foreign key type does not match the column it references".

![Schemity's Findings view after users.id was changed from INTEGER to BIGINT: on the canvas users.id reads BIGINT with its nextval default kept, and contacts is a new table; the drawer lists the planned changes Table contacts created and Column users.id altered, lint findings that contacts.user_id and orders.user_id no longer match the type they reference, and the impact Rewrites users to change id: ~50K rows, 3.2 MB on disk, holds back reads and writes to users while it reads them, and Changing users.id reaches 2 entities, orders and contacts](https://schemity.com/images/blog/postgres-serial-vs-identity/impact-widen-serial.webp)

## Which to use, in one list

- New table: `bigint GENERATED ALWAYS AS IDENTITY`.
- A loader must write ids: `GENERATED BY DEFAULT AS IDENTITY`, or keep `ALWAYS` and use `OVERRIDING SYSTEM VALUE` in the loader.
- Existing `serial` key: convert it in one short transaction, after checking for shared sequences and scripts that insert ids.
- Widening a `serial` key to `bigint`: widen the sequence too, or convert to identity first.
- Foreign key column: the plain integer type under the parent key, `NOT NULL` when the relationship is mandatory, never `serial`.

Whether the key should be a number at all is the question in [UUID vs bigint primary keys in Postgres](https://schemity.com/blog/postgres-uuid-vs-bigint-primary-key/). What a new `NOT NULL` column with a volatile default costs on a large table is in [how to add a NOT NULL column to a large table](https://schemity.com/blog/postgres-add-not-null-column-large-table/).

## Frequently asked questions

### Should I use serial or identity in PostgreSQL?

Use an identity column, `GENERATED ALWAYS AS IDENTITY`, for any new table. It has been in PostgreSQL since version 10, it is the SQL standard form, and its sequence belongs to the column, so permissions, table copies and type changes follow it. Keep `serial` only where a tool you cannot change still generates it.

### How do I convert a serial column to an identity column?

In one transaction: drop the column default, rename the old sequence, add `GENERATED ALWAYS AS IDENTITY`, copy the old sequence's `last_value` into the new one with `setval`, and drop the old sequence. On a 1,000,000-row table it took about 3 ms and did not rewrite the table. It takes an `ACCESS EXCLUSIVE` lock, so it waits behind running queries, and grants on the old sequence have to be given again on the new one.

### What is the difference between `GENERATED ALWAYS` and `GENERATED BY DEFAULT`?

`GENERATED ALWAYS` refuses an `INSERT` that supplies its own id unless the statement says `OVERRIDING SYSTEM VALUE`, and refuses an `UPDATE` that changes the id. `GENERATED BY DEFAULT` accepts the supplied id, which brings back the main `serial` problem: the sequence does not know about that id and the next generated one collides with it. Choose `BY DEFAULT` only when a loader really must write ids.