PostgreSQL Quick Create empty table tips

MySQL has one and excellent syntax create TABLE ... like, you can quickly copy a table and create a copy of it. PostgreSQL also has a similar syntax and is more flexible, but pay attention to the details.
First look at MySQL syntax: CREATE TABLE ... like
The original table T1, with the following structure:

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

Make a quick copy:

Mysql> CREATE table t2 like T1; Query OK, 0 rows affected (0.03 sec)

The same copy table will be generated quickly:

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

Notice here that the self-increment field is used as the primary key, but the syntax for MySQL does not follow the original table's self-increment position or start from scratch. But that's funny, because MySQL doesn't 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 look at PostgreSQL:
The original table structure is as follows, containing 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 a table with a similar syntax ... like to make a copy:

t_girl=# CREATE TABLE T2 (like T1 including all); CREATE tabletime:50.035 ms

The table structure of the replica is as follows, but a problem may be found, along with the sequence of the original table, which 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)

And now that the pointer to this sequence is already 120, then the record of the replica table is not to start from 120? And the insertion of the replica table or other write operations will affect the original table!

t_girl=# Select Currval (' T1_id_seq ');           Currval---------     (1 row) time:3.771 ms

So then recreate a new sequence for the replica table dedicated:

t_girl=# Create sequence T2_id_seq; CREATE sequencetime:12.744 ms

Update the default value for this column.

t_girl=# ALTER TABLE T2 ALTER ID set default Nextval (' T2_id_seq '); ALTER tabletime:5.002 ms

Now insert some records to see:

t_girl=# INSERT INTO T2 (log_time) values ... insert 0 10time:10.331 ms

At this time the record started 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- 23:49:14.393962 (rows) time:4.958 ms

But I like all the options here, or not excluding the default values, so the sequence itself is not 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)

This can also be used without the like option, directly with the Create TABLE as ... Syntax, as follows:
Create an empty table with no records, but this contains only the table structure and field-related.

t_girl=# CREATE TABLE t2 as table T1 with no data; Select 0time:15.562 MS or t_girl=# CREATE TABLE t2 as SELECT * from T1 where false; SELECT 0time:14.181 ms

We manually add the primary key and the default value.

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.

                                    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 Quick Create empty table tips