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.
Add a comment
|
1 Answer
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
answered Nov 18, 2011 at 23:52
Mike Sherrill 'Cat Recall'
96.6k20 gold badges134 silver badges195 bronze badges
2 Comments
RockStar
What if any one row contain value which can not be cast in integer? It will give
ERROR: invalid input syntax for integer: so how to get desired result by skipping that row?Mike Sherrill 'Cat Recall'
@RockStar: Use a WHERE clause and a regular expression:
where (other->'height_in') ~ '^\d+$'. The need to do this points to an error in your application code, though. Fix that.