PostgreSQL快速创建空表TIPS

页面导航:首页 > 数据库 > MsSql > PostgreSQL快速创建空表TIPS

PostgreSQL快速创建空表TIPS

来源: 作者: 时间:2016-01-20 09:48 【

MySQL 有一个和优秀的语法 create table like , 可以快速复制一张表,创建其副本。 PostgreSQL 也有类似的语法,而且更加灵活,不过要注意些细节。
MySQL 有一个和优秀的语法 create table ... like , 可以快速复制一张表,创建其副本。 PostgreSQL 也有类似的语法,而且更加灵活,不过要注意些细节。
先来看看MySQL 语法: create table ... like 
原始表T1,结构如下:

 
+----------+------------------+------+-----+---------+----------------+  
| Field    | Type             | Null | Key | Default | Extra          |  
+----------+------------------+------+-----+---------+----------------+  
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |  
| log_time | datetime(6)      | YES  |     | NULL    |                |  
+----------+------------------+------+-----+---------+----------------+  

 

 
 
 
快速做一张副本:
> create table t2 like t1;  
Query OK, 0 rows affected (0.03 sec)  

 

 
 
 
这时会有一张相同的副本表快速产生:
+----------+------------------+------+-----+---------+----------------+  
| Field    | Type             | Null | Key | Default | Extra          |  
+----------+------------------+------+-----+---------+----------------+  
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |  
| log_time | datetime(6)      | YES  |     | NULL    |                |  
+----------+------------------+------+-----+---------+----------------+  

 

 
 
 
 
这时注意到,这里用到自增字段作为主键,不过MySQL 这类语法不会沿用原始表的自增位置,还是从头开始。不过这点说起来难免搞笑,因为MySQL没有单独的序列。
 
mysql> insert into t2 (log_time) select now();  
Query OK, 1 row affected (0.00 sec)  
Records: 1  Duplicates: 0  Warnings: 0  
  
  
mysql> select * from t2;  
+----+----------------------------+  
| id | log_time                   |  
+----+----------------------------+  
|  1 | 2014-11-27 13:44:12.000000 |  
+----+----------------------------+  
1 row in set (0.00 sec)  

 

 
 
 
 
 
 
现在来看下PostgreSQL:
原始表结构如下, 包含了一个序列作为主键。
 
                                    Table "ytt_sql.t1"  
  Column  |            Type             |                    Modifiers                      
----------+-----------------------------+-------------------------------------------------  
 id       | integer                     | not null default nextval('t1_id_seq'::regclass)  
 log_time | timestamp without time zone |   
Indexes:  
    "t1_pkey" PRIMARY KEY, btree (id)  

 

 
 
 
 
用类似的语法create table ... like 来创建副本:
t_girl=# create table t2 (like t1 including all);  
CREATE TABLE  
Time: 50.035 ms  

 

 
 
 
 
副本的表结构如下,不过可能发现了一个问题,连同原始表的序列也一起弄过来了,这个太不安全了。

 
                                    Table "ytt_sql.t2"  
  Column  |            Type             |                    Modifiers                      
----------+-----------------------------+-------------------------------------------------  
 id       | integer                     | not null default nextval('t1_id_seq'::regclass)  
 log_time | timestamp without time zone |   
Indexes:  
    "t2_pkey" PRIMARY KEY, btree (id)  

 

 
 
 
 
 
而此时查看到这个序列的指针已经是120了,那么副本表的记录不是要从120开始?而且副本表的插入或者其他写入操作都会影响原始表!
t_girl=# select currval('t1_id_seq');            
 currval   
---------  
     120  
(1 row)  
  
  
Time: 3.771 ms  

 

 
 
 
 
所以这时重新创建一个新的序列给副本表专用:
t_girl=# create sequence t2_id_seq;  
CREATE SEQUENCE  
Time: 12.744 ms  

 

 
 
 
更新这列的默认值。
t_girl=# alter table t2 alter id set default nextval('t2_id_seq');  
ALTER TABLE  
Time: 5.002 ms  

 

 
 
 
这时候插入些记录看看:

 
t_girl=# insert into t2 (log_time) values ....;  
INSERT 0 10  
Time: 10.331 ms  

 

 
 
 
 
这时记录从1开始了:
t_girl=# select * from t2;  
 id |          log_time            
----+----------------------------  
  1 | 2014-03-09 06:49:14.393962  
  2 | 2005-12-30 05:49:14.393962  
  3 | 2014-05-17 20:49:14.393962  
  4 | 2004-06-15 22:49:14.393962  
  5 | 2010-06-19 03:49:14.393962  
...  
 10 | 2009-09-07 23:49:14.393962  
(10 rows)  
  
  
Time: 4.958 ms  

 

 
 
 
 
不过我这里LIKE了所有选项,也可以不不包括默认值,这样,序列本身就不会复制进来了。
 
t_girl=# create table t2 (like t1 including all excluding defaults);  
CREATE TABLE  
Time: 40.292 ms  
  
                 Table "ytt_sql.t2"  
  Column  |            Type             | Modifiers   
----------+-----------------------------+-----------  
 id       | integer                     | not null  
 log_time | timestamp without time zone |   
Indexes:  
    "t2_pkey" PRIMARY KEY, btree (id)  

 

 
 
这里也可以不用LIKE 选项,直接用类似CREATE TABLE AS ...语法,如下:
创建没有记录的空表,但是这里只包含了表结构以及字段相关。
 
t_girl=# create table t2 as table t1 with no data;  
SELECT 0  
Time: 15.562 ms  
或者  
t_girl=# create table t2 as select * from t1 where false;  
SELECT 0  
Time: 14.181 ms  

 

 
 
 
 
我们手动给添加主键以及默认值。
 
t_girl=# alter table t2 add constraint pk_t2_id primary key (id), alter id set default nextval('t2_id_seq');  
ALTER TABLE  
Time: 41.105 ms 
 
 
 
结构跟原来一样了。
                                    Table "ytt_sql.t2"  
  Column  |            Type             |                    Modifiers                      
----------+-----------------------------+-------------------------------------------------  
 id       | integer                     | not null default nextval('t2_id_seq'::regclass)  
 log_time | timestamp without time zone |   
Indexes:  
    "pk_t2_id" PRIMARY KEY, btree (id)  

 


Tags:

相关文章

    文章评论

    最 近 更 新
    热 点 排 行
    Js与CSS工具
    代码转换工具
    
    <