Select column name and value from table

Question

If I have the following table in a PostgreSQL database:

Col1    Col2    Col3    

A       a       1       
B       b       2       

Is there a way to get the column name for each value without explicitly specifying the column names? I.e. have a result set like:

Col1    A
Col1    B
Col2    a
Col2    b
Col3    1
Col3    2

Answer 1

Of course, you could write a PL/pgSQL function and query the catalog table pg_attribute yourself. But it's so much easier with one of the following:

JSON

The function row_to_json() provides functionality that goes half the way. Introduced with Postgres 9.2:

SELECT row_to_json(t, TRUE) FROM tbl t;

You don't have to mention the column names at all, the function derives them from the table type.

->SQLfiddle demo.

But you'll need json_each_text() from Postgres 9.3 to go all the way:

SELECT json_each_text(row_to_json(t)) FROM tbl t;

To get a sort order like you display:

SELECT (json_each_text(row_to_json(t))).*
FROM   tbl t
ORDER  BY 1, 2;

(It's unclear how you want to sort exactly.)
Untested. SQLfiddle does not provide Postgres 9.3, yet.

hstore

However, you can do the same with the additional module hstore. Available since 8.4. Install it once with:

CREATE EXTENSION hstore;

Details:
Key value pair in PostgreSQL

Query:

SELECT (each(hstore(t))).*
FROM   tbl t
ORDER  BY 1,2;

That's all.
Again, no SQLfiddle, since one can't install additional modules there.