how 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:
According to this thread in the hackers mailing list:
this is intended behaviour as
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:
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
But I do think Sam’s suggestion using a straight date in the index is probably better