PostgreSQL如何比較兩個表的定義是否一致

一位網友提到的需求, 在PostgreSQL中如何比對兩個表的定義差異.
如果只比對欄位型別, 不比對約束, 觸發器, 策略, 許可權等其他屬性的話, 只需要使用pg_attribute這個catalog即可.
例子 : 
建立兩個測試表, 
postgres=# create table tbl1 (id int, info text, c1 numeric(10,3), c2 timestamp without time zone);
CREATE TABLE
postgres=# create table tbl2 (id int, info text, c0 int, c00 int, c1 numeric(10,3), c2 timestamp with time zone);
CREATE TABLE

postgres=# alter table tbl2 drop column c00;
ALTER TABLE

postgres=# alter table tbl2 add column c00 int;
ALTER TABLE
postgres=# alter table tbl2 add column c01 int;
ALTER TABLE

當前結構
postgres=# d tbl1
               Table "public.tbl1"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 id     | integer                     | 
 info   | text                        | 
 c1     | numeric(10,3)               | 
 c2     | timestamp without time zone | 

postgres=# d tbl2
              Table "public.tbl2"
 Column |           Type           | Modifiers 
--------+--------------------------+-----------
 id     | integer                  | 
 info   | text                     | 
 c0     | integer                  | 
 c1     | numeric(10,3)            | 
 c2     | timestamp with time zone | 
 c00    | integer                  | 
 c01    | integer                  | 

使用這個catalog
postgres=# d pg_attribute
    Table "pg_catalog.pg_attribute"
    Column     |   Type    | Modifiers 
---------------+-----------+-----------
 attrelid      | oid       | not null
 attname       | name      | not null
 atttypid      | oid       | not null
 attstattarget | integer   | not null
 attlen        | smallint  | not null
 attnum        | smallint  | not null
 attndims      | integer   | not null
 attcacheoff   | integer   | not null
 atttypmod     | integer   | not null
 attbyval      | boolean   | not null
 attstorage    | "char"    | not null
 attalign      | "char"    | not null
 attnotnull    | boolean   | not null
 atthasdef     | boolean   | not null
 attisdropped  | boolean   | not null
 attislocal    | boolean   | not null
 attinhcount   | integer   | not null
 attcollation  | oid       | not null
 attacl        | aclitem[] | 
 attoptions    | text[]    | 
 attfdwoptions | text[]    | 
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

當前兩個表在pg_attribute中的資料如下, 系統隱含列和已刪除的列排除掉
postgres=# select attrelid,attname,atttypid,attlen,atttypmod from pg_attribute where attrelid=`tbl2`::regclass and attnum>=1 and not attisdropped;
 attrelid | attname | atttypid | attlen | atttypmod 
----------+---------+----------+--------+-----------
    24681 | id      |       23 |      4 |        -1
    24681 | info    |       25 |     -1 |        -1
    24681 | c0      |       23 |      4 |        -1
    24681 | c1      |     1700 |     -1 |    655367
    24681 | c2      |     1184 |      8 |        -1
    24681 | c00     |       23 |      4 |        -1
    24681 | c01     |       23 |      4 |        -1
(7 rows)

postgres=# select attrelid,attname,atttypid,attlen,atttypmod from pg_attribute where attrelid=`tbl1`::regclass and attnum>=1 and not attisdropped;
 attrelid | attname | atttypid | attlen | atttypmod 
----------+---------+----------+--------+-----------
    24675 | id      |       23 |      4 |        -1
    24675 | info    |       25 |     -1 |        -1
    24675 | c1      |     1700 |     -1 |    655367
    24675 | c2      |     1114 |      8 |        -1
(4 rows)

使用這個SQL就可以比對兩個表不同的欄位
with 
t1 as (
select attrelid,attname,attlen,format_type(atttypid,atttypmod) as typ from pg_attribute where attrelid=`tbl1`::regclass and attnum>=1 and not attisdropped
),
t2 as (
select attrelid,attname,attlen,format_type(atttypid,atttypmod) as typ from pg_attribute where attrelid=`tbl2`::regclass and attnum>=1 and not attisdropped
)
select t1.*,t2.* from t1 full outer join t2 on (t1.attname = t2.attname and t1.typ=t2.typ and t1.attlen=t2.attlen) where t1.* is null or t2.* is null;

 attrelid | attname | atttypid | attlen | atttypmod | attrelid | attname | atttypid | attlen | atttypmod 
----------+---------+----------+--------+-----------+----------+---------+----------+--------+-----------
    24675 | c2      |     1114 |      8 |        -1 |          |         |          |        |          
          |         |          |        |           |    24681 | c01     |       23 |      4 |        -1
          |         |          |        |           |    24681 | c00     |       23 |      4 |        -1
          |         |          |        |           |    24681 | c0      |       23 |      4 |        -1
          |         |          |        |           |    24681 | c2      |     1184 |      8 |        -1
(5 rows)

長度不同也可以比對出來
postgres=# alter table tbl1 add column n1 numeric(10,2);
ALTER TABLE
postgres=# alter table tbl2 add column n1 numeric(10,3);
ALTER TABLE

使用format_type格式化一下型別, 更友好的輸出
postgres=# with                                         
t1 as (
select attrelid,attname,attlen,format_type(atttypid,atttypmod) as typ from pg_attribute where attrelid=`tbl1`::regclass and attnum>=1 and not attisdropped
),
t2 as (
select attrelid,attname,attlen,format_type(atttypid,atttypmod) as typ from pg_attribute where attrelid=`tbl2`::regclass and attnum>=1 and not attisdropped
)
select t1.*,t2.* from t1 full outer join t2 on (t1.attname = t2.attname and t1.typ=t2.typ and t1.attlen=t2.attlen) where t1.* is null or t2.* is null;
 attrelid | attname | attlen |             typ             | attrelid | attname | attlen |           typ            
----------+---------+--------+-----------------------------+----------+---------+--------+--------------------------
    24675 | c2      |      8 | timestamp without time zone |          |         |        | 
    24675 | n1      |     -1 | numeric(10,2)               |          |         |        | 
          |         |        |                             |    24681 | c0      |      4 | integer
          |         |        |                             |    24681 | n1      |     -1 | numeric(10,3)
          |         |        |                             |    24681 | c00     |      4 | integer
          |         |        |                             |    24681 | c01     |      4 | integer
          |         |        |                             |    24681 | c2      |      8 | timestamp with time zone
(7 rows)

如果你還需要比對其他的不同之處, 例如約束, 欄位順序, 觸發器等, 建議用pg_dump將兩個表的定義匯出, 然後diff一下.
或者研究一下pg_dump原始碼, 看看能不能找到更好的方法.