PostgreSQL database Comparison of MySQL quick copy of empty table skills example

MySQL has a and excellent syntax create TABLE ... like, you can quickly copy a table and create a copy of it. PostgreSQL has a similar syntax and is more flexible, but pay attention to the details.

Let's look at MySQL syntax: CREATE TABLE ... like
The original table T1, the structure is as follows:



+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| ID | int (a) 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)




There will be an identical copy table to be generated quickly:



+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| ID | int (a) unsigned | NO | PRI | NULL | auto_increment |
| Log_time | DateTime (6) | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+





Notice here that the self added field is used as the primary key, but the syntax for MySQL does not follow the original table's own location or start from scratch. But that's a funny thing to say, because MySQL has no separate sequence.





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)







Now look at the 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)





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 with similar syntax ... like to make a copy:



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




The table structure of the replica is as follows, but a problem may have been 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 look at this sequence of pointers is already 120, then the record of the copy table is not to start from 120? And the insert or other write operation of the replica table will affect the original table!



t_girl=# Select Currval (' T1_id_seq ');
Currval
---------
120
(1 row)


time:3.771 ms




So then recreate a new sequence to be dedicated to the replica table:



t_girl=# Create sequence T2_id_seq;
CREATE SEQUENCE
time:12.744 ms




Update the default value for this column.



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




Now insert some records to see:



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





At this time the record began with 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
(Ten rows)


time:4.958 ms





But I'm like this. All options, or you can not include the default value, so that the sequence itself will not be copied in.





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)




Here also can not use the like option, directly with similar create TABLE as ... Syntax, as follows:
Create an empty table without records, but this contains only the table structure and field correlation.





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 the primary key as well as 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 TABLE
time:41.105 ms





The structure is the same as it used to be.




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)