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源码, 看看能不能找到更好的方法.