Amazon Redshift does not support directly altering a column's data type with ALTER TABLE ... ALTER COLUMN ... TYPE. Instead, you perform a four-step workaround: add a new column, copy data with a cast, drop the old column, and rename the new one. This limitation exists because Redshift stores data in compressed, columnar format, and in-place type changes would require rewriting every block on disk.

Why Redshift Cannot Alter Column Types Directly

Traditional row-oriented databases like PostgreSQL can rewrite individual rows during an ALTER COLUMN TYPE operation. Redshift is a columnar, MPP (massively parallel processing) warehouse. Each column is stored independently, sorted and compressed per its encoding. Changing a column's type would mean decompressing, casting, re-encoding, and redistributing the entire column across all slices. Rather than hiding that cost behind a single DDL statement, Redshift forces you to perform the migration explicitly so you control the timing and transaction boundaries.

The Four-Step Column Type Migration

Here is the standard pattern wrapped in a transaction to ensure atomicity.

1BEGIN;
2
3-- Step 1: Add a new column with the target data type
4ALTER TABLE users ADD COLUMN user_id_new BIGINT;
5
6-- Step 2: Copy data with an explicit cast
7UPDATE users SET user_id_new = user_id::BIGINT;
8
9-- Step 3: Drop the original column
10ALTER TABLE users DROP COLUMN user_id;
11
12-- Step 4: Rename the new column to the original name
13ALTER TABLE users RENAME COLUMN user_id_new TO user_id;
14
15COMMIT;

Wrapping in BEGIN / COMMIT ensures that if any step fails, the table reverts to its original state.

Handling Columns with Constraints and Dependencies

Real production tables rarely have bare columns. Foreign keys, NOT NULL constraints, default values, sort keys, and distribution keys all need attention during migration.

Columns Referenced by Foreign Keys

Drop the foreign key on the referencing table before you drop the original column, then recreate it afterward.

1BEGIN;
2
3-- Remove FK from orders table
4ALTER TABLE orders DROP CONSTRAINT fk_orders_user;
5
6-- Perform the four-step migration on users.user_id
7ALTER TABLE users ADD COLUMN user_id_new BIGINT;
8UPDATE users SET user_id_new = user_id::BIGINT;
9ALTER TABLE users DROP COLUMN user_id;
10ALTER TABLE users RENAME COLUMN user_id_new TO user_id;
11
12-- Recreate the FK
13ALTER TABLE orders ADD CONSTRAINT fk_orders_user
14  FOREIGN KEY (user_id) REFERENCES users(user_id);
15
16COMMIT;

Columns That Are Sort Keys or Distribution Keys

Redshift does not allow you to drop a column that is the distribution key or part of the sort key. For these columns, you must use the deep-copy approach: create a new table with the desired schema, insert data, drop the old table, and rename.

1-- Create a new table with the corrected type
2CREATE TABLE users_new (
3  user_id   BIGINT    DISTKEY,
4  username  VARCHAR(100),
5  created_at TIMESTAMP DEFAULT GETDATE()
6)
7SORTKEY (created_at);
8
9-- Copy data
10INSERT INTO users_new SELECT user_id::BIGINT, username, created_at FROM users;
11
12-- Swap tables
13DROP TABLE users;
14ALTER TABLE users_new RENAME TO users;

Comparison of Migration Approaches

Preserving Column Order

The four-step approach appends the new column at the end of the table. If column order matters (for example, SELECT * in downstream pipelines), you can use CTAS to reorder.

1CREATE TABLE users_reordered AS
2SELECT
3  user_id_new AS user_id,
4  username,
5  email,
6  created_at
7FROM users;
8
9DROP TABLE users;
10ALTER TABLE users_reordered RENAME TO users;

Validating the Migration

Always verify that the data migrated correctly before dropping the original column or table.

1-- Check row counts match
2SELECT COUNT(*) FROM users;
3SELECT COUNT(*) FROM users_new;
4
5-- Check for NULL values introduced by failed casts
6SELECT COUNT(*) FROM users_new WHERE user_id IS NULL;
7
8-- Spot-check value ranges
9SELECT MIN(user_id), MAX(user_id) FROM users;
10SELECT MIN(user_id), MAX(user_id) FROM users_new;

Common Type Conversions and Their Gotchas

Note that Redshift now supports ALTER TABLE ... ALTER COLUMN ... TYPE VARCHAR(n) when you are only increasing the length of a VARCHAR column. This is the one case where you do not need the four-step workaround.

Common Pitfalls

Forgetting the transaction wrapper. Without BEGIN/COMMIT, a failure at step 3 leaves you with two columns and no rollback path. Always wrap the migration in a transaction.

Running the migration during peak query hours. The UPDATE statement acquires a table-level write lock. On a table with hundreds of millions of rows, this can block reads for minutes. Schedule migrations during maintenance windows or low-traffic periods.

Ignoring views and materialized views. Views that reference the dropped column will break silently. Query pg_views and stv_mv_info to find all dependent objects before starting.

Assuming column order is preserved. The new column is appended at the end of the table. Any downstream code relying on ordinal column positions (such as COPY without a column list) will break.

Skipping validation. Cast operations can introduce NULLs or truncated values. A VARCHAR-to-INTEGER cast on a column with non-numeric strings will fail the entire UPDATE. Filter or clean the data first.

Summary

Altering a column's data type in Amazon Redshift requires a manual migration because the columnar storage engine does not support in-place type changes (with the exception of VARCHAR length increases). The standard approach is add, copy, drop, rename, wrapped in a transaction. For columns involved in sort or distribution keys, use a deep-copy strategy with a new table. Always validate data integrity after migration, account for dependent objects like foreign keys and views, and schedule the operation during low-traffic windows to minimize locking impact.