How to deal with IMMUTABLE error in indexes that work with timestamps

Say that in an older version of PostgreSQL you had an index like this:

  1. span class=”st0″>’day’

and now after upgrading to 8.1+ you’re getting:
ERROR: functions in index expression must be marked IMMUTABLE

The reason is that date_trunc may return different results based on the current time zone. There’s an example query in PostgreSQL Mailing List Archives that you can run to believe.

If you are absolutely sure that you will always access your data with the same time zone you can redefine your index like this:

  1. span class=”st0″>’day’‘Europe/Prague’));

The whole immutable business with indexes containing functions that process timestamps is well explained in these threads:
http://archives.postgresql.org/pgsql-interfaces/…/msg00045.php
http://www.webservertalk.com/archive307-2007-2-1817943.html
http://www.mydatabasesupport.com/forums/…-possible.html

All credit goes to Tom Lane. I admire him for being always so patient and polite, and for spending so much of his valuable time with talking to users. I would love to link to his homepage but I failed to find it.

This entry was posted on Wednesday, March 19th, 2008 at 2:14 pm and is filed under Database, PostgreSQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply