Postgres ALTER COLUMN TYPE Generator

Changing a column type in PostgreSQL is one statement, but whether it takes 5 milliseconds or locks a busy table for an hour depends entirely on the type pair. Pick the old and new type here and the generator writes the ALTER TABLE ... ALTER COLUMN ... TYPE statement with a correct USING expression, tells you whether PostgreSQL has to rewrite the whole table, and gives you a batched add-column / backfill / swap migration for the cases where a rewrite is not acceptable. It also emits queries that find dependent views, indexes and rows that would fail to convert. Runs entirely in your browser.

Table (schema.table)ColumnCurrent typeNew type

Table rewrite required — A USING expression changes the stored values, so PostgreSQL must rewrite every row and rebuild all indexes on the table.

USING expressionlock_timeout

NOT NULLNew DEFAULT (optional)Backfill batch size

Direct ALTER TABLE statement

-- text  ->  numeric(12,2)
-- Table rewrite: YES
-- A USING expression changes the stored values, so PostgreSQL must rewrite every row and rebuild all indexes on the table.

-- Never wait forever for the ACCESS EXCLUSIVE lock: fail fast and retry
SET lock_timeout = '3s';
BEGIN;
ALTER TABLE public.orders
  ALTER COLUMN amount TYPE numeric(12,2)
  USING NULLIF(btrim(amount), '')::numeric(12,2);
COMMIT;

-- The rewrite holds ACCESS EXCLUSIVE for the whole duration:
-- reads and writes on this table block until it finishes.
-- Dry-run the conversion first so a bad row does not abort the migration:
SELECT count(*) FROM public.orders WHERE amount IS NOT NULL AND NULLIF(btrim(amount), '')::numeric(12,2) IS NULL;
-- (rows that fail to convert; expect 0 before you run the ALTER)

Zero-downtime migration

-- Zero-downtime path: new column + backfill + swap in a short transaction
-- Use this when the direct ALTER would rewrite a large, busy table.

-- 1. Add the new column (instant, no default = no rewrite)
ALTER TABLE public.orders ADD COLUMN amount_new numeric(12,2);

-- 2. Keep it in sync for new writes
CREATE OR REPLACE FUNCTION orders_sync_amount() RETURNS trigger AS $$
BEGIN
  NEW.amount_new := NULLIF(btrim(NEW.amount), '')::numeric(12,2);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_sync_amount_trg
  BEFORE INSERT OR UPDATE OF amount ON public.orders
  FOR EACH ROW EXECUTE FUNCTION orders_sync_amount();

-- 3. Backfill in batches so no single transaction holds long locks
DO $$
DECLARE
  updated bigint;
BEGIN
  LOOP
    WITH batch AS (
      SELECT ctid FROM public.orders
      WHERE amount_new IS NULL AND amount IS NOT NULL
      LIMIT 10000
    )
    UPDATE public.orders t SET amount_new = NULLIF(btrim(amount), '')::numeric(12,2)
    FROM batch WHERE t.ctid = batch.ctid;
    GET DIAGNOSTICS updated = ROW_COUNT;
    EXIT WHEN updated = 0;
    COMMIT; -- PG 11+ inside a DO block
  END LOOP;
END $$;

-- 4. Swap: rename in one short transaction
SET lock_timeout = '3s';
BEGIN;
ALTER TABLE public.orders RENAME COLUMN amount TO amount_old;
ALTER TABLE public.orders RENAME COLUMN amount_new TO amount;
DROP TRIGGER orders_sync_amount_trg ON public.orders;
COMMIT;

-- 5. After the application is verified, drop the old column
-- ALTER TABLE public.orders DROP COLUMN amount_old;
-- DROP FUNCTION orders_sync_amount();

Pre-flight checks

-- Current type, nullability and default
SELECT column_name, data_type, character_maximum_length,
       numeric_precision, numeric_scale, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'orders'
  AND column_name = 'amount';

-- Objects that will block the ALTER: views, matviews and generated columns
SELECT dependent.relname AS depends_on_column, dependent.relkind
FROM pg_depend d
JOIN pg_rewrite r ON r.oid = d.objid
JOIN pg_class dependent ON dependent.oid = r.ev_class
JOIN pg_class source ON source.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = source.oid AND a.attnum = d.refobjsubid
WHERE source.oid = 'public.orders'::regclass
  AND a.attname = 'amount';
-- Drop and recreate those views around the migration.

-- Indexes and constraints that reference the column (all are rebuilt on rewrite)
SELECT indexname, indexdef FROM pg_indexes
WHERE schemaname || '.' || tablename = 'public.orders'
  AND indexdef ILIKE '%amount%';

-- Watch for blocked queries while the ALTER runs
SELECT pid, wait_event_type, state, left(query, 80) AS query
FROM pg_stat_activity WHERE wait_event_type = 'Lock';

-- Table size — anything above a few GB deserves the zero-downtime path
SELECT pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size;

Do more than postgres alter column type generator — 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

Enter the table and column, then choose the current type and the target type; the tool fills in a USING expression when a plain cast is not enough.
Read the rewrite verdict: NO means a catalog-only change, YES means every row is rewritten under an ACCESS EXCLUSIVE lock.
Copy the direct ALTER for small tables, or the zero-downtime script for large ones, and run the dependency and dry-run checks before you migrate.

Frequently asked questions

Does ALTER COLUMN TYPE rewrite the whole table in PostgreSQL?

Only when the new type is not binary coercible from the old one, or when a USING expression changes the stored values. Widening varchar(50) to varchar(200), converting varchar to text, or increasing numeric precision while keeping the scale are catalog-only changes that finish instantly. Converting text to integer, integer to bigint, or timestamp to timestamptz in a non-UTC session rewrites every row and rebuilds every index on the table while holding an ACCESS EXCLUSIVE lock, so reads and writes queue behind it.

When do I need a USING clause, and what should it contain?

PostgreSQL applies an assignment cast automatically when one exists between the two types; you need USING when there is no such cast or when the raw data needs cleaning first. Typical cases are text to numeric — where empty strings must become NULL, hence NULLIF(btrim(col), '')::numeric — text to boolean, where you map 'yes'/'no'/'1'/'0' explicitly, and timestamp to timestamptz, where col AT TIME ZONE 'UTC' states which timezone the naive values were in. Always run the dry-run count first: a single unconvertible row aborts the migration after all the work is done.

How do I change a column type without downtime on a large table?

Add a new nullable column of the target type, add a BEFORE INSERT OR UPDATE trigger that keeps it in sync with the old one, backfill existing rows in batches of a few thousand so each transaction is short, then rename both columns inside one short transaction guarded by SET lock_timeout so it fails fast instead of piling up locks. Drop the old column and the trigger once the application has been verified. This tool generates all five steps for you; Chat2DB, a free AI-powered SQL client, is a convenient place to run and monitor them — download at https://chat2db.ai/download or use https://app.chat2db.ai.