我想创建一个多列表达式索引,但是当我创建索引时,输出以下消息:
--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/pgsql-hackers@postgresql.org/msg86725.html
这是预期行为,因为to_char
取决于LC_MESSAGES设置
在您的情况下,这显然没有意义,因为您使用的格式永远不会取决于区域设置,因此如果您确实需要在索引中使用文本表示形式,您可以创建自己的 to_char() 函数并将其标记为不可变:
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建议的那样使用转换为日期),则需要创建自己的格式化函数,可以将其标记为不可变。然后可以在索引中使用它。
但是要使 Postgres 使用索引,您还需要在 SQL 语句中调用my_to_char()
。当您使用内置to_char()
时,它将无法识别它
但我确实认为山姆的建议在索引中使用直接日期可能更好
这更详细地解释了:
https://www.postgresql.org/message-id/CAKFQuwbcMfesmNkm19mXFLXP14sP5BiPsR1GSkY1suKiM1rukg%40mail.gmail.com
基本上时区取决于服务器,因此如果有人更改它,结果可能会改变。但是,如果您锁定时区:
date(timezone('UTC', create_time)
它会起作用。
没有时区的时间戳的to_char
不是一个不可变的函数,因为转换取决于您的本地时区设置。这意味着索引不会移植到不同时区的另一台计算机上,Postgres 不允许这样做。我认为如果您将create_time声明为时区时间,问题就会消失。
不要使用 to_char 将时间戳格式化为 YYYY-MM-DD,请尝试将时间戳转换为日期类型,这将具有相同的效果:
create index CONCURRENTLY idx_test on tmp_table using btree (skyid, cast(create_time as date), actiontype );
归根结底,您似乎正在尝试索引create_time的"YYYY-MM-DD"表示形式。为什么不只是指数create_time?问题是 to_char() 是可变的,因为区域设置环境变量可能会更改,这会更改 to_char() 的输出。
http://www.postgresql.org/docs/current/static/charset.html
如果您可以控制架构,则可以添加一个新列(例如create_date TEXT)并对其进行索引,然后设置一个处理插入的触发器。事实上,如果您创建了将无时区时间戳转换为文本的方法,则可以以恒定的方式对其进行索引。a_horse_with_no_name的建议很好,因为我认为您不关心区域设置。
您遇到的问题是所有日期和时间处理代码都服从区域设置,这不是不可变的,因此您无法轻松依赖这些数据类型的 INDEX 函数。