Jose Hermosilla Rodrigo Jose Hermosilla Rodrigo - 2 months ago 14
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
geometry
(POSTGIS) it returns
USER-DEFINED
instead of
geometry
.

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

Answer

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

data_type

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.