PostgreSQL quick creation of empty table TIPS, postgresql empty table tips

PostgreSQL quick creation of empty table TIPS, postgresql empty table tips
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)