Adding a NOT NULL column to a Redshift table

Question

I'd like to add a NOT NULL column to a Redshift table that has records, an IDENTITY field, and that other tables have foreign keys to.

In PostgreSQL, you can add the column as NULL, fill it in, then ALTER it to be NOT NULL.

In Redshift, the best I've found so far is:

ALTER TABLE my_table ADD COLUMN new_column INTEGER;

-- Fill that column

CREATE TABLE my_table2 (
    id INTEGER IDENTITY NOT NULL SORTKEY,
    (... all the fields ... )
    new_column INTEGER NOT NULL,
    PRIMARY KEY(id)
) DISTSTYLE all;

UNLOAD  ('select * from my_table')
to 's3://blah' credentials '<aws-auth-args>' ;

COPY my_table2 
from 's3://blah' credentials '<aws-auth-args>'
EXPLICIT_IDS;

DROP table my_table;

ALTER TABLE my_table2 RENAME TO my_table;

-- For each table that had a foreign key to my_table:
ALTER TABLE another_table ADD FOREIGN KEY(my_table_id) REFERENCES my_table(id)

Is this the best way of achieving this?

Keith R Keith R · Accepted Answer · 2019-11-25T16:51:22

You can achieve this w/o having to load to S3.

modify the existing table to create the desired column w/ a default value
update that column in some way (in my case it was copying from another column)
create a new table with the column w/o a default value
insert into the new table (you must list out the columns rather than using (*) since the order may be the same (say if you want the new column in position 2)
drop the old table
rename the table
alter table to give correct owner (if appropriate)

ex:

-- first add the column w/ a default value
alter table my_table_xyz
    add visit_id bigint NOT NULL default 0; -- not null but default value

-- now populate the new column with whatever is appropriate (the key in my case)
update my_table_xyz
set visit_id = key;

-- now create the new table with the proper constraints
create table my_table_xzy_new
(
    key bigint not null,
    visit_id bigint NOT NULL, -- here it is not null and no default value
    adt_id bigint not null
);

-- select all from old into new
insert into my_table_xyz_new
select key, visit_id, adt_id
from my_table_xyz;

-- remove the orig table
DROP table my_table_xzy_events;

-- rename the newly created table to the desired table
alter table my_table_xyz_new rename to my_table_xyz;

-- adjust any views, foreign keys or permissions as required

Adding a NOT NULL column to a Redshift table

1 Answers