Show query result column types (PostgreSQL)

Question

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:

 columna : text | columnb : integer
----------------+-------------------
 oh hai         |                42

Is there a way I can get this information without coding something up?

Answer 1

It is possible to get any SELECT query result column type.

Example

Given the following query and result, let's answer the question *"What is the column type of 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".

On the postgres mailing list archives I found reference to a native pg function called pg_typeof.

Example usage:

SELECT pg_typeof(array_agg(distinct "id")) "all_ids" FROM "auth_user";

Output:

 all_ids
----------
 bigint[]
(1 row)

Cheers!