PostgreSQL复制表结构的方法
1.把其他表当模板创建表
postgres=# create table baby (LIKE child);
postgres=# \d baby
Table "public.baby"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
name | character varying(20) | | |
age | integer | | |
note | text | | |
postgres=# \d child
Table "public.child"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
name | character varying(20) | | |
age | integer | | |
note | text | | |
Check constraints:
"ck_child_age" CHECK (age < 18)
注意:此处创建的表没有把源表上的约束复制过来。如果想要完全复制源表上的约束和其他信息,则要加上"INCLUDING".
INCLUDING选项
INCLUDING DEFAULTS
INCLUDING CONSTRAINTS
INCLUDING INDEXES
INCLUDING STORAGE
INCLUDING COMMENTS
INCLUDING ALL
其中 INCLUDING ALL 是把所有的属性都复制过去。
postgres=# create table baby2 (LIKE child INCLUDING ALL);
CREATE TABLE
postgres=# \d baby2
Table "public.baby2"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
name | character varying(20) | | |
age | integer | | |
note | text | | |
Check constraints:
"ck_child_age" CHECK (age < 18)
2.复制表结构的另一种方法
postgres=# create table baby3 as select * from child WITH NO DATA;
CREATE TABLE AS
postgres=# \d baby3
Table "public.baby3"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
name | character varying(20) | | |
age | integer | | |
note | text | | |