Can I use aggregate functions for PostStreSQL HStore values?

Question

Can I execute aggregate queries on the values ​​in the hstore column? For example, I would like to define the SUMfields countin the repository.

Answer 1

Yes. But the values ​​are stored as text, so you must first apply them to the appropriate data type. So, to summarize the heights in inches that are stored in the hstore in the "other" column

CREATE TABLE my_table (
  id integer primary key,
  other hstore
);
insert into my_table values 
(1, hstore('height_in', '72') || hstore('weight_lbs', '180')),
(2, hstore('height_in', '65') || hstore('girth_in', '42'));

select sum((other->'height_in')::integer) sum_of_height
from my_table;

sum_of_height
--
137