postgresql - New table with all columns as differences between two base tables columns -

i using postgres 9.1 , have 2 tables (tab1 , tab2). wish create third table (tab3) each column difference between respective columns in these tables, i.e. tab3.col1 = (tab1.col1 - tab2.col1). tables tab1 , tab2 have large number columns. there efficient way create table tab3?

if hard code desired output plan use code below. wish avoid have on 60 columns create , want avoid hard-coding errors. columns may not in same order across 2 tables, naming consistent across tables.

create table tab3   select a.col1_01 - b.col2_01 col3_01,          a.col1_02 - b.col2_02 col3_02,          ...          ...    tab1 full join tab2 b using (permno, datadate); 

you can build whole statement information in system catalog (or information schema) , execute dynamically do command. that's do.

do $do$ begin  execute ( select 'create table tab3 select ' || string_agg(format('a.%1$i - b.%1$i %1$i', attname)             , e'\n     , ' order attnum) || '   tab1      full   join tab2 b using (permno, datadate)'   pg_attribute  attrelid = 'tab1'::regclass ,    attnum > 0        -- exclude system columns (neg. attnum) ,    not attisdropped  -- no dropped (dead) columns );  end $do$; 

assuming tab1 , tab2 visible in search_path.

produces , executes requested exactly. replace dummy table , column names real names.

read format() , string_agg() in manual.

more information in these related answers:
table name postgresql function parameter
prepend table name each column in result set in sql? (postgres specifically)