PostgreSQL create table like/as创建新表pg_attribute.attndims变为0
PostgreSQL 数据库对于包含数组类型的表，通过 create table like/as 语法创建的新表与原表结构不一致，新表数组字段的 pg_attribute.attndims 值为 0，原表不为 0。
有些场景下需要对比两个表的结构是否一致时，需要注意这个问题。
示例如下：
postgres=# \d test1;
Table "public.test1"
Column | Type | Modifiers
--------+-----------+-----------
id | integer |
datas | integer[] |
d2 | integer[] |
d3 | integer[] |
postgres=# create table test2 (like test1);
CREATE TABLE
postgres=# \d test2;
Table "public.test2"
Column | Type | Modifiers
--------+-----------+-----------
id | integer |
datas | integer[] |
d2 | integer[] |
d3 | integer[] |
postgres=# select att.attname, att.attndims from pg_attribute att, pg_class
c where c.relname='test1' and att.attrelid = c.oid;
attname | attndims
----------+----------
tableoid | 0
cmax | 0
xmax | 0
cmin | 0
xmin | 0
ctid | 0
id | 0
datas | 1
d2 | 2
d3 | 3
postgres=# select att.attname, att.attndims from pg_attribute att, pg_class
c where c.relname='test2' and att.attrelid = c.oid;
attname | attndims
----------+----------
tableoid | 0
cmax | 0
xmax | 0
cmin | 0
xmin | 0
ctid | 0
id | 0
datas | 0
d2 | 0
d3 | 0
(10 rows)
postgres=# create table test3 as select * from test1;
SELECT 1
postgres=# \d test3;
Table "public.test3"
Column | Type | Modifiers
--------+-----------+-----------
id | integer |
datas | integer[] |
d2 | integer[] |
d3 | integer[] |
postgres=# select att.attname, att.attndims from pg_attribute att, pg_class
c where c.relname='test3' and att.attrelid = c.oid;
attname | attndims
----------+----------
tableoid | 0
cmax | 0
xmax | 0
cmin | 0
xmin | 0
ctid | 0
id | 0
datas | 0
d2 | 0
d3 | 0
(10 rows)
参考资料：
文章评论
共0条评论