Change the column type to 'Date' from 'Character varying' in PostgreSQL 11.0

Question

I have a postgreSQL 11.0 table with dates in below format, but the column type 'character varying'.

id        primary_completion_date 
0         December2019
1         April2020
2         September2021
3         September2022
4         December2021

Is it possible to convert the column type to 'date'. When I try to convert it to date, It changes the column content to below:

id        primary_completion_date
0         12-2019-01
1         04-2020-01
2         09-2021-01
3         09-2022-01
4         12-2021-01

I am using following statement in my script.

alter table tbl alter primary_completion_date type date using to_date(primary_completion_date, 'MonthYYYY')

How can I retain the column content as the input but change the column type to 'Date'.

user330315 · Accepted Answer · 2021-04-19 08:26:30Z

1

How can I retain the column content as the input but change the column type to 'Date'*"

You can't because December2019 is not a valid value for a date column.

But you can always format the date value when you retrieve it:

select id, to_char(primary_completion_date, 'MonthYYYY') as formatted_completion_date
from the_table

answered Apr 19, 2021 at 8:26

user330315

Sign up to request clarification or add additional context in comments.

1 Answer 1