List no of columns in a table in Greenplum

Post date: Oct 19, 2014 6:22:14 PM

sachi=# select count(*) from information_schema.columns sachi-# where table_name='sales';

count

-------

5 

(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)