将包含一个表类型的列添加到另一个表（Postgres）

Question

我有两个数据库表

Answer 1

这是我的一个项目的点点滴滴。此查询比较两个表并将alter table脚本文本生成到“TODO”列中。
“TODO2”是更新查询脚本，用第二个表中的数据更新新列。为了加快alter table部分，您可以将“Default”部分与“Todo”分离，并为另一个“alter table”脚本生成脚本，该脚本在更新完成后仅设置默认值。

除了一些第三方工具之外，没有简单或优雅的方法可以做到这一点。

 select string_agg(todo,'') as todo
       ,'update mytable1 set '|| string_agg(todo2,',') ||' from mytable2 where mytable1.id= mytable2.id'  as todo2
   from (
select  
       case 
         when table1.attname is null 
         then 'alter table '||'mytable1'||' add column '||table2.attname||' '||table2.datatype||' '||case when table2.default_value is null or table2.default_value ='' then ' ' else ' default ' || table2.default_value end ||    ';' 
         end  as todo
      ,case
         when table1.attname is null 
         then  ' '|| table2.attname ||'='|| 'mytable2.'||table2.attname
       end as todo2
 
 from 
 (
 SELECT attname 
      ,pg_catalog.format_type(a.atttypid, a.atttypmod) as Datatype 
      ,coalesce(d.adsrc,'') AS default_value
  FROM pg_catalog.pg_attribute a 
  LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)=(d.adrelid,d.adnum)
 WHERE a.attnum > 0 AND NOT a.attisdropped
   AND a.attrelid = ( SELECT c.oid
                       FROM pg_catalog.pg_class c 
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
                      WHERE c.relname = 'mytable1' AND pg_catalog.pg_table_is_visible(c.oid)
                    )
 )table1
 full join  
  (
 SELECT attname 
      ,pg_catalog.format_type(a.atttypid, a.atttypmod) as Datatype 
      ,coalesce(d.adsrc,'') AS default_value
  FROM pg_catalog.pg_attribute a 
  LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum)=(d.adrelid,d.adnum)
 WHERE a.attnum > 0 AND NOT a.attisdropped
   AND a.attrelid = ( SELECT c.oid
                       FROM pg_catalog.pg_class c 
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
                      WHERE c.relname = 'mytable2' AND pg_catalog.pg_table_is_visible(c.oid)
                    )
 )table2 on (table1.attname = table2.attname)
 where table1.attname is null) q1