错误：索引表达式中的函数必须在Postgres中标记为IMMUTABLE [英] ERROR: functions in index expression must be marked IMMUTABLE in Postgres

问题描述

我想创建一个多列表达式索引，但是当我创建索引时，将输出以下消息：

I want to create a Multicolumn expression index, but when I create the index, the following message is output:

--detail message 
wapgrowth=> create index CONCURRENTLY idx_test on  tmp_table using btree (skyid, to_char(create_time, 'YYYY-MM-DD'), actiontype );
ERROR:  functions in index expression must be marked IMMUTABLE


--table ddl
wapgrowth=> \d tmp_table
               Table "wapgrowth.tmp_table"
   Column    |            Type             |   Modifiers   
-------------+-----------------------------+---------------
 id          | integer                     | not null
 actiontype  | character varying(20)       | 
 apptype     | character varying(20)       | 
 score       | integer                     | 
 create_time | timestamp without time zone | default now()
 skyid       | integer                     | 
Indexes:

推荐答案

黑客邮件列表：

http://www.mail-archive.com/[e-mail]/msg86725.html

to_char 的行为取决于LC_MESSAGES设置

this is intended behaviour as to_char depends on the LC_MESSAGES setting

在您的情况下，这显然与您使用的格式没有任何意义使用的将永远不会依赖于语言环境，因此，如果您确实需要在索引中使用文本表示形式，则可以创建自己的to_char（）函数并将其标记为不可变：

In your case this apparently doesn't make sense as the format you are using will never depend on the locale, so if you do need to use the text representation in the index, you can create your own to_char() function and mark it as immutable:

CREATE OR REPLACE FUNCTION my_to_char(some_time timestamp) 
  RETURNS text
AS
$BODY$
    select to_char($1, 'yyyy-mm-dd');
$BODY$
LANGUAGE sql
IMMUTABLE;

如果必须将其用作索引中的文本（并且不能使用强制转换为日期正如Sam所建议的那样），您将需要创建自己的格式化功能，并将其标记为不可变的。

If you have to use it as a text in the index (and cannot use the cast to a date as Sam suggested) you will need to create your own formatting function that you can mark as immutable. That can then be used in the index.

但是要使Postgres 使用成为索引，您需要调用 my_to_char （）在您的SQL语句中。当您使用内置的 to_char（）

But to make Postgres use the index you will need to call my_to_char() in your SQL statements as well. It will not recognize it when you use the built-in to_char()

时，它不会识别它，但我确实认为Sam的建议使用索引中的连续日期可能更好

But I do think Sam's suggestion using a straight date in the index is probably better

这篇关于错误：索引表达式中的函数必须在Postgres中标记为IMMUTABLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！