Can I use aggregate functions on PostgreSQL HStore values?

5
votes

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.

1
databasepostgresql

1 Answers

14
votes

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