CODEWITHSUNDEEP
postgresqlpsql
Justin K
Justin K

Reputation: 2694

Show query result column types (PostgreSQL)

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?

Upvotes: 44

Views: 33735

Answers (4)

Andy
Andy

Reputation: 8672

In Postgres 16+, It's possible to get the column types without even running the query by preparing it and then inspecting pg_prepared_statements:

postgres=# prepare test as select 1::int, '{2,3}'::int[];
postgres=# select result_types from pg_prepared_statements where name = 'test';
┌─────────────────────┐
│    result_types     │
├─────────────────────┤
│ {integer,integer[]} │
└─────────────────────┘
(1 row)

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175766

It is definitely possible with \gdesc command(psql 11):

\gdesc

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[]

Upvotes: 17

Jay Taylor
Jay Taylor

Reputation: 13562

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!

Upvotes: 66

Konrad Garus
Konrad Garus

Reputation: 54025

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"):

  1. create table my_table as select ...
  2. \d my_table
  3. select * from my_table

Upvotes: 8

Related Questions