How to add SUPER column to existing AWS Redshift table?

Question

GOAL

I would like to add a Redshift SUPER column to and existing redshift table.
I need this to store JSON data there

CODE

This is how Normally I would add a new column.

ALTER TABLE products 
ADD COLUMN created_at NOT NULL;

1. Tried

CREATE TABLE temp_test_persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

ALTER TABLE temp_test_persons ADD COLUMN newSuperColumn SUPER NOT NULL;

Error running query: ALTER TABLE ADD COLUMN defined as NOT NULL must have a non-null default expression

Reviewed Solutions

Did you try simply adding the datatype to your statement? `ALTER TABLE products ADD COLUMN newSuperColumn SUPER NOT NULL;` — Ross Presser, Nov 29 '21 at 17:34
I have tried but it gave back an error (as yo can see it above in my edited post). — sogu, Nov 30 '21 at 09:31

score 2 · Answer 1 · answered Dec 01 '21 at 17:05

2

The solution is to set a default value for the column. Then you can define the column as NotNull. Like this

ALTER TABLE temp_test_persons 
ADD COLUMN newSuperColumn SUPER NOT NULL 
DEFAULT '';

answered Dec 01 '21 at 17:05

DataMatthis

31
3

score 1 · Accepted Answer · answered Nov 30 '21 at 09:35

1

ALTER TABLE temp_test_persons 
ADD COLUMN newSuperColumn SUPER;

answered Nov 30 '21 at 09:35

sogu

2,738
5
31
90

How to add SUPER column to existing AWS Redshift table?

Error running query: ALTER TABLE ADD COLUMN defined as NOT NULL must have a non-null default expression

2 Answers2