How to drop a column from a table in Redshift database

Below is the syntax to drop a column from a table in Redshift database where tablename is the name of the table and columnname is the name of the column being dropped.

alter table schemaname.tablename drop column columnname;

If you run into the below error because of dependent objects on the table, you can use CASCADE option to drop all the dependent objects along with the column being dropped.

ERROR: cannot drop table [schema_name].[table_name] column [column_name] because other objects depend on it

Run the below sql to identify all the dependent objects on the table.

select * from information_schema.view_table_usage where table_schema='schemaname' and table_name='tablename';

Obtain the ddl of the dependent objects using below query. This ddl can be used to recreate the objects after modifying the underlying table.

select view_definition from information_schema.views where table_schema='schemaname' and table_name='viewname';

Finally, drop the column with cascade option.

alter table tablename drop column columnname cascade;

You may recreate the dependent objects now.

See also  How to create and refresh a Materialized view in Redshift