PostgreSQL – Dynamically Convert hstore Keys to Columns

dynamic-sqlhstorepivotpostgresql

I have a database that stores a bunch of custom fields using hstore. In order to merge it into another database that doesn't support hstore, I'd like to split the keys into extra columns.

Users can add new custom fields and so I can't rely on knowledge of the keys ahead of time. Which makes the answer at "Attributes from an hstore-column as separate columns in a view?" not applicable for my problem.

Where a record doesn't have a key present in other records, it should get the same column with a null value.

How do I do this?

Best Answer

This can be done, very efficiently, too. Not in a single statement, though, since SQL demands to know the return type at call time. So you need two steps. The solution involves a number of advanced techniques ...

Assuming the same table as @Denver in his answer:

CREATE TABLE hstore_test (
  id serial PRIMARY KEY
, hstore_col hstore
);

Solution 1: Simple SELECT

After I wrote the crosstab solution below it struck me that a simple "brute force" solution is probably faster. Basically, the query @Denver already posted, built dynamically:

Step 1a: Generate query

SELECT format(
     'SELECT id, h->%s
      FROM  (SELECT id, hstore_col AS h FROM hstore_test) t;'
    , string_agg(quote_literal(key) || ' AS ' || quote_ident(key), ', h->')
   ) AS sql 
FROM  (
   SELECT DISTINCT key
   FROM   hstore_test, skeys(hstore_col) key
   ORDER  BY 1
   ) sub;

The subquery (SELECT id, hstore_col AS h FROM hstore_test) is just to get in the column alias h for your hstore column.

Step 1b: Execute query

This generates a query of the form:

SELECT id, h->'key1' AS key1, h->'key2' AS key2, h->'key3' AS key3
FROM  (SELECT id, hstore_col AS h FROM hstore_test) t;

Result:

 id | key1  | key2  | key3
----+-------+-------+-------
  1 | val11 | val12 | val13
  2 | val21 | val22 |
  3 |       |       |        -- for a row where hstore_col IS NULL


Solution 2: crosstab()

For lots of keys this may perform better. Probably not. You'll have to test. Result is the same as for solution 1.

You need the additional extension tablefunc which provides the crosstab() function. Read this first if you are not familiar:

Step 2a: Generate query

SELECT format(
   $s$SELECT * FROM crosstab(
     $$SELECT h.id, kv.*
       FROM   hstore_test h, each(hstore_col) kv
       ORDER  BY 1, 2$$
   , $$SELECT unnest(%L::text[])$$
   ) AS t(id int, %s text);
   $s$
 , array_agg(key)  -- escapes strings automatically
 , string_agg(quote_ident(key), ' text, ')  -- needs escaping!
   ) AS sql 
FROM  (
   SELECT DISTINCT key
   FROM   hstore_test, skeys(hstore_col) key
   ORDER  BY 1
   ) sub;

Note the nested levels of dollar-quoting.

I use this explicit form in the main query instead of the short CROSS JOIN in the auxiliary query to preserve rows with empty or NULL hstore values:

LEFT   JOIN LATERAL each(hstore_col) kv ON TRUE

Related:

Step 2b: Execute query

This generates a query of the form:

SELECT * FROM crosstab(
     $$SELECT h.id, kv.*
       FROM   hstore_test h
       LEFT   JOIN LATERAL each(hstore_col) kv ON TRUE
       ORDER  BY 1, 2$$
   , $$SELECT unnest('{key1,key2,key3}'::text[])$$
   ) AS t(id int, key1 text, key2 text, key3 text);

You may want to inspect it for plausibility before running the first time. This should deliver optimized performance.

Notes

Related Question