查看Postgresql表名、主键、列名

Postgresql有很多系统表，可以查主键、列名、类型、备注、索引等。
参考:https://www.yiibai.com/manual/postgresql/catalogs.html

为了配置datax通用脚本，用到这些，记录一下：

select table_schema,table_name,concat('s01_',table_name) as target_table,t.colname,string_agg(column_name,',') as COLS 
from information_schema.columns
LEFT JOIN (select pg_class.relname as tablename,pg_attribute.attname as colname from
pg_constraint inner join pg_class
on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum = pg_constraint.conkey[1]
where pg_constraint.contype='p') t
on table_name=t.tablename
where TABLE_NAME = 'mem_base_info'
group by table_schema,table_name,t.colname;

结果：