On Monday, February 19, 2018 at 5:38:45 AM UTC-8, João Alexandre wrote: > > Hi folks, I couldn't find this neither in documentation or former posts. > I'm sorry if it has been mentioned already. > > I'm trying to achieve this using Postgres and Sequel: > > create index my_index_name on my_table ((meta_data->>'meta_data_key')) > > This creates a btree index on a Jsonb column called *meta_data* looking > for internal json keys with the name *meta_data_key. *So, on Sequel > migration I'm trying things like: > > Sequel.migration do > change do > alter_table :my_table do > add_column :meta_data, "Jsonb" > add_index "((meta_data->>score))".to_sym > end > end > end > > > The problem is on the `add_index "((meta_data->>score))".to_sym` line. I > keep getting a *PG::UndefinedColumn: ERROR: column "(meta_data->>score)" > does not exist.* > > I've also tried several variations like: > `add_index "meta_data->>score" > `add_index :meta_data->>score > `add_index "(meta_data->>score)" > > Does anyone know to properly do this? >
In general, anytime you want to use raw SQL, use Sequel.lit: add_index Sequel.lit('(meta_data->>score)'), :name=>'score_idx' However, you could just use the pg_json_ops extension and use: add_index Sequel.pg_jsonb(:meta_data).get_text(:score), :name=>'score_idx' Not sure either of those will do what you really want though, you probably want to cast it to integer or numeric: add_index Sequel.pg_jsonb(:meta_data).get_text(:score).cast(Integer), :name=>'score_idx' Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To post to this group, send email to firstname.lastname@example.org. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.