[Original] PostgreSQL quickly creates an empty table TIPS

Source: Internet
Author: User
MySQL has an excellent syntax createtable... like, which allows you to quickly copy a table and create its copy. PostgreSQL also has similar syntax and is more flexible, but pay attention to some details. Let's take a look at the MySQL Syntax: createtable... like the original table T1. The structure is as follows: + ---------- + ---------------- +-

MySQL has an excellent Syntax "create table... like", which allows you to quickly copy a table and create its copy. PostgreSQL also has similar syntax and is more flexible, but pay attention to some details. Let's take a look at the MySQL Syntax: create table... like the original table T1. The structure is as follows: + ---------- + ---------------- +-

MySQL has an excellent Syntax "create table... like", which allows you to quickly copy a table and create its copy. PostgreSQL also has similar syntax and is more flexible, but pay attention to some details.

Let's take a look at MySQL Syntax: create table... like

The original table T1 has the following structure:

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

Make a copy quickly:

mysql> create table t2 like t1;Query OK, 0 rows affected (0.03 sec)


In this case, an identical copy table is generated quickly:

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


Note that the auto-increment field is used as the primary key here. However, the syntax of MySQL does not follow the auto-increment position of the original table, but it starts from the beginning. However, this is funny because MySQL does not have a separate sequence.

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)


Now let's take a look at PostgreSQL:

The original table structure is as follows, including a sequence as the primary key.

                                    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 to create a copy:

t_girl=# create table t2 (like t1 including all);CREATE TABLETime: 50.035 ms


The table structure of the copy is as follows, but a problem may be found, along with the sequence of the original table. This is too insecure.

                                    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)



Now we can see that the pointer to this sequence is already 120, so the record of the copy table does not start from 120? In addition, the insertion of the copy table or other write operations will affect the original table!

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

Therefore, create a new sequence for the copy table:

t_girl=# create sequence t2_id_seq;CREATE SEQUENCETime: 12.744 ms


Update the default value of this column.

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


Insert some records at this time to see:

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

The record starts from 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


However, I LIKE all the options here, or do not include the default values. In this way, the sequence itself will not be copied in.

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)


You can also use the syntax similar to create table as... without the LIKE option, AS shown below:

Create an empty table with no records, but only the table structure and field correlation are included here.

T_girl = # create table t2 as table t1 with no data; SELECT 0 Time: 15.562 ms or t_girl = # create table t2 as select * from t1 where false; SELECT 0 Time: 14.181 ms


We manually add primary keys and default values.

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

The structure is the same as the original one.

                                    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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.