How to Change a Column Type in PostgreSQL

Postgres can skip the table rewrite for a few binary-compatible changes, such as increasing a varchar length limit. Most other changes, including int to bigint, rewrite every row and hold an ACCESS EXCLUSIVE lock for the duration, which blocks reads as well as writes. Check the table size before running this during business hours.

ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2);

With an explicit cast (USING)

USING supplies the expression Postgres should use to convert each existing value, and it is required whenever no implicit cast exists, which covers most interesting conversions. The expression can be any SQL, so you can clean data on the way through, for example USING NULLIF(trim(col), '')::date. If a single row fails to cast, the whole statement rolls back.

ALTER TABLE events ALTER COLUMN payload TYPE jsonb USING payload::jsonb;
ALTER TABLE users ALTER COLUMN signup_date TYPE date USING signup_date::date;

Errors you will hit

"column cannot be cast automatically" means Postgres needs a USING expression. "default for column cannot be cast automatically" means the column default is in the way: drop the default, change the type, then add the default back. And if a view depends on the column, the ALTER is refused outright; drop the view, change the type, recreate the view.

ALTER TABLE users ALTER COLUMN plan DROP DEFAULT;
ALTER TABLE users ALTER COLUMN plan TYPE text USING plan::text;
ALTER TABLE users ALTER COLUMN plan SET DEFAULT 'free';

Big tables without the long lock

When the rewrite would lock a large table for minutes, do it in stages: add a new column, backfill it in batches so no single transaction holds the lock, then swap the columns in one quick transaction. Each backfill batch only locks the rows it touches.

ALTER TABLE events ADD COLUMN user_id_big bigint;

-- repeat in batches until no rows remain
UPDATE events SET user_id_big = user_id
WHERE user_id_big IS NULL AND id BETWEEN 1 AND 100000;

BEGIN;
ALTER TABLE events DROP COLUMN user_id;
ALTER TABLE events RENAME COLUMN user_id_big TO user_id;
COMMIT;

Conversions that need no rewrite

A few changes are metadata-only and effectively instant even on huge tables: increasing a varchar length limit, varchar to text, and dropping the limit entirely. Since Postgres 12, adding a column with a constant default is also instant. When you only need a wider string, prefer these over anything that rewrites.

ALTER TABLE products ALTER COLUMN sku TYPE varchar(64);  -- was varchar(32): instant
ALTER TABLE products ALTER COLUMN notes TYPE text;       -- varchar to text: instant

When it rewrites the table and when it does not

A type change that needs new on-disk values rewrites the whole table under an ACCESS EXCLUSIVE lock, so nothing can read it until the rewrite finishes. A few changes skip the rewrite entirely: widening varchar(50) to varchar(100) or to text, and widening numeric precision without changing the scale. On a large table, prefer the no-rewrite path, or add a new column, backfill it in batches, and swap the names.

ALTER TABLE orders ALTER COLUMN status TYPE varchar(100);  -- no rewrite
ALTER TABLE orders ALTER COLUMN status TYPE text;          -- no rewrite

Common errors

SELECT total FROM orders WHERE total !~ '^-?[0-9]+(\.[0-9]+)?$';

ALTER TABLE orders
  ALTER COLUMN total TYPE numeric(12,2) USING total::numeric(12,2);

SELECT viewname, definition FROM pg_views WHERE definition ILIKE '%orders%';

DROP VIEW recent_orders;
ALTER TABLE orders ALTER COLUMN created_at TYPE timestamp;
CREATE VIEW recent_orders AS ...;

Do more with PostgreSQL

• Export Postgres to Google Sheets on a schedule
• Postgres SQL editor in the browser