Multicolumn index with expression (PostgreSQL and Rails)

Question

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.

Answer 1

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

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