PostgreSQL 在从字符串到日期转换时创建索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16405602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL create index on cast from string to date
提问by Topo
I'm trying to create an index on the cast of a varchar column to date. I'm doing something like this:
我正在尝试在迄今为止的 varchar 列的强制转换上创建索引。我正在做这样的事情：
CREATE INDEX date_index ON table_name (CAST(varchar_column AS DATE));
I'm getting the error: functions in index expression must be marked IMMUTABLEBut 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).
我收到错误：functions in index expression must be marked IMMUTABLE但我不明白为什么，迄今为止的演员表不依赖于时区或类似的东西（这使得时区的时间戳转换给这个错误）。
Any help?
有什么帮助吗？
采纳答案by a_horse_with_no_name
Your first error was to store a date as a varchar column. You should not do that.
您的第一个错误是将日期存储为 varchar 列。你不应该那样做。
The proper fix for your problem is to convert the column to a real datecolumn.
解决您的问题的正确方法是将列转换为真正的date列。
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:
现在我很确定该声明的答案是“我没有设计数据库并且我无法更改它”，所以这里有一个解决方法：
CASTand to_char()are not immutable because they can return different values for the same input value depending on the current session's settings.
CAST并且to_char()不是一成不变的，因为它们可以根据当前会话的设置为相同的输入值返回不同的值。
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 datecolumn) then you can create your own function that converts a varchar to a date and is marked as immutable.
如果您知道表中所有值的格式一致（如果有的话，这意味着您可以将列转换为实际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
/
With that definition you can create an index on the expression:
使用该定义，您可以在表达式上创建索引：
CREATE INDEX date_index ON table_name (fix_bad_datatype(varchar_column));
But you haveto use exactly that function call in your query so that Postgres uses it:
但是您必须在查询中准确使用该函数调用，以便 Postgres 使用它：
select *
from foo
where fix_bad_datatype(varchar_column) < current_date;
Note that this approach will fail badly if you have just one "illegal" value in your varchar column. The only sensible solution isto store dates as dates,
请注意，如果您的 varchar 列中只有一个“非法”值，则此方法将失败。唯一明智的解决方案是将日期存储为dates，
回答by Kuberchaun
Please provide the database version, table ddl, and some example data.
请提供数据库版本、表 ddl 和一些示例数据。
Would making your own immutable function do what you want, like this? Also look into creating a new cast in the docs and see if that does anything for you.
让你自己的不可变函数做你想做的事，像这样吗？还要研究在文档中创建一个新的演员表，看看它是否对你有任何帮助。
create table emp2 (emp2_id integer, hire_date VARCHAR(100));
insert into emp2(hire_date)
select now();
select cast(hire_date as DATE)
from emp2
CREATE FUNCTION my_date_cast(VARCHAR) RETURNS DATE
AS 'select cast( as DATE)'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
CREATE INDEX idx_emp2_hire_date ON emp2 (my_date_cast(hire_date));
