ERROR: functions in index expression must be marked IMMUTABLE in Postgres

Question

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: 

Answer 1

According to this thread in the hackers mailing list:

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

this is intended behaviour as to_char depends on the LC_MESSAGES setting

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; 

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.

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()

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

Answer 2

This explains more in detail:

https://www.postgresql.org/message-id/CAKFQuwbcMfesmNkm19mXFLXP14sP5BiPsR1GSkY1suKiM1rukg%40mail.gmail.com

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)

it will work.