PostgreSQL 9.2 – Extract All Tags from hstore Field

hstorepostgresqlpostgresql-9.2

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.

Best Answer

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.

Related Question