1、查看表的索引以及索引的列信息

SELECT t.tablename, i.indexname, indisunique, indisprimary, indexdef, ai.attname, ai.attnum, ai.*
  FROM pg_tables t
  JOIN pg_namespace n
    ON t.schemaname = n.nspname
  LEFT JOIN pg_indexes i
    ON t.schemaname = i.schemaname
   AND t.tablename = i.tablename
  LEFT JOIN pg_class ci
    ON i.indexname = ci.relname
   AND n.oid = ci.relnamespace
  LEFT JOIN pg_index ii
    ON ci.oid = ii.indexrelid
  LEFT JOIN pg_attribute ai
    ON ci.oid = ai.attrelid
WHERE t.schemaname = 'xxx'
   AND t.tablename LIKE 'xxx'
ORDER BY t.tablename, i.indexname, ai.attname, ai.attnum;

2、查看表的索、对应列在一起

 SELECT t.tablename,
        i.indexname,
        indisunique,
        indisprimary,
        indexdef,
        substring(indexdef,
                  strpos(indexdef, '(') + 1,
                  strpos(indexdef, ')') - strpos(indexdef, '(') - 1) cols
   FROM pg_tables t
   JOIN pg_namespace n
     ON t.schemaname = n.nspname
   LEFT JOIN pg_indexes i
     ON t.schemaname = i.schemaname
    AND t.tablename = i.tablename
   LEFT JOIN pg_class ci
     ON i.indexname = ci.relname
    AND n.oid = ci.relnamespace
   LEFT JOIN pg_index ii
     ON ci.oid = ii.indexrelid
  WHERE t.schemaname = 'xxx'
    AND t.tablename LIKE 'xxx'
  ORDER BY t.tablename, i.indexname;

3、表注释查看

SELECT nspname, c.relname, d.description
  FROM pg_namespace s
  JOIN pg_class c
    ON s.oid = c.relnamespace
  LEFT JOIN pg_description d
    ON d.objoid = c.oid
   AND d.objsubid = '0'
 WHERE nspname IN ('schema')
   AND c.relkind = 'r'
 ORDER BY nspname, c.relname, d.description;

4、列注释查看

SELECT nspname, c.relname, d.description
  FROM pg_namespace s
  JOIN pg_class c
    ON s.oid = c.relnamespace
  LEFT JOIN pg_description d
    ON d.objoid = c.oid
   AND d.objsubid = '0'
 WHERE nspname IN ('schema')
   AND c.relkind = 'r'
 ORDER BY nspname, c.relname, d.description;

5、重要伪列或系统列

--重要伪列或系统列
--ctid:块编号（通常8k），块内行偏移量
--xc_node_hash：分布式集群的节点hash信息
--xmin：表示插入该行的事务ID（xid）
--xmax：表示更新或删除该行的事务ID（要在MVCC下理解，如果不为0，说明当前的tuple正在被删除或更新，只是事务还没有提交，如果已经提交完，这个版本的tuple就查询不到了）
--xmin、xmax是判断事务可见性、事务隔离的关键列，xid是递增的整数，用于唯一标识
--tableoid：表的oid
select ctid,xc_node_hash,xmin,xmax,tableoid,* from htap_test1 where id >(8000000-1) ;
select txid_current();--获取当前事务id

6、表的列信息查看

--查看表的列信息，只能看有权限的
SELECT * FROM db_tab_columns WHERE owner = 'username';

--查看表的列信息，只能看有权限的
SELECT * FROM information_schema.columns WHERE table_schema IN ('xxx');

--基于information_schema.columns  查看系统表，避免权限控制
SELECT nc.nspname AS table_schema,
       c.relname AS table_name,
       a.attname AS column_name,
       a.attnum AS ordinal_position,
       (CASE
         WHEN (t.typtype = 'd' ::"char") THEN
          CASE
            WHEN ((bt.typelem <> (0) ::OID) AND (bt.typlen = (-1))) THEN
             'ARRAY' ::text
            WHEN (nbt.nspname = 'pg_catalog' ::NAME) THEN
             format_type(t.typbasetype, NULL ::INTEGER)
            ELSE
             'USER-DEFINED' ::text
          END
         ELSE
          CASE
            WHEN ((t.typelem <> (0) ::OID) AND (t.typlen = (-1))) THEN
             'ARRAY' ::text
            WHEN (nt.nspname = 'pg_catalog' ::NAME) THEN
             format_type(a.atttypid, NULL ::INTEGER)
            ELSE
             'USER-DEFINED' ::text
          END
       END) AS data_type,
       information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*),
                                              information_schema._pg_truetypmod(a.*, t.*)) AS character_maximum_length
  FROM pg_attribute a
  LEFT JOIN pg_attrdef ad
    ON a.attrelid = ad.adrelid
   AND a.attnum = ad.adnum
  JOIN pg_class c
    ON a.attrelid = c.oid
  JOIN pg_namespace nc
    ON c.relnamespace = nc.oid
  JOIN pg_type t
    ON a.atttypid = t.oid
  JOIN pg_namespace nt
    ON t.typnamespace = nt.oid
  LEFT JOIN(pg_type bt
  JOIN pg_namespace nbt
    ON bt.typnamespace = nbt.oid) ON t.typtype = 'd' AND t.typbasetype = bt.oid
  LEFT JOIN(pg_collation co
  JOIN pg_namespace nco
    ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog' OR co.collname <> 'default')
 WHERE NOT pg_is_other_temp_schema(nc.oid)
   AND a.attnum > 0
   AND NOT a.attisdropped
   AND c.relkind = ANY(ARRAY [ 'r', 'm', 'v', 'f' ])
   AND c.relname NOT LIKE 'mlog\_%'
   AND c.relname NOT LIKE 'matviewmap\_%'
   AND nc.nspname IN ('xxx');