如何在Postgres中为TEXT与DATE拼接表达式创建索引？

核心问题 #

报错的根本原因是：你用来拼接的日期转文本操作不是IMMUTABLE类型的函数。

当你直接把date类型和text拼接时，PostgreSQL会隐式调用日期转文本的函数，这个转换依赖数据库的时区配置——不同时区下同一日期的文本输出可能有差异，这类依赖外部配置的函数属于STABLE而非IMMUTABLE，而PostgreSQL要求索引表达式必须是IMMUTABLE（确保索引值永远不会变化），所以触发了错误。

哪怕你显式用dob::text转换，本质还是调用了同一个非immutable的转换函数，所以不管换concat还是直接用||，都解决不了问题。

解决方法 #

要让整个表达式变成IMMUTABLE，你需要用固定格式的日期转换，也就是用to_char函数指定明确的日期格式——to_char(date, '固定格式')是标准的IMMUTABLE函数，因为它的输出只依赖输入的日期和指定的格式，和任何外部配置无关。

修改后的索引创建语句：

create index fuzzy on person ((lower(concat(first_name, last_name, to_char(dob, 'YYYY-MM-DD')))));

如果习惯用||拼接，也可以写成：

create index fuzzy on person ((lower(first_name || last_name || to_char(dob, 'YYYY-MM-DD'))));

同时，你的查询语句也要对应调整，保证和索引表达式完全一致，这样查询时才能命中索引：

select * from person 
where levenshtein('johndoe2000-01-01', lower(concat(first_name, last_name, to_char(dob, 'YYYY-MM-DD')))) < 4;

补充说明 #

• 单独用date列创建索引时，你是直接索引date类型本身，不是转成text的结果，所以不会涉及非immutable的转换；
• 单独用text列时，lower和文本拼接都是IMMUTABLE操作，自然能正常创建索引。

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