CODEWITHSUNDEEP
sqlpostgresqlhstore
Funkwecker
Funkwecker

Reputation: 814

Get the per-row number of keys of hstore data in postgresql

I have a hstore column in my postgresql database. Each row of the database has a different number of keys/values in the hstore-column. How can i get the number of keys/values for each row?

Upvotes: 9

Views: 3511

Answers (2)

MatheusOl
MatheusOl

Reputation: 11825

You can use one of hstore functions, to convert hstore to another data type that can retrieve number of elements. For instance, you can use avalue to count the number of keys on a hstore value:

SELECT id, array_length(avals(my_hstore_field), 1) AS count_keys
FROM mytable;

On the docs there is a nice example to get all the keys and the number of occurrences of it that may be useful for you:

SELECT key, count(*) FROM
  (SELECT (each(h)).key FROM testhstore) AS stat
  GROUP BY key
  ORDER BY count DESC, key;

Upvotes: 4

user330315
user330315

Reputation: 

select hstore_column, 
       array_length(akeys(hstore_column), 1) as num_keys
from the_table

Upvotes: 9

Related Questions