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?

  • 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

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

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

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.

|improve this answer|||||

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

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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