7

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?

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
Adam Matan
  • 11,659
  • 29
  • 80
  • 95
  • 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. – Craig Ringer Sep 22 '14 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 Sep 22 '14 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). – Erwin Brandstetter Sep 22 '14 at 21:42

2 Answers2

9

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.

Daniel Vérité
  • 31,182
  • 3
  • 72
  • 80
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')).

Gaurav
  • 506
  • 4
  • 6