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

Question

I am using Postgres 9.1 and have two tables (tab1 and tab2). I wish to create a third table (tab3) where each column is the difference between respective columns in these tables, i.e. tab3.col1 = (tab1.col1 - tab2.col1). However my tables tab1 and tab2 have a large number columns. Is there an efficient way to create table tab3?

If I were to hard code my desired output I would plan to use the code below. However I wish to avoid this as I have over 60 columns to create and want to avoid any hard-coding errors. The columns may not be in the same order across the two tables, however the naming is consistent across tables.

CREATE TABLE tab3 AS
  SELECT a.col1_01 - b.col2_01 AS col3_01,
         a.col1_02 - b.col2_02 AS col3_02,
         ...
         ...
   FROM tab1 a FULL JOIN tab2 b USING (permno, datadate);

No, there's no convenient shorthand for this. – Craig Ringer Mar 10 '14 at 04:47 — Craig Ringer, Mar 10 '14 at 04:47
Not to worry. Thanks for letting me know – malligator Mar 10 '14 at 05:35 — malligator, Mar 10 '14 at 05:35

score 1 · Accepted Answer · edited May 23 '17 at 10:26

You can build the whole statement from information in the system catalog (or information schema) and execute it dynamically with a DO command. That's what I would do.

DO
$do$
BEGIN

EXECUTE (
SELECT 'CREATE TABLE tab3 AS
SELECT '
|| string_agg(format('a.%1$I - b.%1$I AS %1$I', attname)
            , E'\n     , ' ORDER BY attnum)
|| '
FROM   tab1      a
FULL   JOIN tab2 b USING (permno, datadate)'
FROM   pg_attribute
WHERE  attrelid = 'tab1'::regclass
AND    attnum > 0        -- exclude system columns (neg. attnum)
AND    NOT attisdropped  -- no dropped (dead) columns
);

END
$do$;

Assuming that tab1 and tab2 are visible in your search_path.

Produces and executes what you requested exactly. Just replace dummy table and column names with your real names.

Read about format() and string_agg() in the manual.

More information in these related answers:
Table name as a PostgreSQL function parameter
Prepend table name to each column in a result set in SQL? (Postgres specifically)

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

1 Answers1