Redshift - How to remove NOT NULL constraint?

sql amazon-redshift
shihpeng picture shihpeng · Apr 9, 2015 · Viewed 16k times · Source

Since Redshift does not support ALTER COLUMN, I would like to know if it's possible to remove the NOT NULL constraints from columns in Redshift.

Answer

Rahul Tripathi picture Rahul Tripathi · Apr 9, 2015

You cannot alter the table.

There is an alternative approach. You can create a new column with NULL constraint. Copy the values from your old column to this new column and then drop the old column.

Something like this:

ALTER TABLE table1 ADD COLUMN somecolumn (definition as per your reqm);
UPDATE table1 SET somecolumn = oldcolumn;
ALTER TABLE table1 DROP COLUMN oldcolumn;
ALTER TABLE table1 RENAME COLUMN somecolumn TO oldcolumn;

Related questions

postgresql - view schema privileges

Is there a query I can run to show currently assigned privileges on a particular schema? i.e. privileges that were assigned like so: GRANT USAGE ON SCHEMA dbo TO MyUser I have tried SELECT * FROM information_schema.usage_privileges; …

Read More
How do I view grants on Redshift

I'd like to view grants on redshifts. I found this view for postgres: CREATE OR REPLACE VIEW view_all_grants AS SELECT use.usename as subject, nsp.nspname as namespace, c.relname as item, c.relkind as type, use2.usename …

Read More
How to get the last day of month in postgres?

How to find the last day os the month in postgres? I have a date columns stored as numeric(18) in the format(YYYYMMDD) I am trying it to make it date using to_date("act_dt",'YYYYMMDD') AS "act date" …

Read More