How to create a "collate nocase" column in a table in migration?

Question

This answer https://stackoverflow.com/a/973785/1297371 to the question: How to set Sqlite3 to be case insensitive when string comparing? tells how to create table with "COLLATE NOCASE" column.

My question is how to create such column in a rails migration? i.e. how from

create table Test
(
  Text_Value  text collate nocase
);

create index Test_Text_Value_Index
  on Test (Text_Value collate nocase);

do:

create_table :tests do
  #WHAT HERE?
end
add_index #... WHAT HERE?

score 0 · Answer 1 · edited Apr 02 '13 at 21:11

0

I added new migration where I deleted and recreated the index like:

class AddIndexToWordVariations < ActiveRecord::Migration

  def change

    remove_index :word_variations, :variation_word

    add_index :word_variations, :variation_word, :COLLATE => :NOCASE

  end

end

where 'word_variations' is my table

edited Apr 02 '13 at 21:11

Trinimon

13,839
9
44
60

answered Apr 02 '13 at 20:34

ayakushev

1

Presumably this isn't on sqlite3 (at least not back in 2013), just for people searching: http://stackoverflow.com/questions/37878967/sqlite3-on-rails-create-table-using-collate-nocase – David Ljung Madison Stellar Jun 17 '16 at 19:22

score 0 · Answer 2 · answered Jul 06 '16 at 07:59

For people searching, if 'collate' is still not available in your version of rails (you'll get "Unknown key: :COLLATE"), then you have to create the index manually:

execute("create index Test_Text_Value_Index on Test (Text_Value collate nocase);")

In your 'def up' (the 'drop_table' will drop the index as well in your 'def down')

How to create a "collate nocase" column in a table in migration?

2 Answers2