Postgres types of result columns

Question

In postgres, there is a function pg_typeof which will give the type of a field. Example:

-- Original query
select name, age, descriptions from things LIMIT 1;
-- Meta query
select pg_typeof(name), pg_typeof(age), pg_typeof(descriptions) from things LIMIT 1;

     pg_typeof     | pg_typeof | pg_typeof 
-------------------+-----------+-----------
 character varying | integer   | text[]

This is a really cool feature. What I am interested in is how to perform a similar technique on an empty table. If there is nothing in the things table, the previous command returns no rows. From the query alone, I want to be able to see the types of the result columns of a query. Notice that what I am asking is not about getting the column types for a table. I already know about information_schema.columns. Envision a query that doesn't map neatly to table rows or a situation with lots a FK constraints. I don't want to have to create dummy data to make the above technique work. If anyone knows of a way to do this (and I know that F# has a library with some magic for doing this), I would be grateful to know. If the question is unclear, please let me know how I could improve it. Thanks.

IMSoP · Accepted Answer · 2014-11-22 17:45:16Z

8

Since a value has a type (deduced by the planner) even when its value is NULL, you can create a dummy row consisting only of NULLs, and test the type of the NULLs. You can do that using a UNION, where the column types are determined based on the first (empty) result set, and the second result set simply has the correct number of NULLs.

Here's an example which I just tested on SQLFiddle; more complex examples might require additional sub-queries to allow the empty result to be UNIONed (you couldn't use a LIMIT 0 here, for instance), but note that it works with a function's return type as well as the base columns:

Create Table Test ( id int, name text );

Select pg_typeof(id) as id_type, pg_typeof(name) as name_type, pg_typeof(len) as len_type
From 
(
    Select id, name, length(name) as len From test
    Union
    Select null, null, null
) as x;

(Note: the JSON notation of the results on SQLFiddle appears to be what happens when it encounters a column type it doesn't know how to display.)

answered Nov 22, 2014 at 17:45

IMSoP

99.8k18 gold badges135 silver badges183 bronze badges

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

1 Comment

Andrew Thaddeus Martin Over a year ago

Excellent answer. This reminds me of using undefined in Haskell to coerce types. I'm planning on accepting this answer, but I'll give it a day first in case anyone has a better way.

Collectives™ on Stack Overflow

Postgres types of result columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related