Add columns including their type of one table to another table (Postgres)

Question

I have two database tables

Table1

id	col1: type1	col2: type2	col3: type3
1	val1	val2	val3

Table2

id	col4: type4
1	val4

I want to dynamically add Table2 to Table1, in way that I don't only insert the value but also add the typed column:

Table1

id	col1: type1	col2: type2	col3: type3	col4: type4
1	val1	val2	val3	val4

Answer 1

This is bits and pieces from one of my projects. This query compares two tables and generates alter table script text into "todo" column.
"todo2" is update query script to update new columns with data from second table. To speed up alter table part, you can separate "default" part from "todo" and generate script for an another "alter table" script which sets only defaults after update is done.

There is no simple or elegant way to do this except maybe some third party tools.

 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
 
 

Answer 2

Are you looking for something like this?

select *
from table1 t1 join
     table2 t2
     using (id);

If you want to replace table 1 with this, I would suggest deleting table 1 and replacing it. However, I would caution that you be very careful with this approach, because it loses triggers and constraints:

create temp_table1 as 
    select *
    from table1 t1 left join
         table2 t2
         using (id);

-- Backup the table first!
delete table table1; 

alter table temp_table1 rename to table1;