Geek Answers Handbook

Hstore aggregate column in PostreSQL

I have a table like this:

Table "public.statistics" id | integer | not null default nextval('statistics_id_seq'::regclass) goals | hstore |

elements:

 |id |goals | |30059 |"3"=>"123" | |27333 |"3"=>"200", "5"=>"10" |

What do I need to do to aggregate all values ​​using a key in a hash?

I want to get the result as follows:

 select sum(goals) from statistics

return

 |goals | |"3"=>"323", "5"=>"10" |
+6
source share
2 answers

Based on Lawrence, answer here in a clean SQL way to aggregate summed key / value pairs into a new hstore using the array_agg constructor and hstore(text[], text[]) .

http://sqlfiddle.com/#!1/9f1fb/17

 SELECT hstore(array_agg(hs_key), array_agg(hs_value::text)) FROM ( SELECT s.hs_key, sum(s.hs_value::integer) FROM ( SELECT (each(goals)).* FROM statistics ) as s(hs_key, hs_value) GROUP BY hs_key ) x(hs_key,hs_value)

I also replaced to_number simple integer conversion and simplified the iteration of the key / value.

+11
source

There may be ways to do this to avoid a numerical conversion, but this should do the job:

 SELECT key, Sum(to_number(value, '999999999999')) FROM ( SELECT (each(goals)).key, (each(goals)).value FROM public.statistics ) as s Group By key

http://sqlfiddle.com/#!1/eb745/10/0

This is a big smell that Postgres doesn't want to bend in this way, but:

 create table test (id int, goals hstore); Insert Into Test(id, goals) Values (30059, '3=>123'); Insert Into Test(id, goals) Values (27333, '3=>200,5=>10'); Create Function hagg() returns hstore As 'Declare ret hstore := ('''' :: hstore); i hstore; c cursor for Select hstore(key, (x.Value::varchar)) From (Select key, Sum((s.value::int)) as Value From (Select (each(goals)).* From Test) as s Group By key) as x; BEGIN Open c; Loop Fetch c into i; Exit When Not FOUND; ret := i || ret; END LOOP; return ret; END' Language 'plpgsql';

I could not get the sql script to accept a body with multiple lines, in real postgres you should be able to quote $$ from this and break it down a bit.

http://sqlfiddle.com/#!1/e2ea7/1/0

+4
source

More articles:


All Articles