如何比较PostgreSQL表的定义(compare table ddl)

3 minute read

背景

一位网友提到的需求, 在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)  

Flag Counter

digoal’s 大量PostgreSQL文章入口