查看元数据信息

通过字段名称查找中文描述信息

postgre测试通过，gp不清楚

select
    a.attnum,
    (
    select
        description
    from
        pg_catalog.pg_description
    where
        objoid = a.attrelid
        and objsubid = a.attnum) as descript ,
    a.attname,
    pg_catalog.format_type(a.atttypid,
    a.atttypmod) as data_type
from
    pg_catalog.pg_attribute a
where
    1 = 1
    and a.attrelid =(
    select
        oid
    from
        pg_class
    where
        relname = 'room' )  --表名称
    and a.attnum>0
    and not a.attisdropped
order by
    a.attnum;

GP通过字段名称查找中文描述信息

SELECT
    'tablename' AS TABLE_NAME,
    A .attname AS COLUMN_NAME,
    d.description AS description
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 = 'ods_mysql_work_order' :: regclass
ORDER BY
    A .attnum;

创建测试数据

创建100万条测试数据

select i,'text:'||i as text,i*10 as a into poc.test from generate_series(1,10) as i;
insert into poc.test(i,a,text) SELECT generate_series(1,1000000) as key,(random()*100.)::numeric(4,2),repeat('1',(random()*25)::integer) ORDER BY random();