List no of columns in a table in Greenplum

posted Apr 28, 2017, 10:39 AM by Sachchida Ojha
sachi=# select count(*) from information_schema.columns sachi-# where table_name='sales'; 
count 
------- 
(1 row)


sachi=# SELECT DISTINCT a.attnum as num, a.attname as name, format_type(a.atttypid, a.atttypmod) as typ, a.attnotnull as notnull, com.description as comment, coalesce(i.indisprimary,false) as primary_key, def.adsrc as default FROM pg_attribute a JOIN pg_class pgc ON pgc.oid = a.attrelid LEFT JOIN pg_index i ON (pgc.oid = i.indrelid AND i.indkey[0] = a.attnum) LEFT JOIN pg_description com on (pgc.oid = com.objoid AND a.attnum = com.objsubid) LEFT JOIN pg_attrdef def ON (a.attrelid = def.adrelid AND a.attnum = def.adnum) WHERE a.attnum > 0 AND pgc.oid = a.attrelid AND pg_table_is_visible(pgc.oid) AND NOT a.attisdropped AND pgc.relname = 'sales' ORDER BY a.attnum;
num | name | typ | notnull | comment | primary_key | default 
-----+--------+---------+---------+---------+-------------+---------
1 | id | integer | f | | f | 
2 | year | integer | f | | f | 
3 | month | integer | f | | f | 
4 | day | integer | f | | f | 
5 | region | text | f | | f | 
(5 rows)



sachi=# SELECT a.attnum ,a.attname AS name ,format_type(a.atttypid, a.atttypmod) AS typ ,a.attnotnull AS notnull ,coalesce(p.indisprimary, FALSE) AS primary_key ,f.adsrc AS default_val ,d.description AS col_comment FROM pg_attribute a LEFT JOIN pg_index p ON p.indrelid = a.attrelid AND a.attnum = ANY(p.indkey) LEFT JOIN pg_description d ON d.objoid = a.attrelid AND d.objsubid = a.attnum LEFT JOIN pg_attrdef f ON f.adrelid = a.attrelid AND f.adnum = a.attnum WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = 'public.foo'::regclass ORDER BY a.attnum;
attnum | name | typ | notnull | primary_key | default_val | col_comment 
--------+------+-----------------------------+---------+-------------+-------------+-------------
1 | x | timestamp without time zone | f | f | | 
(1 row)
Comments