1

I am trying to add 2 new hstore key/value pairs in my postgreSQL database. Is it possible to do this in one statement or do I need to break it up? Here is what I have so far and it is not working.

with foo as (
   select 
          id, 
          new_value_1,
          new_value_2
   from table 3
)
update public.table1
    set hstore = hstore('new_key_1',foo.new_value_1),
        hstore = hstore('new_key_2',foo.new_value_2)
    where id = foo.id

I am getting a error where I cannot call the same row in a update twice in a row. How would can i insert into the hstore two different values I am selecting in the foo statement above as values with a new key into a hstore of columns that already exist. I just need to tag the rows so even if the values change the original values are stored for future issue resolution.

3

you can concatenate them:

set hstore_col = hstore('new_key_1',foo.new_value_1)||hstore('new_key_2',foo.new_value_2)

Alternatively use the constructor function that accepts two arrays:

set hstore_col = hstore(array['new_key_1', 'new_key_2'],array[foo.new_value_1, foo.new_value_2])

The second solution will be easier to extend when you have more keys then just two

If you don't want to overwrite the current value, then append that to the original one:

set hstore_col = hstore_col || hstore('new_key_1',foo.new_value_1)||hstore('new_key_2',foo.new_value_2)

or

set hstore_col = hstore_col || hstore(array['new_key_1', 'new_key_2'],array[foo.new_value_1, foo.new_value_2])
  • With these options will these save my current key/value pairs currently in the hstore. I want to make sure everything already in the hstore are already preserved. – Kyle K Mar 7 '17 at 15:57
  • @KyleK: just append them to the current value. See my edit – a_horse_with_no_name Mar 7 '17 at 16:21

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.