How to DROP COLUMN in Redshift

Galaxy Glossary

How do I drop a column in Amazon Redshift without affecting running queries?

ALTER TABLE … DROP COLUMN removes an existing column and its data from an Amazon Redshift table.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

When should you drop a column in Redshift?

Drop a column when it is obsolete, has been denormalized elsewhere, or stores erroneous data. Removing unused columns reduces disk usage and speeds up scans.

What permissions are required?

You need ownership of the table or superuser privileges.Granting ALTER on the table is insufficient; ownership is mandatory.

How do you drop a single column?

Use ALTER TABLE table_name DROP COLUMN column_name; Redshift reclaims space asynchronously, so the query finishes quickly while a background vacuum runs.

Can you drop multiple columns at once?

Yes. Since 2020, Redshift allows a comma-separated list: ALTER TABLE table_name DROP COLUMN col1, DROP COLUMN col2; This minimises lock time.

Does dropping a column lock the table?

Redshift takes a metadata lock for milliseconds.Reads continue; writes wait briefly. Plan maintenance windows for heavy workloads.

How to verify column removal?

Query SVV_COLUMNS or run SELECT * LIMIT 0; The dropped column no longer appears.

Best practices for production

1) Always back up critical tables with UNLOAD or snapshot. 2) Test in a dev cluster. 3) Perform VACUUM DELETE after large drops to reclaim space sooner.

How to restore a mistakenly dropped column?

You must add a new column and reload data from backup.Redshift cannot undelete a column.

Why How to DROP COLUMN in Redshift is important

How to DROP COLUMN in Redshift Example Usage


-- Remove deprecated 'stock' column from Products
aLTER TABLE Products
    DROP COLUMN stock;

How to DROP COLUMN in Redshift Syntax


ALTER TABLE <table_name>
    DROP COLUMN <column_name> [, DROP COLUMN <column_name> ...];

-- Options
-- IF EXISTS is not supported in Redshift as of this writing.
-- Only one ALTER TABLE statement can run at a time on the same table.

-- Ecommerce example
ALTER TABLE Orders
    DROP COLUMN discount_code,
    DROP COLUMN shipping_method;

Common Mistakes

Mistake: Assuming GRANT ALTER ON table lets you drop columns. Why wrong: Redshift requires table ownership. Fix: ALTER TABLE tablename OWNER TO your_user or connect as the owner.
Mistake: Forgetting to VACUUM after dropping large columns. Why wrong: Space isn’t reclaimed immediately, leading to bloated tables. Fix: Run VACUUM DELETE or schedule VACUUM shortly after the drop.