Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

ALTER TABLE ADD and DROP COLUMN Examples

The following examples demonstrate how to use ALTER TABLE to add and then drop a basic table column and also how to drop a column with a dependent object.

ADD Then DROP a Basic Column

The following example adds a standalone FEEDBACK_SCORE column to the USERS table. This column simply contains an integer, and the default value for this column is NULL (no feedback score).

First, query the PG_TABLE_DEF catalog table to view the USERS table:

Copy
column | type | encoding | distkey | sortkey --------------+------------------------+----------+---------+-------- userid | integer | delta | true | 1 username | character(8) | lzo | false | 0 firstname | character varying(30) | text32k | false | 0 lastname | character varying(30) | text32k | false | 0 city | character varying(30) | text32k | false | 0 state | character(2) | bytedict | false | 0 email | character varying(100) | lzo | false | 0 phone | character(14) | lzo | false | 0 likesports | boolean | none | false | 0 liketheatre | boolean | none | false | 0 likeconcerts | boolean | none | false | 0 likejazz | boolean | none | false | 0 likeclassical | boolean | none | false | 0 likeopera | boolean | none | false | 0 likerock | boolean | none | false | 0 likevegas | boolean | none | false | 0 likebroadway | boolean | none | false | 0 likemusicals | boolean | none | false | 0

Now add the feedback_score column:

Copy
alter table users add column feedback_score int default NULL;

Select the FEEDBACK_SCORE column from USERS to verify that it was added:

Copy
select feedback_score from users limit 5; feedback_score ---------------- (5 rows)

Drop the column to reinstate the original DDL:

Copy
alter table users drop column feedback_score;

DROPPING a Column with a Dependent Object

This example drops a column that has a dependent object. As a result, the dependent object is also dropped.

To start, add the FEEDBACK_SCORE column to the USERS table again:

Copy
alter table users add column feedback_score int default NULL;

Next, create a view from the USERS table called USERS_VIEW:

Copy
create view users_view as select * from users;

Now, try to drop the FEEDBACK_SCORE column from the USERS table. This DROP statement uses the default behavior (RESTRICT):

Copy
alter table users drop column feedback_score;

Amazon Redshift displays an error message that the column cannot be dropped because another object depends on it.

Try dropping the FEEDBACK_SCORE column again, this time specifying CASCADE to drop all dependent objects:

Copy
alter table users drop column feedback_score cascade;