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;