CODEWITHSUNDEEP
postgresql
Rpj
Rpj

Reputation: 6080

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

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?

Upvotes: 0

Views: 52

Answers (2)

user_0
user_0

Reputation: 3363

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.

Upvotes: 1

Nickname_used
Nickname_used

Reputation: 428

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

Upvotes: 1

Related Questions