Amazon RedShift Drop Default

Question

I want to add a NOT NULL column to a table in RedShift. To do this, I specify a default so that existing rows know what to put in the new column. But I don't want a default on this column - if no data is supplied in my COPY command, I want it to fail. In Postgres I could do this:

ALTER TABLE my_table ALTER COLUMN my_column DROP DEFAULT;

However Redshift gives me an error saying ERROR: ALTER COLUMN SET/DROP DEFAULT is not supported.

What is the correct way to add a non-null column, update existing rows, and not have a default?

UPDATE: It seems Redshift won't allow any ALTER COLUMN statements. Does that make this impossible?

Answer 1

You could do the following:

• Create a new table with you new schema (including the NOT NULL)
• INSERT INTO new_table with your pseudo-default hard-coded
• Drop the old table
• Rename the new one

e.g

CREATE TABLE new_table 
(column_1 type, column_2 type, new_column type NOT NULL);

INSERT INTO new_table (column_1, column_2, new_column)
SELECT column_1, column_2, 'default_value' FROM old_table;

DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;