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

我正试图在varchar列的转换上创建一个索引。我在做这样的事情：

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

我得到了一个错误：functions in index expression must be marked IMMUTABLE但是我不知道为什么，到目前为止的转换不依赖于时区或类似的东西（这使得转换到时间戳和时区给出了这个错误）。
有什么帮助吗？

最佳答案

第一个错误是将日期存储为varchar列。你不应该那样做。
正确的解决方法是将列转换为实际的date列。
现在我很确定对这句话的回答是“我没有设计数据库，我不能更改它”，所以这里有一个解决方法：
CAST和to_char()不是不可变的，因为它们可以根据当前会话的设置为相同的输入值返回不同的值。
如果您知道表中所有值的格式都是一致的（如果您知道，这意味着您可以将列转换为实际的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列中只有一个“非法”值，那么这种方法将会失败。唯一合理的解决方案是将日期存储为dates，

关于postgresql - PostgreSQL在从字符串到日期的转换上创建索引，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16405602/