PostgreSQL determine column type when data_type is set to ARRAY

Question

I came across an oddity where PostgreSQL sets the data_type to ARRAY in the information_schema.columns table. I did the following query to gain some insight:

SELECT * FROM information_schema.columns 
WHERE table_schema NOT IN ('pg_catalog', 'information_schema') 
AND table_name='__table_name__';

I noticed the udt_name column and am wondering if it is a reliable way to determine what type of array the column's data is?

SELECT column_name, data_type, udt_name FROM information_schema.columns 
WHERE table_schema NOT IN ('pg_catalog', 'information_schema') 
AND table_name='__table_name__';

Answer 1

One option is to simply cast udt_name to a regtype:

SELECT column_name, data_type, udt_name::regtype
FROM information_schema.columns 
WHERE table_schema = 'public'
  AND table_name='table_name';

Another option is to use the function format_type(), but that needs the oid of the data type:

select c.attname, pg_catalog.format_type(c.atttypid, NULL) as data_type
from pg_attribute c
  join pg_class t on c.attrelid = t.oid
  join pg_namespace n on t.relnamespace = n.oid
where t.relname = 'table_name'  -- your table name
  and n.nspname = 'public' -- table schema
  and c.attnum >= 0 -- exclude internal columns