PostgreSQL如何将text类型日期列转换为指定格式的date类型

首先纠正你原有代码的一个错误：你存储的01-31-2020是「月-日-年」的格式，你写的DD-MM-YYYY是「日-月-年」，用这个格式转换会直接报错，因为31不可能是月份，正确的转换格式串是MM-DD-YYYY。

1. 查询时得到01-31-2020格式的输出 #

PostgreSQL的date类型本身是二进制存储的，没有固定的显示格式，你看到的2020-01-31只是数据库客户端的默认展示样式。如果需要固定格式的字符串结果，需要用TO_CHAR函数对日期值做格式化：

SELECT TO_CHAR(TO_DATE(date_text, 'MM-DD-YYYY'), 'MM-DD-YYYY') AS formatted_date
FROM my_table;

2. 直接将列更新为date类型 #

为了避免数据丢失，建议按以下步骤操作，先校验转换结果再替换原列：

• 第一步：新增一个date类型的临时列存储转换结果

ALTER TABLE my_table ADD COLUMN tmp_date DATE;

• 第二步：把原text列的日期转换后写入临时列

UPDATE my_table 
SET tmp_date = TO_DATE(date_text, 'MM-DD-YYYY');

如果执行UPDATE时报错，说明原date_text列存在不符合MM-DD-YYYY格式的脏数据，需要先清理异常值后再重新执行转换。

• 第三步：校验转换结果是否正确，确认没有转换异常或数据错误

-- 随机抽20条对比原数据和转换后的数据
SELECT date_text, tmp_date FROM my_table LIMIT 20;

• 第四步：确认无误后，删除原text列，将临时列重命名为原列名

ALTER TABLE my_table DROP COLUMN date_text;
ALTER TABLE my_table RENAME COLUMN tmp_date TO date_text;

提示：后续如果需要对这个date类型的列做排序、日期计算、按时间段过滤等操作，直接用列本身即可，不需要转成字符串；只有需要输出MM-DD-YYYY格式的展示结果时，再用TO_CHAR做格式化即可。

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