Check if two tables have the same structure (are compatible) I was wondering if there is an established way to test if two PostgreSQL tables/relations have the same structure. In other words, how to test whether they are compatible with each other in the sense that I can perform set operations such as UNION ALL and EXCEPT on them?

I searched around on DBA.SE and elsewhere, and can only find questions about finding whether the contents of two tables are different, (e.g. Checking whether two tables have identical content in PostgreSQL), or when the compatibility is known (e.g. Compare two tables with same structure, but differing membership numbers). But I'm interested in checking the compatibility of table structure.

I'm using PostgreSQL 10.3, but standard compliant ways are certainly preferable.
Having example schema like:

create table table1 (
  id integer,
  txt text,
  col1 integer,
  col2 integer);

create table table2 (
  id integer,
  txt text,
  col1 text,
  colx integer);


and assuming you don't care about column names you can check potential conflicts in union-ed columns with query:

(select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table1'
except
select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table2')
union all
(select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table2'
except
select data_type, ordinal_position 
from information_schema.columns 
where table_name = 'table1'
)
order by 2;


The result will show offending column's position in tables' structure and data type.

data_type   ordinal_position
integer     3
text    3


If you have this shortened list you can check details in information_schema.columns disctionary to check details about each column.