260
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源碼, 看看能不能找到更好的方法.
最後更新：2017-04-01 13:37:07