Skip to content

Instantly share code, notes, and snippets.

@ferblape
Created December 6, 2020 06:44
Show Gist options
  • Save ferblape/9415ca01dace22963aa86e12d36c8017 to your computer and use it in GitHub Desktop.
Save ferblape/9415ca01dace22963aa86e12d36c8017 to your computer and use it in GitHub Desktop.
Postgres ALTER COLUMN column typecasting
DROP TABLE IF EXISTS test;
CREATE TABLE test(col_integer varchar, col_numeric varchar, col_boolean varchar, col_date varchar, col_timestamp varchar, col_time varchar);
INSERT INTO test VALUES('1', '1.33', 'true', '2020-10-21', '2020-11-21 00:32:10', '10:32:10');
INSERT INTO test VALUES('2', '2.33', 'false', '2020-10-22', '2020-11-22 01:32:10', '10:31:10');
ALTER TABLE test ALTER COLUMN col_integer TYPE integer USING (col_integer::integer);
ALTER TABLE test ALTER COLUMN col_numeric TYPE numeric USING (col_numeric::numeric);
ALTER TABLE test ALTER COLUMN col_boolean TYPE boolean USING (col_boolean::boolean);
ALTER TABLE test ALTER COLUMN col_date TYPE timestamp USING (col_date::timestamp);
ALTER TABLE test ALTER COLUMN col_timestamp TYPE timestamp USING (col_timestamp::timestamp);
ALTER TABLE test ALTER COLUMN col_time TYPE time USING (col_time::time);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment