Can I use aggregate functions on PostgreSQL HStore values?

Question

Can I perform aggregate queries on values in an hstore column? For example, I'd like to determine the SUM of a count field in the store.

Answer 1

Yes. But values are stored as text, so you have to cast them to an appropriate data type first. So, to sum heights in inches, which are stored in an hstore in the column "other"

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