I have a table within postgres which has a month year column formatted as mmyyyy, how do I go about either adding a new column, or amending the exisiting column so I can have the format as mm-yyyy please?

CodePudding user response：

You can convert it to a proper date (at the start of the month), then format that date back the way you want it:

to_char(to_date(the_column, 'mmyyyy'), 'mm-yyyy')

You can use that in a SELECT query, create view or UPDATE the column using that expression.

Alternatively use a simple string manipulation:

left(the_column, 2)||'-'||right(the_column, 4)