Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
As I can't uptick either of your answers, this will have to do: thanks to both!<p> –  MMacD Mar 4 at 12:37
    
As I can't uptick either of your answers, this will have to do: thanks to both of you! I found your examples much more helpful than any in the docs. In the end, I used hstore_to_matrix(), though for some reason one of the hstore fields was declared as array-of-text rather than hstore by osm2pgsql, so I had to cast, the whole statement (for anyone who might be interested in doing the same thing) being copy ( select id,hstore_to_matrix(tags::hstore) from osm_rels ) to '/osm_rels' ; –  MMacD Mar 4 at 12:47
    
@MMacD you can still mark as accepted one of these (or add your own answer, basically copying your comment, and accept that). I'm glad to hear that we could help. –  dezso Mar 4 at 14:25
    
How do I do an accept? Click that funny looking check mark, or something else? –  MMacD Mar 4 at 15:01

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.