Create new Date column of DATE type from existing Date column of TEXT type in PostgresSQL-CodePudding

I have a PostgresSQL table that has a Date column of type TEXT

Values look like this: 2019-07-19 00:00

I want to either cast this column to type DATE so I can query based on latest values, etc.... or create a new column of type DATE and cast into there (so I have both for the future). I would appreciate any advice on both options!

Hope this isn't a dupe, but I havn't found any answers on SO.

Some context, I will need to add more data later on to the table that only has the TEXT column, which is why i want to keep the original but open to suggestions.

CodePudding user response：

You can alter the column type with the simple command:

alter table my_table alter my_col type date using my_col::date

This seems to be the best solution as maintaining duplicate columns of different types is a potential source of future trouble.

Note that all values in the column have to be null or be recognizable by Postgres as a date, otherwise the conversion will fail.

Test it in db<>fiddle.

However, if you insist on creating a new column, use the update command:

alter table my_table add my_date_col date;
update my_table
set my_date_col = my_col::date;

Db<>fiddle.