How to Add or Remove Default Values or Null Constraints to a Column in Redshift

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:

Method 1: Add a New Column, Copy, then Drop the Old

1. Add a new column to the table with the same data type as the original column, plus the default value
2. Use update to backfill the data
3. Drop the old column
4. Rename the new column to use the original column name

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;

Method 2: Recreate the Table

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:

1. Create a new table using the basic CREATE TABLE statement
2. Use INSERT with a SELECT command to copy over the contents from old to new table.
3. Drop the old table
4. Rename the new table to use the original table name

-- 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.