How to get columns data types from arbitrary SQL select?

Question

I need to obtain column types from arbitrary SQL query. It's certainly possible, PgAdmin3 does it.

For example, given a query:

SELECT src.col, CAST(src.col AS varchar)
FROM (SELECT 'anything' AS col) AS src;

PgAdmin3 returns:

As you can see, column types are displayed basing on executed query, not on table schema. How to obtain data types of columns in the result set programmatically?

I think this is the same question answered here. Does the query in that post help you out? — Jeanne Boyarsky
– Jeanne Boyarsky, Commented Nov 28, 2013 at 17:31
@JeanneBoyarsky Not really. This accesses information schema which, AFAIU, stores information on persisted columns. I don't think I can use it to learn that some COUNT(*) is a bigint neither learn about applied type conversions. Or maybe I don't get purpose of information schema? — skalee
– skalee, Commented Nov 28, 2013 at 17:36
Oh. So you want something like describe but for queries rather than tables. If you are using a calling language like Java there are APIs to get this info from the metadata of the query. I don't know how to do it at the command line (raw sql) — Jeanne Boyarsky
– Jeanne Boyarsky, Commented Nov 28, 2013 at 18:12
@JeanneBoyarsky I'm using Ruby's ActiveRecord, yes, this approach sounds interesting. I got to investigate, although I was pretty sure it can be done with raw SQL. — skalee
– skalee, Commented Nov 28, 2013 at 18:25
the better solution is in the other answer: stackoverflow.com/a/25369205/3494126 — Ufos
– Ufos, Commented Aug 9, 2017 at 8:59

Erwin Brandstetter · Accepted Answer · 2013-11-28 17:46:19Z

3

Use the system catalog information function pg_typeof().

answered Nov 28, 2013 at 17:46

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

skalee Over a year ago

Yes, it does the job. I'm going to approve unless someone provides simpler answer, that is one which works like EXPLAIN … where you pass whole query without modifying SELECT clause. I think that would be useful when SELECT clause is * like in SELECT src.* FROM (SELECT CAST('wow' AS varchar) AS col) AS src;.

Daniel Vérité · Accepted Answer · 2013-11-29 00:03:08Z

The corresponding function in the C client library is PQftype. It provides the type of any field from a query, as described in Retrieving Query Result Information, with other functions that apply on result fields, such as PQftable or PQfsize.
No EXPLAIN is needed, this information is shipped with every set of result coming back to a SQL client.

PQftype
Returns the data type associated with the given column number.  
The integer returned is the internal OID number of the type. Column numbers start at 0.
   Oid PQftype(const PGresult *res,
               int column_number);
You can query the system table pg_type to obtain the names and properties of the various data types. The OIDs of the built-in data types are defined in the file src/include/catalog/pg_type.h in the source tree.

Additionally the OIDs of all types of a database can be obtained with:

select oid, typname from pg_type;

Other languages generally base their postgres support on libpq library, so they can call this function. They may or may not expose it depending on how complete their implementation is.

In the case of ruby, the ruby-pg driver apparently does it through PGResult's ftype(column_number) instance method. I don't know if this is of any help with ActiveRecord. Database-agnostic APIs tend to leave out functions that can't be easily abstracted across all the databases they support.

Collectives™ on Stack Overflow

How to get columns data types from arbitrary SQL select?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related