How to find if two tables contain the same set of column definitions-CodePudding

How to find if two tables contain the same set of column definitions in a postgres database. We are using a ETL tool to synchronize data into Postgres and some of the tables have huge number of columns and we would like to find out whether two tables are similar in structure. Is there a way to find this in postgres?

CodePudding user response：

SELECT    *
FROM      information_schema.columns
WHERE     column_name like '%SOMETHING%'
-- AND       table_name not like '%SOMETHING%' -- IF NEED

CodePudding user response：

You can use something like this (to customize):

select * from 
(
  SELECT    *
  FROM      information_schema.columns as c1
  where table_schema = 'schema1'
  and table_name  = 'table1'
) as a
full outer join
(
  SELECT    *
  FROM      information_schema.columns as c1
  where table_schema = 'schema2'
  and table_name  = 'table2'
) as b
on a.column_name = b.column_name
and a.data_type = b.data_type
where a.table_name is null
or b.table_name is null

You need to customize schemas and table_name. Also this query only check column name and datatype, but you can add more attribute.

If the query returns some rows, table are different.