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

Question

Trying to implement the suggestions made to this other question:

I have written this migration to remove the current index and create the new one:

class ChangeIndexes < ActiveRecord::Migration[5.1]
  def change
    remove_index :part_masters, name: "part_masters_on_combo_idx" 

    execute <<-SQL
      CREATE INDEX ON part_masters (lower(unaccent(combo)) text_pattern_ops);
      CREATE INDEX ON locations (lower(unaccent(ubicacion)) text_pattern_ops);     
    SQL
   end
end

The problem is that I'm getting this error, I think because I'm using functions like lower or unaccent to create the index:

PG::InvalidObjectDefinition: ERROR: functions in index expression must be marked IMMUTABLE

Ernesto G · Accepted Answer · 2018-06-06 12:12:18Z

2

In case someone finds it useful in the future, the user @laurenz-albe provided the solution for this question in the referenced question, just had to create the function unaccent as a custom pg function:

CREATE FUNCTION my_unaccent(text) RETURNS text LANGUAGE SQL IMMUTABLE AS 
'SELECT unaccent($1)';

answered Jun 6, 2018 at 12:12

Ernesto G

5456 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related