Postgres result column types
There is a function in postgres pg_typeof
that 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. I am wondering how to perform a similar technique on an empty table. If there is things
nothing in the table , the previous command returns no rows. From one query, I want to see the column types of the query result. Note that I am not asking for getting the column types for the table. I already know about information_schema.columns
. Imagine a query that won't appear neatly in table rows or situations with a lot of FK constraints. I don't want to create dummy data to do the above technique. If anyone knows a way to do this (and I know F # has a library with some magic to do this), I would appreciate it. If the question is unclear please let me know how I could improve it. Thank.
source to share
Since the value has a type (inferred by the planner) even when its value is NULL, you can create a dummy string containing only NULLs and check for NULL type. This can be done with UNION
, where the column types are determined based on the first (empty) result set and the second result set just has the correct number of NULLs.
Here's an example I just tested on SQLFiddle ; more complex examples might require additional subqueries for empty results to be UNIONed (you couldn't use it LIMIT 0
here, for example), but note that it works on the return type of the function as well as 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 note on the results on the SQLFiddle is similar to what happens when it encounters a column type that it doesn't know how to display.)
source to share