CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Peter Tretiakov
Peter Tretiakov

Reputation: 3410

Multicolumn index with expression (PostgreSQL and Rails)

I need to create multicolumn index for 3 columns (VARCHAR, INT and INT), lets call them varchar_col, int1_col and int2_col. For VARCHAR column I need to apply lower(varchar_col) expression.

When I create one-column index with expression

add_index :table, 'lower(varchar_col)', name: 'index_name'

it works, but when I try to create multicolumn with

add_index :table, ['lower(varchar_col)', :int1_col, :int2_col], name: 'index_name'

I receive

PG::UndefinedColumn: ERROR: column "lower(varchar_col)" does not exist

Is it possible to create such an index with Rails add_index function or I need to execute SQL query in migration file? Does PostgreSQL support multicolumn index with expression? And if it supports, what I have to write in migration file to make it works?

Thank you for any help!

P.S. I'm using Rails 5.

Upvotes: 4

Views: 1336

Answers (2)

Peter Tretiakov
Peter Tretiakov

Reputation: 3410

So, after a little research, I've made it with executing SQL query in migration file. I just leave it here as another approach, but Ilya Lavrov's suggestion with string of columns also works.

migration file:

def up
  connection.execute(%q{
    CREATE INDEX index_name ON table(lower(varchar_col), int1_col, int2_col)
  })
end

def down
  remove_index :table, name: 'index_name'
end

Upvotes: 0

Ilya Lavrov
Ilya Lavrov

Reputation: 2860

You may send the columns with the expressions as a string:

add_index :table, 'lower(varchar_col), int1_col, int2_col', name: 'index_name'

Upvotes: 12

Related Questions