PG 常用命令

---------------pg 常用语句------------------
1. 查看表信息
select  * from pg_tables t where t.schemaname ='sdata';

2. 查看字段信息
select *
  from information_schema.columns t1
where t1.table_catalog ='dw'
   and t1.table_schema ='sdata'
   and t1.table_name ='user_table_name001'
  limit 10;
 
----------------------
一：查看表结构（字段）信息：
Select table_name,
       column_name,
       data_type,
       character_maximum_length
  from information_schema.columns
where table_schema='sdata'
   and table_name='user_table_name001';
   
二：查看字段注释信息：
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='user_table_name001' )
   and a.attnum>0
   and not a.attisdropped
order by a.attnum;
-----------------------------------

-- 查询所有表注释
SELECT tb.table_name,
       d.description
  FROM information_schema.tables tb
  JOIN pg_class c
    ON c.relname = tb.table_name
  LEFT JOIN pg_description d
         ON d.objoid = c.oid
        AND d.objsubid = '0'
WHERE tb.table_schema = 'sdata';
-- 查询高频字段信息
SELECT col.column_name,
       count(*)
FROM information_schema.columns col
JOIN pg_class c
   ON c.relname = col.table_name
WHERE col.table_schema = 'sdata'
  and col.table_name like '%common_coupon_info'
GROUP BY col.column_name;

-- 查询所有列注释
SELECT col.table_name,
       col.column_name,
       col.ordinal_position AS o,
       d.description,
       COALESCE(col.udt_name||'('||col.character_maximum_length||')',col.udt_name)  as udt_name2   -- 字段属性
FROM information_schema.columns col
JOIN pg_class c
   ON c.relname = col.table_name
LEFT JOIN pg_description d
        ON d.objoid = c.oid
       AND d.objsubid = col.ordinal_position
WHERE col.table_schema