Extract all tags from hstore field

Question

I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know what the keys or values are, I have to use wildcards.

Regretably, my secret decoder ring seems to have let me down, because nothing I've tried has worked, possibly because all my expertise is in vanilla sql and mysql/mariadb.

Many thanks for help with this.

Answer 1

There are functions for doing this, supported by the hstore extension. Depending on your exact needs, you may use akeys() or skeys(). For extracting into a file which contains the ID from the table and a comma separated list of the keys:

SELECT your_id, array_to_string(akeys(hstore_column))
FROM your_table;

Again, depending on your needs, you may wrap this into a COPY (or, in psql, a \copy) command or use your favourite client to extract the output.

Answer 2

You are looking for the skeys function:

select skeys(hstore_column)
from the_table;

If you also need the values for each key:

select (a).key, (a).value
from (
  select each(hstore_column) as a
  from the_table
) t;

More details in the manual: http://www.postgresql.org/docs/current/static/hstore.html