postgresql—-根据现有表创建新表详解数据库

除普通的建表语句 create table table_name(columns); 之外，还可以根据现有表快速的创建新表:

一.使用create table (like )创建一个和原表结构相同的新表，根据INCLUDING保留原表的约束，索引等。

create table table_name (like parent_table {INCLUDING|EXCLUDING}{DEFAULTS|CONSTRAINTS|INDEXES|STORAGE|COMMENTS|ALL});

直接使用LIKE不使用INCLUDING，新表只保留原表的表结构和NOT NULL约束，但是使用INCLUDING CONSTRAINTS配置会保留了主键，唯一键，CHECK约束，并不会保留外键约束。

示例：

1.创建示例表

test=# create table tbl_inherits_test (a int not null); 

CREATE TABLE 

test=# alter table tbl_inherits_test add constraint pk_tbl_inherits_test_a primary key(a); 

ALTER TABLEtest=# create table tbl_inherits_parent( 

 a int not null, 

 b varchar(32) not null default got u, 

 c int, 

 d date); 

test=# alter table tbl_inherits_parent add constraint pk_tbl_inherits_parent_a primary key(a); 

ALTER TABLE 

test=# alter table tbl_inherits_parent add constraint fk_tbl_inherits_parent_a foreign key(a) references tbl_inherits_test(a); 

ALTER TABLE 

test=# alter table tbl_inherits_parent add constraint ck_tbl_inherits_parent_c check (c 10); 

ALTER TABLE 

test=# alter table tbl_inherits_parent add constraint uk_tbl_inherits_parent_b_d unique (b,d); 

ALTER TABLE

  test=# create index idx_tbl_inherits_parent_d on tbl_inherits_parent using btree (d);
  CREATE INDEX

2.使用LIKE创建表

test=# create table tbl_inherits_partition (like tbl_inherits_parent including constraints including indexes including defaults); 

CREATE TABLE 

test=# /d tbl_inherits_parent 

 Table "public.tbl_inherits_parent" 

 Column | Type | Modifiers 

--------+-----------------------+--------------------------------------------- 

 a | integer | not null 

 b | character varying(32) | not null default got u::character varying 

 c | integer | 

 d | date | 

Indexes: 

 "pk_tbl_inherits_parent_a" PRIMARY KEY, btree (a) 

 "uk_tbl_inherits_parent_b_d" UNIQUE CONSTRAINT, btree (b, d) 

 "idx_tbl_inherits_parent_d" btree (d) 

Check constraints: 

 "ck_tbl_inherits_parent_c" CHECK (c 10) 

Foreign-key constraints: 

 "fk_tbl_inherits_parent_a" FOREIGN KEY (a) REFERENCES tbl_inherits_test(a) 

test=# /d tbl_inherits_partition 

 Table "public.tbl_inherits_partition" 

 Column | Type | Modifiers 

--------+-----------------------+--------------------------------------------- 

 a | integer | not null 

 b | character varying(32) | not null default got u::character varying 

 c | integer | 

 d | date | 

Indexes: 

 "tbl_inherits_partition_pkey" PRIMARY KEY, btree (a) 

 "tbl_inherits_partition_b_d_key" UNIQUE CONSTRAINT, btree (b, d) 

 "tbl_inherits_partition_d_idx" btree (d) 

Check constraints: 

 "ck_tbl_inherits_parent_c" CHECK (c 10)

二、使用create table as table with {data|no data}创建一个和原表结构相同的新表，保留或不保留数据，但是不会继承原表的约束，索引等。

test=# insert into tbl_inherits_test values (1); 

INSERT 0 1 

test=# insert into tbl_inherits_parent (a,b,c,d) values(1,sss,12,2016-06-22 17:00:00); 

INSERT 0 1 

test=# 

test=# create table tbl_inherits_partition1 as table tbl_inherits_parent with data; 

SELECT 1 

test=# select * from tbl_inherits_partition1 ; 

 a | b | c | d 

---+-----+----+------------ 

 1 | sss | 12 | 2016-06-22 

(1 row) 

test=# /d tbl_inherits_partition1 

 Table "public.tbl_inherits_partition1" 

 Column | Type | Modifiers 

--------+-----------------------+----------- 

 a | integer | 

 b | character varying(32) | 

 c | integer | 

 d | date | 

test=# 

test=# 

test=# create table tbl_inherits_partition2 as table tbl_inherits_parent with no data; 

SELECT 0 

test=# /d tbl_inherits_partition2 

 Table "public.tbl_inherits_partition2" 

 Column | Type | Modifiers 

--------+-----------------------+----------- 

 a | integer | 

 b | character varying(32) | 

 c | integer | 

 d | date | 

test=# select * from tbl_inherits_partition2; 

 a | b | c | d 

---+---+---+--- 

(0 rows) 

test=# 

三、使用select * into new_table from table将结果集保存在新表中，但是只能执行一次。

test=# select * into tbl_inherits_partition3 from tbl_inherits_parent ; 

SELECT 1 

test=# /d tbl_inherits_partition3 

 Table "public.tbl_inherits_partition3" 

 Column | Type | Modifiers 

--------+-----------------------+----------- 

 a | integer | 

 b | character varying(32) | 

 c | integer | 

 d | date | 

test=# select * from tbl_inherits_partition3 ; 

 a | b | c | d 

---+-----+----+------------ 

 1 | sss | 12 | 2016-06-22 

(1 row)

四、使用create table new_table as select * from table将结果集保存在新表中。

test=# create table tbl_inherits_partition4 as select * from tbl_inherits_parent ; 

SELECT 1 

test=# /d tbl_inherits_partition4 

 Table "public.tbl_inherits_partition4" 

 Column | Type | Modifiers 

--------+-----------------------+----------- 

 a | integer | 

 b | character varying(32) | 

 c | integer | 

 d | date | 

test=# select * from tbl_inherits_partition4 ; 

 a | b | c | d 

---+-----+----+------------ 

 1 | sss | 12 | 2016-06-22 

(1 row)

4879.html