从表中选择列名称和值 [英] Select column name and value from table

问题描述

如果我在PostgreSQL数据库中有下表：

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

推荐答案

当然，您可以编写PL / pgSQL函数并查询目录表您自己 pg_attribute 。但是，使用以下其中一项可以轻松得多：

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:

函数 row_to_json（） 提供了一半的功能。由Postgres 9.2 引入：

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演示。 / a>

->SQLfiddle demo.

但是您需要 json_each_text（） 来自Postgres 9.3 一路走下去：

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;

（尚不清楚您要如何精确排序。）

未经测试。 SQLfiddle还没有提供Postgres 9.3。

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

但是，您可以使用< a href = http://www.postgresql.org/docs/current/interactive/hstore.html#HSTORE-FUNC-TABLE rel = nofollow noreferrer>其他模块 hstore 。自 8.4 起可用。使用以下命令安装一次：

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

CREATE EXTENSION hstore;

详细信息：

PostgreSQL中的键值对

查询：

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

仅此而已。

同样，没有SQLfiddle，因为无法安装还有其他模块。

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

这篇关于从表中选择列名称和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！