ERROR: functions in index expression must be marked IMMUTABLE in Postgres

by

According to this thread in the hackers mailing list:

http://www.mail-archive.com/[email protected]/msg86725.html

this is intended behaviour as to_char depends on the LC_MESSAGES setting

In your case this apparently doesn’t make sense as the format you are using will never depend on the locale, so if you do need to use the text representation in the index, you can create your own to_char() function and mark it as immutable:

CREATE OR REPLACE FUNCTION my_to_char(some_time timestamp) 
  RETURNS text
AS
$BODY$
    select to_char($1, 'yyyy-mm-dd');
$BODY$
LANGUAGE sql
IMMUTABLE;

If you have to use it as a text in the index (and cannot use the cast to a date as Sam suggested) you will need to create your own formatting function that you can mark as immutable. That can then be used in the index.

But to make Postgres use the index you will need to call my_to_char() in your SQL statements as well. It will not recognize it when you use the built-in to_char()

But I do think Sam’s suggestion using a straight date in the index is probably better

More Related Contents:

  1. Select rows which are not present in other table
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Aggregate columns with additional (distinct) filters
  5. Check if value exists in Postgres array
  6. Insert data in 3 tables at a time using Postgres
  7. How to use ANY instead of IN in a WHERE clause?
  8. Is there a shortcut for SELECT * FROM?
  9. sql statement error: “column .. does not exist”
  10. What is the difference between single quotes and double quotes in PostgreSQL?
  11. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  12. sql ORDER BY multiple values in specific order?
  13. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  14. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  15. PostgreSQL: Which Datatype should be used for Currency?
  16. Best way to count rows by arbitrary time intervals
  17. Sorting null values after all others, except special
  18. Reference alias in WHERE clause
  19. NOT NULL constraint over a set of columns
  20. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  21. Alphanumeric sorting with PostgreSQL
  22. Transposing an sql result so that one column goes onto multiple columns
  23. SQL column reference “id” is ambiguous
  24. How to use multiple CTEs in a single SQL query?
  25. IN Clause with NULL or IS NULL
  26. postgres – where in (list) – column does not exist
  27. How to get a list column names and datatypes of a table in PostgreSQL?
  28. Postgresql query between date ranges
  29. How to use SQL LIKE condition with multiple values in PostgreSQL?
  30. Why isn’t row level security enabled for Postgres views?

Leave a Comment