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

时间：2014-12-10 02:28:05 阅读：314 评论：0 收藏：0 [点我收藏+]

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

点击(此处)折叠或打开

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)

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

原文：http://blog.chinaunix.net/uid-259788-id-4679667.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)