Jose Hermosilla Rodrigo Jose Hermosilla Rodrigo - 3 months ago 28
SQL Question

How to get the right colum type name?

I need to know the column-type names of a table, and so far i'm using this query :

SELECT column_name as name,
data_type as type
FROM information_schema.columns
WHERE table_name = 'MY_TABLE';

The problem is that for data types like
(POSTGIS) it returns
instead of

Is there a way to get the right column-type name?


I cannot test this right now, but according to the documentation for information_schema.columns:


Data type of the column, if it is a built-in type, or ARRAY if it is some array (in that case, see the view element_types), else USER-DEFINED (in that case, the type is identified in udt_name and associated columns). If the column is based on a domain, this column refers to the type underlying the domain (and the domain is identified in domain_name and associated columns).

So maybe the column udt_name contains what you want.