Transposing columns to rows in Postgres

Question

I currently have a one row settings column in Postgres with each column representing a setting:

setting1 | setting2 | setting3 | ...
------------------------------------
true     | false    | 17       | ...

I need to transpose this in to a table with key / value rows:

key      | value
----------------
setting1 | true
setting2 | false
setting3 | 17
...      | ...

I've seen similar questions here, but not this exact problem. Can anyone help?

Daniel Vérité · Accepted Answer · 2014-03-14 12:15:46Z

1

Assuming you want to avoid enumerating the columns in the query, it can be done with the hstore extension:

SELECT (each(hstore(tablename.*))).* FROM tablename;

Note that the values are casted to the text type.

answered Mar 14, 2014 at 12:15

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Unfortunately I'm runnig 8.4 which doesn't have hStore module installed

It's available in 8.4 as a contrib module: postgresql.org/docs/8.4/static/hstore.html