【原创】PostgreSQL 快速创建空表TIPS

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||
+----------+------------------+------+-----+---------+----------------+

快速做一张副本：

MysqL>createtablet2liket1;
QueryOK,0rowsaffected(0.03sec)

这时会有一张相同的副本表快速产生：

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

这时注意到，这里用到自增字段作为主键，不过MysqL 这类语法不会沿用原始表的自增位置，还是从头开始。不过这点说起来难免搞笑，因为MysqL没有单独的序列。

MysqL>insertintot2(log_time)selectnow();
QueryOK,1rowaffected(0.00sec)
Records:1Duplicates:0Warnings:0
MysqL>select*fromt2;
+----+----------------------------+
|id|log_time|
+----+----------------------------+
|1|2014-11-2713:44:12.000000|
+----+----------------------------+
1rowinset(0.00sec)

现在来看下Postgresql:

原始表结构如下，包含了一个序列作为主键。

Table"ytt_sql.t1"
Column|Type|Modifiers
----------+-----------------------------+-------------------------------------------------
id|integer|notnulldefaultnextval('t1_id_seq'::regclass)
log_time|timestampwithouttimezone|
Indexes:
"t1_pkey"PRIMARYKEY,btree(id)

用类似的语法create table ... like 来创建副本：

t_girl=#createtablet2(liket1includingall);
CREATETABLE
Time:50.035ms

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

Table"ytt_sql.t2"
Column|Type|Modifiers
----------+-----------------------------+-------------------------------------------------
id|integer|notnulldefaultnextval('t1_id_seq'::regclass)
log_time|timestampwithouttimezone|
Indexes:
"t2_pkey"PRIMARYKEY,btree(id)

而此时查看到这个序列的指针已经是120了，那么副本表的记录不是要从120开始？而且副本表的插入或者其他写入操作都会影响原始表！

t_girl=#selectcurrval('t1_id_seq');
currval
---------
120
(1row)
Time:3.771ms

所以这时重新创建一个新的序列给副本表专用：

t_girl=#createsequencet2_id_seq;
CREATESEQUENCE
Time:12.744ms

更新这列的默认值。

t_girl=#altertablet2alteridsetdefaultnextval('t2_id_seq');
ALTERTABLE
Time:5.002ms

这时候插入些记录看看：

t_girl=#insertintot2(log_time)values....;
INSERT010
Time:10.331ms

这时记录从1开始了：

t_girl=#select*fromt2;
id|log_time
----+----------------------------
1|2014-03-0906:49:14.393962
2|2005-12-3005:49:14.393962
3|2014-05-1720:49:14.393962
4|2004-06-1522:49:14.393962
5|2010-06-1903:49:14.393962
...
10|2009-09-0723:49:14.393962
(10rows)
Time:4.958ms

不过我这里LIKE了所有选项，也可以不不包括默认值，这样，序列本身就不会复制进来了。

t_girl=#createtablet2(liket1includingallexcludingdefaults);
CREATETABLE
Time:40.292ms

Table"ytt_sql.t2"
Column|Type|Modifiers
----------+-----------------------------+-----------
id|integer|notnull
log_time|timestampwithouttimezone|
Indexes:
"t2_pkey"PRIMARYKEY,btree(id)

这里也可以不用LIKE 选项，直接用类似CREATE TABLE AS ...语法，如下：

创建没有记录的空表，但是这里只包含了表结构以及字段相关。

t_girl=#createtablet2astablet1withnodata;
SELECT0
Time:15.562ms
或者
t_girl=#createtablet2asselect*fromt1wherefalse;
SELECT0
Time:14.181ms

我们手动给添加主键以及默认值。

t_girl=#altertablet2addconstraintpk_t2_idprimarykey(id),alteridsetdefaultnextval('t2_id_seq');
ALTERTABLE
Time:41.105ms

结构跟原来一样了。

Table"ytt_sql.t2"
Column|Type|Modifiers
----------+-----------------------------+-------------------------------------------------
id|integer|notnulldefaultnextval('t2_id_seq'::regclass)
log_time|timestampwithouttimezone|
Indexes:
"pk_t2_id"PRIMARYKEY,btree(id)

【原创】PostgreSQL 快速创建空表TIPS

猜你在找的Postgre SQL相关文章