Amazon RedShift Drop Default
I want to add a NOT NULL column to a table in RedShift. To do this, I provide a default value so that existing rows know what to put in the new column. But I don't want to default on this column - if there is no data 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 is giving me an error message ERROR: ALTER COLUMN SET/DROP DEFAULT is not supported
.
What's the correct way to add a non-empty column, update existing rows and not have a default?
UPDATE: It seems Redshift won't allow any ALTER COLUMN statements . Does it make it impossible?
+3
source to share
2 answers
You can do the following:
- Create a new table with a new schema (including NOT NULL)
- INSERT INTO new_table with your hardcoded pseudo default
- Delete old table
- Rename new
eg
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;
0
source to share