Search code examples
ruby-on-railspostgresqlhstore

How to migrate data from an associated table's column to an hstore column?

I'm working on a project where localisation was done by creating associated *_locales tables with a locale and name field. I am migrating this over to use an hstore column (using Postgres) but I don't quite have the syntax right.

The structure is now like this

table: thingy_locales
  locale :string
  name   :string
  thingy_id : integer


table: thingies
  name_translations :hstore

In a migration I wish to move all data from the thingy_locales table into the name_translations field with an key of 'en' (as currently there are only 'en' locales in the thingy_locales table.)

so I've tried this

execute "UPDATE thingies t SET name_translations=(select (\"'en'\" => \"'|name|'\")::hstore from thingy_locales where thingy_id = t.id);"

but that gives me

PG::UndefinedColumn: ERROR:  column "'en'" does not exist
LINE 1: ...ort_categories loc SET name_translations=(select ("'en'" => ...
                                                             ^

What have I done wrong?

Solution

  • Okay I got it to work.

    execute "UPDATE thingies t SET name_translations=hstore('en', (select name from thingy_locales where thingy_id = t.id));"

    does the job perfectly