本文共 6385 字，大约阅读时间需要 21 分钟。
一位网友提到的需求, 在PostgreSQL中如何比对两个表的定义差异.如果只比对字段类型, 不比对约束, 触发器, 策略, 权限等其他属性的话, 只需要使用pg_attribute这个catalog即可.例子 : 创建两个测试表, postgres=# create table tbl1 (id int, info text, c1 numeric(10,3), c2 timestamp without time zone);CREATE TABLEpostgres=# create table tbl2 (id int, info text, c0 int, c00 int, c1 numeric(10,3), c2 timestamp with time zone);CREATE TABLEpostgres=# alter table tbl2 drop column c00;ALTER TABLEpostgres=# alter table tbl2 add column c00 int;ALTER TABLEpostgres=# 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 | 使用这个catalogpostgres=# \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 TABLEpostgres=# 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源码, 看看能不能找到更好的方法.
转载地址：http://qxmra.baihongyu.com/