Select data for columns that are only in another table

Question

I have two tables, table1 and table2, that have columns common to both.
The queries that obtain the column names of each table are given below,

Query to get columns from first table:

select column_name from information_schema.columns 
where table_schema = 'schema1'
and table_name = 'table1';

Query to get columns from second table:

select column_name from information_schema.columns 
where table_schema = 'schema2'
and table_name = 'table2';      

I need to select data from table2, only the columns which are also in table1.

Answer 1

I do not have a postrgesql intace at the moment but Dynamic SQL is what you need.

The following query will get you the column names that appear in both table 1 and table 2.

select string_agg(column_name, ',') FROM (
  select column_name from information_schema.columns 
  where table_schema = 'schema1'
  and table_name = 'table1'
  intersect 
  select column_name from information_schema.columns 
  where table_schema = 'schema2'
  and table_name = 'table2'
)

And you need to build

EXECUTE 'select ' || select string_agg(column_name, ',') FROM (
  select column_name from information_schema.columns 
  where table_schema = 'schema1'
  and table_name = 'table1'
  intersect 
  select column_name from information_schema.columns 
  where table_schema = 'schema2'
  and table_name = 'table2'
) || ' from schema2.table2 '

Answer 2

Sory if there any syntax error as writing from mobile app, you can join both result set to get common data.

select column_name from information_schema.columns T2
JOIN  (select column_name from information_schema.columns where table_schema = 'schema1' and table_name = 'table1') T1
ON T2.COLUMN_NAME = T1.COLUMN_NAME  Where T2.table_schema = 'schema2' and T2.table_name = 'table2';