Other than renaming, Redshift does not allow changing a column’s attributes. To add a default value or a null constraint to a column in Redshift, you need to choose one of the following methods:
updateto backfill the data
Here’s an example of all the steps:
-- Add a new column with a default value ALTER TABLE products ADD COLUMN stocks_new integer DEFAULT 0; -- Or, if you want a not null constraint, add a new column with a not null constraint ALTER TABLE products ADD COLUMN created_at NOT NULL; -- Backfill the data UPDATE products SET stocks_new=stocks; -- Drop the old column ALTER TABLE products DROP COLUMN stocks; -- Rename the new column ALTER TABLE products RENAME stocks_new TO stocks;
Some people don’t like the previous method because it changes the order of the columns in the table with the recreated column ending up last. To avoid this, you can do the following instead:
SELECTcommand to copy over the contents from old to new table.
-- Create the new table with the default value CREATE TABLE products_new (stocks_new integer DEFAULT 0); -- Or, create the new table with with a not null constraint CREATE TABLE products_new (stocks_new integer NOT NULL); -- Backfill the data INSERT INTO products_new SELECT * FROM products; -- Drop the old table DROP TABLE products; -- Rename the new table ALTER TABLE products_new RENAME TO products;
Besides the overhead of copying over the contents of the whole table, another downside of this method is if you have other objects referring to the original table, such as views, you will also need to update/recreate them as they will not recognize the new table, even if it inherited the name of the original one.