postgres create table as select & create table like

1. 准备

先建立一个基础表：

create table mytb1(id serial,name character varying,age integer);

create index mytb1_name_index on mytb1(name);

查看mytb1的表结构：

postgres=# d mytb1;
                              Table "public.mytb1"
 Column |       Type        |                     Modifiers                     
 
--------+-------------------+---------------------------------------------------
 id     | integer           | not null default nextval('mytb1_id_seq'::regclass)
 name   | character varying | 
 age    | integer           | 
Indexes:
    "mytb1_name_index" btree (name)

插入两条记录：

postgres=# insert into mytb1(name,age) values('zhangsan',12),('lisi',23);

2. 使用created table as select

postgres=# create table mytb2 as select * from mytb1;
SELECT 2

postgres=# d mytb2
          Table "public.mytb2"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | 
 name   | character varying | 
 age    | integer           | 

postgres=# select * from mytb2;
 id |   name   | age 
----+----------+-----
  1 | zhangsan |  12
  2 | lisi     |  23
(2 rows)

3. 使用create table like

postgres=# create table mytb3 (like mytb1);
CREATE TABLE

postgres=# d mytb3
          Table "public.mytb3"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | not null
 name   | character varying | 
 age    | integer           | 

postgres=# select * from mytb3;
 id | name | age 
----+------+-----
(0 rows)

create table like的时候通过including来包含索引，约束等。

postgres=# create table mytb5 (like mytb1 including defaults including constraints including indexes);
CREATE TABLE

postgres=# d mytb5
                              Table "public.mytb5"
 Column |       Type        |                     Modifiers                     
 
--------+-------------------+---------------------------------------------------
 id     | integer           | not null default nextval('mytb1_id_seq'::regclass)
 name   | character varying | 
 age    | integer           | 
Indexes:
    "mytb5_name_idx" btree (name)

结论：

create table as select : 只会复制表中的数据，表结构中的索引，约束等不会复制过来。

create table like: 不复制数据，需要复制索引，约束等还要自己including进去。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!