8

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?

3
  • I would've expected to_char of a timestamp to be immutable, yes. Not for timestamptz, but that's not what you're using. However, \df+ to_char shows all the to_char variants are only stable. Commented Sep 22, 2014 at 14:22
  • Can you add a new column to the table? If so, you could update that column to contain the function output and index it.
    – dartonw
    Commented Sep 22, 2014 at 15:48
  • The interesting question about the not immutable character of to_char() aside (Daniel nailed it) - may I inquire the purpose of such index? My educated guess is you do not need it. Rather cast search expressions to timestamp and use a plain index on the timestamp column, or if you need values truncated to full hours, use date_trunc('hour', update_time) instead, which is immutable for timestamp (but not for timestamptz, obviously). Commented Sep 22, 2014 at 21:42

2 Answers 2

10

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.

3

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')).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.