Why isn't to_char IMMUTABLE, and how can I work around it?

10 votes

2 answers

9143 views

                          How can I index a to_char() of a column?

I have tried:

    adam_db=> CREATE INDEX updates_hourly_idx 
              ON updates (to_char(update_time, 'YYYY-MM-DD HH24:00'));

But got the error:

> ERROR:  functions in index expression must be marked IMMUTABLE

Which seems strange, since the to_char() of a timestamp is reasonably immutable.

Any ideas how to generate that index?

Asked by Adam Matan (12079 rep)

Sep 22, 2014, 01:31 PM
Last activity: Jul 14, 2018, 12:39 AM

Why isn't to_char IMMUTABLE, and how can I work around it?

Related Questions