CODEWITHSUNDEEP
postgresql
Alex Nikitin
Alex Nikitin

Reputation: 941

How to concatenate values in two hstore postgresql?

Soppose i have 2 hstores:

'"MarkingCount"=>"0","BaseCount"=>"6","EgaisCount"=>"0","MercuryCount"=>"3"':: hstore
'"MarkingCount"=>"1","BaseCount"=>"15","EgaisCount"=>"65","MercuryCount"=>"35"':: hstore

How to concatenate them usning postgresql, that results will be: '"MarkingCount"=>"1","BaseCount"=>"21","EgaisCount"=>"65","MercuryCount"=>"38"'

Operation hstore || hstore is not the answer

Upvotes: 1

Views: 218

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

Use each() to make rows of your hstore, aggregate with sum(), and then turn the result back into an hstore.

with hs(id, val) as (
  values (1, '"MarkingCount"=>"0","BaseCount"=>"6","EgaisCount"=>"0","MercuryCount"=>"3"':: hstore), 
         (2, '"MarkingCount"=>"1","BaseCount"=>"15","EgaisCount"=>"65","MercuryCount"=>"35"':: hstore)
), agg as (
  select key, sum(value::int) as value
    from hs h
   cross join lateral each(val) as e(key, value)
   group by key
)
select hstore(array_agg(key), array_agg(value::text)) as result
  from agg;

                                      result                                      
----------------------------------------------------------------------------------
 "BaseCount"=>"21", "EgaisCount"=>"65", "MarkingCount"=>"1", "MercuryCount"=>"38"
(1 row)

Upvotes: 3

Related Questions