Multicolumn index with expression (PostgreSQL and Rails)

postgresql, ruby-on-rails, sql

Solution

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

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

Problem

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.

Original source