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!