current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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__';
share|improve this question

1 Answer 1

up vote 0 down vote accepted

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 n.nspname = 'table_name'  -- your table name
  and t.relname = 'public' -- table schema
  and c.attnum >= 0 -- exclude internal columns
share|improve this answer
    
The natural path I go down on my own led me to SELECT column_name, data_type, translate(udt_name,'0123456789_','') AS _column_alias_ FROM information_schema.columns WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name=''table_name'; however I'm going with your answer as you command a greater understanding of PostgreSQL, thanks! –  John Jan 27 at 15:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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