SQL 错误：索引表达式中的函数必须在 Postgres 中标记为 IMMUTABLE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5973030/
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
ERROR: functions in index expression must be marked IMMUTABLE in Postgres
提问by francs
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:
采纳答案by a_horse_with_no_name
According to this thread in the hackers mailing list:
根据黑客邮件列表中的这个线程：
http://www.mail-archive.com/[email protected]/msg86725.html
http://www.mail-archive.com/[email protected]/msg86725.html
this is intended behaviour as to_chardepends on the LC_MESSAGES setting
这是预期行为，to_char取决于 LC_MESSAGES 设置
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:
在您的情况下，这显然没有意义，因为您使用的格式永远不会依赖于语言环境，因此如果您确实需要在索引中使用文本表示，您可以创建自己的 to_char() 函数并将其标记为不可变：
CREATE OR REPLACE FUNCTION my_to_char(some_time timestamp)
RETURNS text
AS
$BODY$
select to_char(, 'yyyy-mm-dd');
$BODY$
LANGUAGE sql
IMMUTABLE;
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.
如果您必须将它用作索引中的文本（并且不能像 Sam 建议的那样使用转换为日期），您将需要创建自己的格式化函数，您可以将其标记为不可变的。然后可以在索引中使用它。
But to make Postgres usethe 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()
但是要使 Postgres使用索引，您还需要调用my_to_char()SQL 语句。当您使用内置的时它不会识别它to_char()
But I do think Sam's suggestion using a straight date in the index is probably better
但我确实认为 Sam 的建议在索引中使用直接日期可能更好
回答by holdfenytolvaj
This explains more in detail:
这更详细地解释了：
basically the timezone depends on the server, and thus the result might change if somebody changes it. But if you lock the timezone:
基本上时区取决于服务器，因此如果有人更改它，结果可能会改变。但是如果你锁定时区：
date(timezone('UTC', create_time)
date(timezone('UTC', create_time)
it will work.
它会起作用。
回答by Andrew Lazarus
to_charof a timestamp-without-timezone is not an immutable function, because the conversion depends on your local time-zone setting. That means the index wouldn't be portable to another computer in a different time zone, and Postgres won't allow it. I think the problem will go away if you declare create_time as a time with timezone.
to_char没有时区的时间戳不是一个不可变的函数，因为转换取决于您当地的时区设置。这意味着索引不能移植到不同时区的另一台计算机上，Postgres 也不允许。我认为如果您将 create_time 声明为带时区的时间，问题就会消失。
回答by Sam Choukri
Instead of using to_char to format your timestamp as YYYY-MM-DD, try casting your timestamp to a date type, which will have the same effect:
不要使用 to_char 将时间戳格式化为 YYYY-MM-DD，而是尝试将时间戳转换为日期类型，这将具有相同的效果：
create index CONCURRENTLY idx_test on tmp_table using btree (skyid, cast(create_time as date), actiontype );
回答by Sean
At the end of the day it looks like you're trying to index a "YYYY-MM-DD" representation of create_time. Why not just INDEX create_time? The problem is to_char() is MUTABLE because the locale environment variable could change, which changes the output of to_char().
在一天结束时，您似乎正在尝试索引 create_time 的“YYYY-MM-DD”表示形式。为什么不只是 INDEX create_time？问题是 to_char() 是可变的，因为语言环境环境变量可能会改变，这会改变 to_char() 的输出。
http://www.postgresql.org/docs/current/static/charset.html
http://www.postgresql.org/docs/current/static/charset.html
If you have control over the schema, you could add a new column (e.g. create_date TEXT) and INDEX that, then setup a trigger that handles the inserts. In fact, if you created a way of converting your TIMESTAMP WITHOUT TIME ZONE to TEXT, you could INDEX that in a constant way. a_horse_with_no_name's suggestion was a good one since I don't think you care about locale.
如果您可以控制架构，您可以添加一个新列（例如 create_date TEXT）和 INDEX，然后设置一个处理插入的触发器。事实上，如果您创建了一种将 TIMESTAMP WITHOUT TIME ZONE 转换为 TEXT 的方法，则可以以恒定的方式对其进行索引。a_horse_with_no_name 的建议很好，因为我认为您不关心语言环境。
The problem you're running in to is all DATE and TIME handling code obeys locale, which isn't IMMUTABLE, therefore you can't easily INDEX functions that rely on those data types.
您遇到的问题是所有 DATE 和 TIME 处理代码都遵循区域设置，这不是 IMMUTABLE，因此您不能轻松地依赖于这些数据类型的 INDEX 函数。
