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?
Get the per-row number of keys of hstore data in postgresql
3.2k Views Asked by Funkwecker At
2
There are 2 best solutions below
2

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;