PostgreSQL生成列：无需自定义函数将Varchar转为Date的方法？

我尝试用PostgreSQL的生成列（generated column）将Varchar类型列转换为Date类型列，生成日期的表达式包含字符串处理，以得到可转换为DATE的合适字符串。但根据PostgreSQL生成列文档，生成列表达式仅允许使用immutable（不可变）函数，因此使用CAST或TO_DATE会报错：generation expression is not immutable。

现有可行方案：

• 创建TO_DATE的immutable版本函数（示例代码如下），但不确定是否存在潜在不良影响；
• 将字符串转换为日期格式后仍以Varchar类型存储在生成列中。

CREATE OR REPLACE FUNCTION immutable_to_date_fn(varchar) 
RETURNS DATE 
AS $$
BEGIN
    RETURN CAST(
        -- 此处为字符串处理逻辑
    AS DATE);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

现咨询：是否存在无需自定义函数，即可在生成列表达式中实现Varchar转Date的方法？

解决方案 #

有，核心思路是使用PostgreSQL内置的immutable函数组合构造日期，避开依赖可变参数的TO_DATE或CAST(... AS DATE)（这两个函数属于stable级别，因为依赖数据库的datestyle配置）。

适用场景：字符串格式固定且可拆分 #

如果你的Varchar列日期字符串格式完全固定（比如ISO标准的YYYY-MM-DD、MM/DD/YYYY等），可以通过以下步骤实现：

1. 用substr（immutable函数）拆分字符串，提取年、月、日的子串；
2. 将子串转换为整数（::int是immutable操作）；
3. 用make_date(int, int, int)（immutable函数）构造日期。

示例1：字符串格式为YYYY-MM-DD

假设Varchar列名为date_str，生成列定义如下：

ALTER TABLE your_table
ADD COLUMN generated_date DATE GENERATED ALWAYS AS (
    make_date(
        (substr(date_str, 1, 4))::integer,
        (substr(date_str, 6, 2))::integer,
        (substr(date_str, 9, 2))::integer
    )
) STORED;

示例2：字符串格式为MM/DD/YYYY

调整substr的参数位置即可：

ALTER TABLE your_table
ADD COLUMN generated_date DATE GENERATED ALWAYS AS (
    make_date(
        (substr(date_str, 7, 4))::integer,
        (substr(date_str, 1, 2))::integer,
        (substr(date_str, 4, 2))::integer
    )
) STORED;

注意事项 #

• 该方法仅适用于格式完全统一的字符串，若字符串格式混杂（比如同时存在YYYY-MM-DD和MM/DD/YYYY），则无法直接使用，只能回到自定义函数方案；
• 自定义immutable函数的风险：如果转换逻辑依赖数据库的datestyle或区域设置，强制标记为immutable会导致配置变更后函数结果不更新，引发数据一致性问题。只有当转换逻辑完全不依赖可变配置时，才适合自定义immutable函数。

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