postgresql

1.查询postgresql表结构

SELECT a.relname as 表名,
       cast(obj_description(a.relfilenode,'pg_class') as varchar) as 表描述,
       b.attname as  字段名,
       d.description as 字段描述,
       format_type(b.atttypid,b.atttypmod) as 字段类型 
FROM pg_class a                  --表、索引、序列、视图（“关系”）
    ,pg_attribute b              --表的列（也称为“属性”或“字段”）
    ,pg_type c                   --数据类型
    ,pg_description d            --数据库对象的描述或注释
WHERE 
      b.attnum > 0               --“字段计数”大于0
  AND a.oid = b.attrelid         --attrelid类型为oid,此字段所属的表
  AND b.atttypid = c.oid         --atttypid类型为oid,此字段的数据类型
  AND b.attrelid = d.objoid      --objoid类型为oid,这条描述涉及到的对象oid
  AND b.attnum = d.objsubid      --“字段计数”等于“字段号”
  AND a.relname = 'my_test'      --小写表名，问题：会显示多个模式的重复表名,待解决
  --AND a.relkind = 'f'          --外部表
ORDER BY b.attnum;               --根据“字段计数”排序

2.外部表卸数

drop external table fdm.ext_my_test;
create writable external table fdm.ext_my_test(
          like fdm.my_test
          )
          location('gpfdist://190.200.50.60:9999/data/fdm_my_test.dat')
          format 'CUSTOM' (FORMATTER=delimiter_ou_any,entry_delim='|',fix_flag='FALSE',line_delim=E'\n',null='',encoding='utf8');
          set optimizer to on;
          insert into fdm.ext_my_test select * from fdm.my_test;