Loading...

Redshift - How to change data type of a column when inserting?


I have a column metric_date of type Varchar in a Redshift table. It contains date values in the YYYY-MM-DD format. I wanted to change the type of a column to Date and made the following query:

ALTER TABLE main.metrics
ALTER COLUMN metric_date TYPE DATE 
USING to_date(metric_date, 'YYYY-MM-DD');

But it resulted in the following error:

Invalid operation: target data type "date" is not supported;

I decided to create a new table where metric_date will have a Date type and then insert data from the old table. But how do I insert values from a column of Varchar type to the metric_date column of Date type in a new table?

CREATE TABLE main.metrics_new
(  metric_id       varchar(20),
   metric_name     varchar(20),
   metric_date     date
);

INSERT INTO main.metrics_new
( metric_id,
  metric_name,
  metric_date // Date type here
)
SELECT
metric_id,
metric_name,
metric_date // Varchar type here
FROM main.metrics;
- - Source
comments powered by Disqus