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?

See `https://stackoverflow.com/questions/29536916/redshift-how-to-remove-not-null-constraint` — Chris Riddell, Jun 23 '15 at 02:38
Or using a hyperlink- https://stackoverflow.com/questions/29536916/redshift-how-to%E2%80%8C%E2%80%8B-remove-not-null-con%E2%80%8C%E2%80%8Bstraint — Kirk Broadhurst, Dec 08 '17 at 20:03

score 1 · Answer 1 · answered Dec 23 '15 at 21:06

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;

score -1 · Answer 2 · answered Aug 30 '15 at 11:10

-1

You should choose the not null value for zero for example,

alter table my_table
      add column my_column int default 0

answered Aug 30 '15 at 11:10

user3600910

2,839
4
22
36

Amazon RedShift Drop Default

2 Answers2