PostgreSQL中如何转换格式不统一的字符串日期为日期类型？

方案一：直接在PostgreSQL中转换（无需重新处理CSV） #

由于日期字符串存在两种格式（dd/mm/yyyy和mm/dd/yyyy），直接用to_date会因格式不匹配报错。可以创建一个自定义函数尝试多种格式解析，捕获转换错误：

1. 创建安全转换函数 #

CREATE OR REPLACE FUNCTION safe_to_date(input_str text, format_list text[])
RETURNS date AS $$
DECLARE
    parsed_date date;
BEGIN
    -- 遍历所有候选格式，尝试解析
    FOREACH format IN ARRAY format_list LOOP
        BEGIN
            parsed_date := to_date(input_str, format);
            RETURN parsed_date;
        EXCEPTION WHEN invalid_datetime_format THEN
            -- 格式不匹配，继续尝试下一种
            CONTINUE;
        END;
    END LOOP;
    -- 所有格式都失败时返回NULL（可根据需求调整，比如抛出错误）
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 转换列类型 #

执行以下语句将varchar类型的project_date列转为date类型：

ALTER TABLE your_table_name
ALTER COLUMN project_date TYPE date
USING safe_to_date(project_date, ARRAY['MM/DD/YYYY', 'DD/MM/YYYY']);

该函数会先尝试按mm/dd/yyyy解析，失败则自动切换为dd/mm/yyyy，完美适配你的示例数据：

• '12/31/2022' → 解析为2022-12-31
• '23/10/2022' → 解析为2022-10-23

方案二：预处理CSV文件（适合未导入数据的场景） #

如果还未将CSV导入数据库，先统一日期格式会更稳妥，避免后续数据转换风险。以Python为例，用pandas批量处理：

import pandas as pd

# 读取CSV文件
df = pd.read_csv("your_source_file.csv")

# 自定义日期解析函数
def parse_mixed_dates(date_str):
    try:
        # 先尝试mm/dd/yyyy格式
        return pd.to_datetime(date_str, format="%m/%d/%Y")
    except ValueError:
        # 失败则尝试dd/mm/yyyy格式
        return pd.to_datetime(date_str, format="%d/%m/%Y")

# 转换日期列
df["project_date"] = df["project_date"].apply(parse_mixed_dates)

# 导出格式化后的CSV
df.to_csv("formatted_date_file.csv", index=False)

之后将处理后的CSV导入PostgreSQL时，直接指定project_date列为date类型即可。

方案选择建议 #

• 已完成数据导入：优先用方案一，无需重新导入数据，操作便捷。
• 未导入数据：优先用方案二，预处理后的数据格式统一，减少数据库端的转换逻辑，更易维护。

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