显示查询结果列类型 (PostgreSQL)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3267769/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Show query result column types (PostgreSQL)
提问by Justin K
Is there a way to easily get the column types of a query result? I read the psql documentation, but I don't think it supports that. Ideally, I'd be able to get something like:
有没有办法轻松获取查询结果的列类型？我阅读了 psql 文档，但我认为它不支持。理想情况下，我将能够得到类似的东西：
columna : text | columnb : integer
----------------+-------------------
oh hai | 42
Is there a way I can get this information without coding something up?
有没有办法不用编码就可以获得这些信息？
采纳答案by Konrad Garus
I don't think you can print exactly what you have in the sample, unless you write a stored procedure for it.
我认为您无法准确打印示例中的内容，除非您为其编写存储过程。
One way to do it (two "selects"):
一种方法（两个“选择”）：
create table my_table as select ...\d my_tableselect * from my_table
create table my_table as select ...\d my_tableselect * from my_table
回答by Jay Taylor
It is possible to get any SELECT query result column type.
可以获取任何 SELECT 查询结果列类型。
Example
例子
Given the following query and result, let's answer the question *"What is the column type of all_ids?"*
给定以下查询和结果，让我们回答问题*“all_ids 的列类型是什么？”*
SELECT array_agg(distinct "id") "all_ids" FROM "auth_user";
all_ids
--------------------------------------------
{30,461577687337538580,471090357619135524}
(1 row)
We need a mechanism to unveil the type of "all_ids".
我们需要一种机制来揭示“all_ids”的类型。
On the postgres mailing list archivesI found reference to a native pg function called pg_typeof.
在postgres 邮件列表档案中，我找到了对名为pg_typeof.
Example usage:
用法示例：
SELECT pg_typeof(array_agg(distinct "id")) "all_ids" FROM "auth_user";
Output:
输出：
all_ids
----------
bigint[]
(1 row)
Cheers!
干杯!
回答by Lukasz Szozda
It is definitely possible with \gdesccommand(PostgreSQL 11):
\gdesc命令（PostgreSQL 11）绝对可以：
Shows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed;however, if it contains some type of syntax error, that error will be reported in the normal way.
If the current query buffer is empty, the most recently sent query is described instead.
显示当前查询缓冲区结果的描述（即列名和数据类型）。查询并未实际执行；但是，如果它包含某种类型的语法错误，则会以正常方式报告该错误。
如果当前查询缓冲区为空，则改为描述最近发送的查询。
For example:
例如：
$ SELECT * FROM pg_database \gdesc
COLUMN | TYPE
---------------+-----------
datname | name
datdba | oid
encoding | INTEGER
datcollate | name
datctype | name
datistemplate | BOOLEAN
datallowconn | BOOLEAN
datconnlimit | INTEGER
datlastsysoid | oid
datfrozenxid | xid
datminmxid | xid
dattablespace | oid
datacl | aclitem[]
