PostgreSQL 快速建立空表TIPS

標籤：postgresql   sql   mysql   

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: 0mysql> 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 TABLETime: 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 SEQUENCETime: 12.744 ms

更新這列的預設值。

t_girl=# alter table t2 alter id set default nextval(‘t2_id_seq‘);ALTER TABLETime: 5.002 ms

這時候插入些記錄看看：

t_girl=# insert into t2 (log_time) values ....;INSERT 0 10Time: 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 TABLETime: 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 0Time: 15.562 ms或者t_girl=# create table t2 as select * from t1 where false;SELECT 0Time: 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 TABLETime: 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