1

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__';
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.