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

Question

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?

Answer 1

The formats accepted by to_char(timestamp, text) include localized patterns that make it not immutable.

Example of different results with the same input:

test=> BEGIN;
test=> set lc_time='en_US.utf8';
test=> select to_char(now()::timestamp, 'TMDay');
 to_char 
---------
 Monday

test=> set lc_time TO 'fr_FR.utf8';
test=> select to_char(now()::timestamp, 'TMDay');
 to_char 
---------
 Lundi

test=> END;

If not using this kind of format, the solution is to create your own immutable wrapper function,

CREATE FUNCTION custom_to_char(timestamp) RETURNS text AS
$$ select to_char($1, 'YYYY-MM-DD HH24:00'); $$
LANGUAGE sql immutable;

and then create the index on that function.

Answer 2

I just found another solution to this problem based on this forum post -- EXTRACT(timestamp with time zone) isn't immutable, but EXTRACT(timestamp) is. So it is possible to create a table with CREATE INDEX ON table (EXTRACT(MONTH FROM timestamp AT TIME ZONE 'UTC')).