PostgreSQL-表信息查询

1、查询表信息

SELECT   tablename, obj_description(relfilenode,'pg_class')  
  FROM   pg_tables  a, pg_class b  
 WHERE  a.tablename = b.relname  
   and a.tablename NOT LIKE 'pg%'    
   AND a.tablename NOT LIKE 'sql_%'  
 ORDER BY a.tablename; 

2、查询列信息

SELECT col_description(a.attrelid,a.attnum) as comment,
       format_type(a.atttypid,a.atttypmod) as type,
       a.attname as name,
       a.attnotnull as notnull  
  FROM pg_class as c,pg_attribute as a  
 where c.relname = 'sms_statistics' 
   and a.attrelid = c.oid and a.attnum>0 ;

3、查询所有的序列seq

select relname from pg_class where relowner=(select usesysid from pg_user where usename='YOURUSERNAME') and relkind='S'

4、自动转换字段属性：

select
    t.*,
    '/**' || t.comment || '*/ private  ' || case
        when t.type like 'int%' then 'long'
        when t.type like 'character%' then 'String'
        when t.type like 'date%' then 'Date'
        when t.type like 'timestamp%' then 'Date'
        when t.type like 'numeric%' then 'BigDecimal'
    end || ' ' || substring( t.name, 1, 1 )|| substring( replace( initcap( t.name ), '_', '' ), 2, length( replace( initcap( t.name ), '_', '' )))|| ';'
from (select col_description(a.attrelid, a.attnum) as comment,
             format_type(a.atttypid, a.atttypmod) as type,
             a.attname as name,
             a.attnotnull as notnull
        from pg_class as c, pg_attribute as a
       where c.relname = 'segment_account'
         and a.attrelid = c.oid
         and a.attnum > 0
    ) as t;

5、删除列默认值及非空约束

ALTER TABLE wx_ucp_distri_detail ALTER COLUMN dating_flag DROP NOT NULL;
ALTER TABLE wx_ucp_distri_detail ALTER COLUMN dating_flag DROP DEFAULT;