I need to get the column data types of all columns in a table, including the geometry types. What I wanted to know is if there is a function or SQL that gives something like this:
column_name | data_type
------------+--------------
gid | integer
descr | character varying(32)
class | character varying(10)
area | double precision
geom | geometry(Polygon,3763)
From a few answers on stackexchange and gis.stackexchange I know that I can get some of the information with the following query:
SELECT
g.column_name,
g.data_type,
g.character_maximum_length,
g.udt_name,
f.type,
f.srid
FROM
information_schema.columns as g JOIN
geometry_columns AS f
ON (g.table_schema = f.f_table_schema and g.table_name = f.f_table_name )
WHERE
table_schema = 'my_schema_name' and
table_name = 'my_table_name'
Result:
column_name | data_type | character_maximum_length | udt_name | type | srid
------------+-------------------+--------------------------+----------+---------+------
gid | integer | | | |
descr | character varying | 32 | | |
class | character varying | 10 | | |
area | double precision | |
geom | USER-DEFINED | | geometry | Polygon | 3763
But, is there a proper more practical way to retrieve the information in the format I need? Or must I enter the "world" of CASE WHEN
structures and string concatenation to gather all column attributes in one single column in that format?
My fear is if a non expected data type surprises me by needing other attribute from the information_schema.columns table. I.e, in the example table before, I did not used any numeric (15,2)
data type, that would need to use another attributes (numeric_precision and numeric_scale) to be parsed by a CASE WHEN.
Best Answer
The theory yes, though you could find it very complex indeed.
But, hey, psql generates the strings you want, if we look at what SQL it generates, maybe the answer is in there.
Sure enough, there is a magic function that takes a typeid and typmod and returns the magic string.
With a join to pg_class you should be able to get this info per-table.