redshift Uncategorized

Redshift: unable to connect newly created instance

Problem: you just created a new instance, and even though you told it to be publicly available, you can’t connect to it using the provided endpoint….

Solution: you need to explicitly add your current ip address to the security group you are using. Their default security group is miss-leading: even though it says it will accept all traffic from everywhere, it doesn’t (sad panda). Once you add a new security group and attach it to the redshift instance, you will be fine fine fine.

Source of the solution: the infamous stackoverflow:


Redshift: alter table column TYPE is not allowed

Only allowed for varchar column types.

The trick to get it done:

ALTER TABLE sometable ADD COLUMN some_new_column (with the new definition you want)
UPDATE sometable SET some_new_column = old_column;
ALTER TABLE sometable DROP COLUMN old_column;
ALTER TABLE sometable RENAME COLUMN some_new_column TO old_column;

The catch: column order will be altered (the new column will be the last now

If you use copy to fill out that table, you can’t reorder columns to make it fit still

If that is your setup, instead of create a new column, create a new table with the right TYPE, and do as above