如何比较PostgreSQL表的定义(compare table ddl)
背景
一位网友提到的需求, 在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)