我可以在PostgreSQL HStore值上使用聚合函数吗？

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.

我可以对hstore列中的值执行聚合查询吗？例如，我想确定商店中计数字段的SUM。

1 个解决方案

#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"

是。但值存储为文本，因此您必须首先将它们转换为适当的数据类型。因此，以英寸为单位加总高度，这些高度存储在“其他”列中的hstore中

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