How to do it...

First, we will show you how to identify columns that are defined in different ways in different tables, using a query against the catalog. We use an information_schema query, as follows:

SELECT
 table_schema
,table_name
,column_name
,data_type
  ||coalesce(' ' || text(character_maximum_length), '')
  ||coalesce(' ' || text(numeric_precision), '')
  ||coalesce(',' || text(numeric_scale), '')
  as data_type
FROM information_schema.columns
WHERE column_name IN
(SELECT
 column_name
FROM
(SELECT
  column_name
 ,data_type
 ,character_maximum_length
 ,numeric_precision
 ,numeric_scale
 FROM information_schema.columns
 WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
 GROUP BY
  column_name
 ,data_type
 ,character_maximum_length
 ,numeric_precision
 ,numeric_scale
) derived
GROUP BY column_name
HAVING count(*) > 1
)
AND table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY column_name
;

The query gives an output as follows:

 table_schema | table_name | column_name |   data_type  
--------------+------------+-------------+---------------
 s2           | x          | col1        | integer 32,0
 s1           | x          | col1        | smallint 16,0
(2 rows)

Comparing two given tables is more complex, as there are so many ways that the tables might be similar and yet a little different. The following query looks for all tables of the same name (and hence, in different schemas) that have different definitions:

WITH table_definition as
( SELECT table_schema
       , table_name
       , string_agg( column_name || ' ' || data_type
                   , ',' ORDER BY column_name
                   ) AS def
    FROM information_schema.columns
   WHERE table_schema NOT IN ( 'information_schema'
                             , 'pg_catalog')
   GROUP BY table_schema
          , table_name
)
  , unique_definition as
( SELECT DISTINCT table_name
       , def
    FROM table_definition
)
   , multiple_definition as
( SELECT table_name
    FROM unique_definition
   GROUP BY table_name
  HAVING count( * ) > 1
)
SELECT table_schema
     , table_name
     , column_name
     , data_type
  FROM information_schema.columns
 WHERE table_name
       IN ( SELECT table_name
              FROM multiple_definition )
 ORDER BY table_name
        , table_schema
        , column_name
;

Here is its output:

 table_schema | table_name | column_name | data_type
--------------+------------+-------------+-----------
 s1           | x          | col1        | smallint
 s1           | x          | col2        | text
 s2           | x          | col1        | integer
 s2           | x          | col3        | numeric
(4 rows)