Compute shared hstore key names in Postgresql

Question

If I have a table with an HSTORE column:

CREATE TABLE thing (properties hstore);

How could I query that table to find the hstore key names that exist in every row.

For example, if the table above had the following data:

               properties                    
-------------------------------------------------
"width"=>"b", "height"=>"a"
"width"=>"b", "height"=>"a", "surface"=>"black"
"width"=>"c"

How would I write a query that returned 'width', as that is the only key that occurs in each row?

skeys() will give me all the property keys, but I'm not sure how to aggregate them so I only have the ones that occur in each row.

Answer 1

The manual gets us most of the way there, but not all the way... way down at the bottom of http://www.postgresql.org/docs/8.3/static/hstore.html under the heading "Statistics", they describe a way to count keys in an hstore.

If we adapt that to your sample table above, you can compare the counts to the # of rows in the table.

SELECT key
    FROM (SELECT (each(properties)).key FROM thing1) AS stat
    GROUP BY key
        HAVING count(*) = (select count(*) from thing1)
    ORDER BY key;

If you want to find the opposite (all those keys that are not in every row of your table), just change the = to < and you're in business!