How to query hstore tag keys that match part of the filter string

Question

In PostgreSQL query, how can I filter the results by using any record that tags with a key like %building%

something like:

..
where 
    Table.tags ? '%building%'

I didn't find an answer within the postgresql documentation (https://www.postgresql.org/docs/9.6/static/hstore.html)

Answer 1

You need to create a set of the keys in order to be able to compare them using like.

Something like:

select *
from the_table t
where exists (select * 
              from skeys(t.tags) as x(tagname)
              where x.tagname like '%building%');

But this is not going to be fast.