Search code examples
sqlpostgresqlaggregate-functionshstore

What is the maximum number of key value pairs in a single hstore value?

I've got a postgres table (mapfeatures_20120813) with 2 columns (tags and pky) with around 1000 rows. Each row consists of a hstore and a primary key:

   tags (hstore)          pky
"aerialway"=>"cable_car";  1
"aerialway"=>"chair_lift"; 2
"aerialway"=>"drag_lift";  3
"aerialway"=>"gondola";    4
"aerialway"=>"goods";      5
"aerialway"=>"mixed_lift"; 6
"aerialway"=>"pylon";      7
"aerialway"=>"station";    8
"aeroway"=>"aerodrome";    9
"aeroway"=>"apron";        10
...

For some analysis I need to push all these single hstore key-value-pairs into one single hstore -row and I'm not sure how to solve this.
Therefore I first convert all the rows into a single-row text-field: 

CREATE TABLE mf_text AS
SELECT array_to_string(array_agg(tags), ',')
FROM mapfeatures_20120813;

In a second step I create a hstore out of this text-field: 

SELECT hstore(array_to_string)
FROM mf_text

But the problem is that only 97 of the more than 1000 key-value-pairs are written into the new hstore-field. Also I can't see any pattern in my result, it's totally mixed up: 

"atv"=>"no", "hgv"=>"forestry", "lit"=>"no", "psv"=>"private"
, "area"=>"yes", "boat"=>"permissive"

Is there some sort of limitation to an hstore-field on how many key-value-pairs fit into a single hstore? The documentation doesn't say anything.

Solution

  • Aggregating a single hstore value should be as simple as:

    SELECT string_agg(tags::text,',')::hstore
FROM   mapfeatures_20120813;

    As to your question in the title: there is practically no limit to the number of elements.

    For your remark:

    Also I can't see any pattern in my result, it's totally mixed up:

    The manual has this to say:

    The order of the pairs is not significant (and may not be reproduced on output).