How to Change Column Type in Redshift

How do I change a column's data type in Amazon Redshift without data loss?

ALTER TABLE … ALTER COLUMN … TYPE lets you convert a column to a new data type in Amazon Redshift.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How do I change a column type in Redshift?

Run ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type. Optionally add USING to cast existing values, plus COLLATE or ENCODE. The statement rewrites the column; ensure backups and free disk space.

What are the prerequisites?

Confirm no active loads, the column is not a DISTKEY/SORTKEY, and you hold ALTER privilege. Verify the new type is compatible or supply an explicit cast.

How to cast existing data during the change?

Add a USING clause: ALTER TABLE Orders ALTER COLUMN total_amount TYPE DECIMAL(12,2) USING total_amount::DECIMAL(12,2);. Without USING, Redshift attempts an implicit cast and fails if unsafe.

Step-by-step example

1. Pause ETL jobs.
2. ALTER TABLE Orders ALTER COLUMN total_amount TYPE DECIMAL(12,2) ENCODE az64;
3. Verify with SELECT * FROM SVV_COLUMNS WHERE table_name='orders' AND column_name='total_amount';
4. Resume jobs after completion.

How to verify the change?

Query SVV_COLUMNS or pg_table_def to confirm the new data_type and encoding. Run test selects to ensure application logic still behaves correctly.

Best practices

Choose the smallest precise type to reduce I/O. Always specify column encoding after the change. Batch multiple column alterations in one transaction but monitor maintenance windows.

What can go wrong?

Long locks, insufficient disk space, or failed casts. Monitor STL_ALTER_TABLE for progress and errors. Abort quickly if disk usage spikes.

Why How to Change Column Type in Redshift is important

How to Change Column Type in Redshift Example Usage


-- Convert order amount to DECIMAL with explicit cast and encoding
ALTER TABLE Orders
    ALTER COLUMN total_amount TYPE DECIMAL(12,2)
    USING total_amount::DECIMAL(12,2)
    ENCODE az64;

How to Change Column Type in Redshift Syntax


ALTER TABLE table_name
    ALTER COLUMN column_name TYPE new_data_type
    [USING expression]
    [COLLATE collation]
    [ENCODE encoding];

-- Ecommerce example
ALTER TABLE Orders
    ALTER COLUMN total_amount TYPE DECIMAL(12,2)
    USING total_amount::DECIMAL(12,2)
    ENCODE az64;

Common Mistakes

Skipping the USING clause when the new type is not implicitly compatible. Fix by supplying an explicit cast: USING column_name::new_type.
Altering a DISTKEY/SORTKEY column, which Redshift forbids. Create a new column with the desired type, copy data, drop the old column, then rename.