How to union two tables using only their shared columns

Question

So far I've used a CTE to get a list of shared columns. The problem is is cant use the list in the select statement.

Create table churn_telco as (

WITH cols AS (
  SELECT Column_Name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE column_Name IN (
                        select COLUMN_NAME
                        from INFORMATION_SCHEMA.COLUMNS
                        where TABLE_NAME = 'churn'
                        )
  and table_Name = 'telco'
)
SELECT (SELECT column_name FROM cols)   
FROM telco
UNION ALL
Select (SELECT column_name FROM cols)   
FROM churn
)

Answer 1

It's not possible to execute dynamic SQL to create tables using pure SQL. The following PL/pgSQL block will create a new table from the union of the two source tables:

DO LANGUAGE plpgsql
$block$
DECLARE
  column_list text;
BEGIN
  SELECT string_agg(format('%I', c1.column_name), ', ' ORDER BY c1.ordinal_position)
    INTO column_list
    FROM information_schema.columns c1
    JOIN information_schema.columns c2
      ON c1.column_name = c2.column_name
    WHERE c1.table_schema = 'public'
      AND c1.table_name = 'telco'
      AND c2.table_schema = 'public'
      AND c2.table_name = 'churn';

  EXECUTE FORMAT($$CREATE TABLE churn_telco AS (SELECT %1$s FROM telco UNION ALL SELECT %1$s FROM churn)$$, column_list);
END
$block$;