[GIS] Getting data type of each column from PostGIS table

postgis

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.

  • Every table (select * from pg_class) has columns.
  • Every column (select * from pg_attribute) optionally has a "typmod" number.
  • For types with typmod (select * from pg_type) there will be a "typmodout" function.
  • Running the typmod out function on a typmod number will return a string that can be concatenated with the type name to form the kind of user-readable signature you're used to (select 'numeric' || numerictypmodout(786441)) (select geography_typmod_out(1107460))

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.

select a.attname, format_type(a.atttypid, a.atttypmod) from pg_attribute a where attname = 'geog';

With a join to pg_class you should be able to get this info per-table.

Related Question