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

问题描述

我试图在迄今为止的varchar列的演员表上创建索引。我正在做这样的事情：

I'm trying to create an index on the cast of a varchar column to date. I'm doing something like this:

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

我遇到了错误：索引表达式中的函数必须标记为IMMUTABLE 但我不明白为什么，到目前为止的转换不取决于时区或类似的东西（这会使具有时区的时间戳转换为该错误）。

I'm getting the error: functions in index expression must be marked IMMUTABLE But I don't get why, the cast to date doesn't depends on the timezone or something like that (which makes a cast to timestamp with time zone give this error).

有帮助吗？

推荐答案

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

Your first error was to store a date as a varchar column. You should not do that.

正确的解决方法是将列转换为实际的 date 列。

The proper fix for your problem is to convert the column to a real date column.

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

Now I'm pretty sure the answer to that statement is "I didn't design the database and I cannot change it", so here is a workaround:

CAST 和 to_char（）并不是不变的，因为他们可以根据当前会话的设置为相同的输入值返回不同的值。

CAST and to_char() are not immutable because they can return different values for the same input value depending on the current session's settings.

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

If you know you have a consistent format of all values in the table (which - if you had - would mean you can convert the column to a real date column) then you can create your own function that converts a varchar to a date and is marked as immutable.

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
/

具有该定义您可以在表达式上创建索引：

With that definition you can create an index on the expression:

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

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

But you have to use exactly that function call in your query so that Postgres uses it:

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

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

Note that this approach will fail badly if you have just one "illegal" value in your varchar column. The only sensible solution is to store dates as dates,

这篇关于PostgreSQL在从字符串到日期的转换上创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！