CODEWITHSUNDEEP
postgresqlpostgresql-9.1hstore
jay.lee
jay.lee

Reputation: 19837

Most efficient way to retrieve a unique list of keys from all rows of an hstore?

For simplicity sake, say I have a table with a single column that is just an hstore. What is the most efficient way to go about getting a unqiue list of all the keys from all rows of the hstore?

eg.

my_hstore
------------
a=>1,b=>2
b=>2,c=>3
x=>10,y=>11
y=>11,z=12

What is the most efficient way to retrieve a list/array/set containing (a,b,c,x,y,z) ?

Upvotes: 20

Views: 7147

Answers (1)

mu is too short
mu is too short

Reputation: 434665

There's always the straight forward skeys approach:

select distinct k
from (
    select skeys(my_hstore) as k
    from your_table
) as dt

And if you need an array, then add an array_agg:

select array_agg(distinct k)
from (
    select skeys(my_hstore) as k
    from your_table
) as dt

Upvotes: 36

Related Questions