PostgreSQL：将年月格式从mmyyyy修改为mm-yyyy的实现方法

一、添加新列的方式 #

适合需保留原始数据的场景，新增格式化后的列，不改动原有数据。

1. 创建新列 #

假设原列名为month_year，新增格式化列month_year_formatted：

ALTER TABLE your_table_name ADD COLUMN month_year_formatted VARCHAR(7);

2. 填充格式化数据 #

根据原列的数据类型选择对应语句：

• 若原列是字符类型（VARCHAR/TEXT）：

UPDATE your_table_name
SET month_year_formatted = CONCAT(SUBSTRING(month_year FROM 1 FOR 2), '-', SUBSTRING(month_year FROM 3 FOR 4));

• 若原列是数字类型（INT/BIGINT）：

UPDATE your_table_name
SET month_year_formatted = CONCAT(
    SUBSTRING(CAST(month_year AS VARCHAR) FROM 1 FOR 2),
    '-',
    SUBSTRING(CAST(month_year AS VARCHAR) FROM 3 FOR 4)
);

3. 可选优化操作 #

• 确保新列非空（需确认所有数据已更新完成）：

ALTER TABLE your_table_name ALTER COLUMN month_year_formatted SET NOT NULL;

• 删除原列并重命名新列（替代原列）：

ALTER TABLE your_table_name DROP COLUMN month_year;
ALTER TABLE your_table_name RENAME COLUMN month_year_formatted TO month_year;

二、直接修改现有列的方式 #

适合不需要保留原始格式数据的场景，直接修改原列内容。

1. 原列为字符类型（VARCHAR/TEXT） #

直接更新格式：

UPDATE your_table_name
SET month_year = CONCAT(SUBSTRING(month_year FROM 1 FOR 2), '-', SUBSTRING(month_year FROM 3 FOR 4));

2. 原列为数字类型（INT/BIGINT） #

需先将列转为字符类型，再更新格式：

-- 转换列类型为字符串
ALTER TABLE your_table_name ALTER COLUMN month_year TYPE VARCHAR(6);

-- 更新为mm-yyyy格式
UPDATE your_table_name
SET month_year = CONCAT(SUBSTRING(month_year FROM 1 FOR 2), '-', SUBSTRING(month_year FROM 3 FOR 4));

-- 调整列长度匹配新格式
ALTER TABLE your_table_name ALTER COLUMN month_year TYPE VARCHAR(7);

三、规范日期类型存储方案（推荐） #

若后续需进行日期运算，建议将年月存储为DATE类型（存储每月第一天），查询时再格式化显示：

1. 添加DATE类型新列 #

ALTER TABLE your_table_name ADD COLUMN month_year_date DATE;

2. 转换并填充日期数据 #

UPDATE your_table_name
SET month_year_date = TO_DATE(month_year, 'mmyyyy');

3. 查询时格式化输出 #

SELECT TO_CHAR(month_year_date, 'mm-yyyy') AS month_year_formatted FROM your_table_name;

内容的提问来源于stack exchange，提问作者AJ_25