updating a hstore with multiple new columns

Question

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.

Answer 1

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])