Postgres SERIAL to IDENTITY Converter

SERIAL is not a real type — it is shorthand that creates an integer column, a sequence and a default, three loosely connected objects that can drift apart. Identity columns, standard since PostgreSQL 10, replace all of it with one coherent definition. The conversion is only a handful of statements, but getting them wrong breaks inserts immediately: forget the setval and the very next INSERT collides with an existing row. This generator writes the full migration in the right order, keeps the sequence positioned correctly, adds the rollback, and can pair the change with an integer-to-bigint widening. Runs entirely in your browser.

DirectionIdentity kind

SchemaTableColumnCurrent type

Also widen to bigintDrop the legacy sequence

Detect what you have

-- What is this column actually backed by? Run this first.
SELECT a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS data_type,
       a.attidentity,   -- '' = none, 'd' = BY DEFAULT, 'a' = ALWAYS
       a.attgenerated,  -- 's' = generated/stored column
       pg_get_expr(d.adbin, d.adrelid) AS column_default,
       pg_get_serial_sequence('public.orders', a.attname) AS owned_sequence
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 'public.orders'::regclass
  AND a.attnum > 0 AND NOT a.attisdropped
  AND a.attname = 'id';

-- How close is an integer column to overflowing?
SELECT max(id) AS current_max,
       2147483647 AS int4_max,
       round(100.0 * max(id) / 2147483647, 2) AS pct_used
FROM public.orders;

Migration SQL

BEGIN;

-- 1. Detach the old sequence default. The sequence itself still exists and
--    still holds the current value.
ALTER TABLE public.orders
  ALTER COLUMN id DROP DEFAULT;

-- 2. Break the ownership link so dropping the sequence later is safe.
ALTER SEQUENCE public.orders_id_seq OWNED BY NONE;

-- 3. Attach a real identity column.
ALTER TABLE public.orders
  ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

-- 4. Fast-forward the NEW identity sequence past the existing rows,
--    otherwise the next INSERT collides with an existing primary key.
SELECT setval(pg_get_serial_sequence('public.orders', 'id'),
              (SELECT coalesce(max(id), 0) FROM public.orders) + 1,
              false);

-- 5. Drop the now-unused legacy sequence.
DROP SEQUENCE IF EXISTS public.orders_id_seq;

-- 6. Confirm before committing: attidentity should be
--    'd' and column_default should be NULL.
COMMIT;

Rollback (down migration)

BEGIN;

-- Turn the identity column back into a plain column with a sequence default.
ALTER TABLE public.orders
  ALTER COLUMN id DROP IDENTITY IF EXISTS;

CREATE SEQUENCE IF NOT EXISTS public.orders_id_seq AS integer;
SELECT setval('public.orders_id_seq',
              (SELECT coalesce(max(id), 0) FROM public.orders) + 1,
              false);

ALTER TABLE public.orders
  ALTER COLUMN id SET DEFAULT nextval('public.orders_id_seq'::regclass);
ALTER SEQUENCE public.orders_id_seq OWNED BY public.orders.id;

COMMIT;

Notes and warnings

1. Steps 1 to 5 take an ACCESS EXCLUSIVE lock on the table, but each is a catalogue-only change and completes in milliseconds. The risk is not the duration of the work, it is waiting for the lock: set lock_timeout (for example SET lock_timeout = '3s') so the migration fails fast instead of queueing behind a long transaction and blocking every reader behind it.

2. Step 4 is the step people forget. ADD GENERATED ... AS IDENTITY creates a brand-new sequence that starts at 1, so without the setval the next insert immediately fails with a duplicate key error on an existing row.

3. GENERATED BY DEFAULT behaves like SERIAL: callers may supply their own value. It is the safe drop-in choice, but note that supplying a value does not advance the sequence, so the same collision that SERIAL suffers is still possible.

4. Identity columns are the SQL-standard mechanism and are what PostgreSQL has recommended since version 10. Practical wins over SERIAL: the sequence cannot be dropped or altered independently by accident, permissions are handled with the table rather than granted separately on the sequence, and pg_dump emits a single coherent column definition.

5. Check your ORM before switching to ALWAYS. Recent Hibernate, Django, Rails, SQLAlchemy, Prisma and Drizzle all support identity columns, but some older versions still generate explicit id values on insert.

Do more than postgres serial to identity converter — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Run the detection query to confirm whether the column is really a SERIAL with an owned sequence or already an identity column.
Enter the schema, table and column, pick the direction and choose GENERATED BY DEFAULT for a drop-in swap or GENERATED ALWAYS to block explicit id writes.
Copy the migration into your migration tool, keep the rollback block for the down migration, and read the notes on locking before running it in production.

Frequently asked questions

What is the difference between SERIAL and GENERATED AS IDENTITY in Postgres?

SERIAL is a macro. Writing id SERIAL PRIMARY KEY creates an integer column, a separate sequence named table_id_seq, and a DEFAULT nextval(...) on the column. The three objects are only loosely linked, so the sequence can be altered, dropped or have its permissions changed independently of the table, and the column is really just an integer with a default. GENERATED AS IDENTITY is the SQL-standard equivalent: the sequence belongs to the column, is dumped and restored as part of the column definition, inherits the table's permissions, and cannot be detached by accident. Identity also offers GENERATED ALWAYS, which rejects inserts that supply the value explicitly — something SERIAL cannot enforce at all. PostgreSQL has recommended identity over SERIAL since version 10.

Why does my first INSERT fail after converting to an identity column?

Because ALTER COLUMN ... ADD GENERATED AS IDENTITY creates a brand-new sequence that starts at 1, and it has no idea your table already contains a million rows. The next insert therefore tries to use id 1, which already exists, and fails with a duplicate key violation on the primary key. The fix is the setval call in step 4 of the generated migration: it fast-forwards the new sequence to max(id) + 1 before anything writes to the table. Always run it inside the same transaction as the ALTER, so a failure cannot leave the table accepting writes with a sequence pointing at 1.

Does converting SERIAL to IDENTITY lock the table or rewrite the data?

No data is rewritten. Dropping the default, detaching the sequence and adding the identity property are all catalogue-only changes that finish in milliseconds, so the table is not rewritten and indexes are not rebuilt. They do each take an ACCESS EXCLUSIVE lock, however, which means the migration has to wait for every open transaction touching the table — and while it waits, new queries queue behind it. On a busy table set lock_timeout to a few seconds so the migration fails fast and retries rather than stalling traffic. The one exception is the optional integer-to-bigint widening, which does rewrite every row and rebuild every index. Chat2DB shows column defaults, identity properties and owned sequences together in its schema browser, so you can confirm the change landed correctly: download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.