PostgreSQL 在从字符串到日期的转换上创建索引

Question

我正在尝试为迄今为止的 varchar 列创建一个索引。我正在做这样的事情:

CREATE INDEX date_index ON table_name (CAST(varchar_column AS DATE));

我收到错误:索引表达式中的函数必须标记为 IMMUTABLE 但我不明白为什么，到目前为止的转换不依赖于时区或类似的东西(这将时区转换为时间戳会出现此错误)。

有什么帮助吗？

Answer 1

您的第一个错误是将日期存储为 varchar 列。你不应该那样做。

解决问题的正确方法是将该列转换为真正的date 列。

现在我很确定该声明的答案是“我没有设计数据库并且我无法更改它”，所以这是一个解决方法:

CAST 和 to_char() 不是不可变的，因为它们可以根据当前 session 的设置为相同的输入值返回不同的值。

如果您知道表中所有值的格式一致(如果有的话，这意味着您可以将该列转换为真正的 date 列)，那么您可以创建自己的将 varchar 转换为日期并标记为不可变的函数。

create or replace function fix_bad_datatype(the_date varchar)
   returns date
   language sql
   immutable
as
$body$
  select to_date(the_date, 'yyyy-mm-dd');
$body$
ROWS 1
/

使用该定义，您可以在表达式上创建索引:

CREATE INDEX date_index ON table_name (fix_bad_datatype(varchar_column));

但是您必须在查询中准确地使用该函数调用，以便 Postgres 使用它:

select *
from foo
where fix_bad_datatype(varchar_column) < current_date;

请注意，如果您的 varchar 列中只有一个“非法”值，则此方法将严重失败。唯一明智的解决方案是将日期存储为date，