I have table products, in which there is an hstore array field countries_of_origin.

So a product that is assembled from multiple products might have multiple countries of origin:

  { "country" => "USA" },
  { "country" => "MEX" }

My only issue is, I have no idea whatsoever on how to write a SQL update. Our developers interact with the table via an ORM. But I sometimes have to do a mass update for expediency's sake, and I don't know to do it!

Thanks for any help you can offer.


Look at the examples for hstore in the PostgreSQL documentation.

UPDATE tab SET h = h || ARRAY['b => 2'::hstore,'c => 3'::hstore];

  • That looks like it will just work for hstore, not hstore[]. But by trying a combination of a couple of things on that page I came up with: update products set brands = ARRAY['country=>USA'::hstore,'country=>RUS'::hstore] where id = 2; – AKWF Sep 11 '15 at 1:28
  • 1
    You're quite right. I've updated the example for hstore[] instead of hstore. My apologies. – Kassandry Sep 11 '15 at 2:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.