How to Change Column Type in PostgreSQL

How to Change Column Type in PostgreSQL

Use ALTER TABLE ... ALTER COLUMN ... TYPE to convert a column to a different data type without recreating the table.

Why would you change a column type?

Schema evolution, precision needs, or performance tuning often require switching from smaller to larger numeric types, from text to JSONB, or from VARCHAR to TEXT. ALTER COLUMN TYPE handles these migrations in-place.

What is the exact command?

Run ALTER TABLE <table> ALTER COLUMN <column> TYPE <new_type> [USING expression] [CASCADE]; see full syntax below.

How do I handle incompatible data?

Provide a USING clause to transform existing values. For example, USING price::numeric(12,2) converts text price values safely.

Can I change a primary-key column?

Yes, but the column is rewritten, and all referencing foreign keys are checked. Use CASCADE to update dependent objects automatically.

Example: convert integer to bigint

ALTER TABLE sales
ALTER COLUMN order_id TYPE bigint;

The table rewrites only if a physical storage change is required. PostgreSQL locks the table for writes during the rewrite.

Example: text to JSONB with USING

ALTER TABLE events
ALTER COLUMN payload TYPE jsonb
USING payload::jsonb;

Always test the USING expression on a subset of rows to confirm it parses correctly.

Best practices for ALTER COLUMN TYPE

• Create a backup before large rewrites.
• Run in maintenance windows for big tables.
• Use USING to avoid runtime cast errors.
• Add indexes after the type change to prevent long rebuilds.

What happens to indexes and constraints?

PostgreSQL drops and recreates indexes on the altered column automatically. Check index size afterwards to ensure optimal performance.

How to revert if something fails?

Use a transactional block: BEGIN; ALTER TABLE ...; ROLLBACK; test inside a transaction on staging. If committed already, restore from backup or run another ALTER to the old type.