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;
结果: