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

问题描述：

我想创建一个varchar列转换日期的索引。我在做这样的事情：PostgreSQL创建索引从字符串转换到日期

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

，我发现了错误：functions in index expression must be marked IMMUTABLE但我不知道为什么，剧组至今不依赖于时区或类似的东西（这使得强制转换为带有时区的时间戳发出此错误）。

任何帮助？

有没有办法在pg转换日期通过特定的格式规则？如果格式必须被猜测，SQL Server不喜欢做这样的转换。 – user2246674 2013-05-06 19:23:24

答

您的第一个错误是将日期存储为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列中只有一个“非法”值，则此方法将失败。唯一明智的解决方案是存储日期为date s，

@Topo：'to_date（）'也是'STABLE'，而不是'IMMUTABLE'，即使这个很可能。 [Here]（http://www.postgresql.org/message-id/flat/[email protected]#[email protected]s）是关于devel列表的一个讨论，为什么会这样。 – 2013-05-06 19:43:28

但是，当我使用这样的查询'select * from foo where fix_bad_datatype（varchar_column）> ='2015-02-05':: date;'而不使用'current_date'时，需要比执行没有索引的时间更长的时间。任何想法？ – 2015-10-07 06:13:11

@MarlonAbeykoon：https://wiki.postgresql.org/wiki/Slow_Query_Questions – 2015-10-07 06:13:58

答

请提供数据库版本，表ddl和一些示例数据。

会使你自己的不可变函数做你想做的事情吗？也看看在文档中创建一个新的演员，看看是否为你做了任何事情。

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($1 as DATE)' 
    LANGUAGE SQL 
    IMMUTABLE 
    RETURNS NULL ON NULL INPUT; 


CREATE INDEX idx_emp2_hire_date ON emp2 (my_date_cast(hire_date));

我们如何查询单独的索引？ – Chris 2015-06-18 22:00:25

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

相关推荐