Skip to content

Instantly share code, notes, and snippets.

@mmasashi

mmasashi/update_column_type.sql

Created October 22, 2013 20:18
Show Gist options
  • Star 8 You must be signed in to star a gist
  • Fork 6 You must be signed in to fork a gist
  • Save mmasashi/7107430 to your computer and use it in GitHub Desktop.
Save mmasashi/7107430 to your computer and use it in GitHub Desktop.
Download ZIP
How to change the column type for Redshift.
Raw
update_column_type.sql
BEGIN;
LOCK table_name;
ALTER TABLE table_name ADD COLUMN column_new column_type;
UPDATE table_name SET column_new = column_name;
ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name RENAME column_new TO column_name;
END;
-- varchar -> integer
-- UPDATE cpvbeacon_dev SET column_new = CAST (nullif(column_name, '') AS INTEGER);
@darkcrawler01
Copy link

this changes the order of column names.

@raniendu
Copy link

Wouldn't ALTER commit immediately ? BEGIN...END block is use less here.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment