Postgres CREATE TABLE as Select & CREATE TABLE like

1. Prepare

Create a base table first:

CREATE TABLE mytb1 (ID serial,name character varying,age integer);

To create an index on the name field:

Create INDEX Mytb1_name_index on MYTB1 (name);

To view the MYTB1 table structure:

postgres=# \d mytb1;
                              Table "public.mytb1"
 Column |       Type        |                     Modifiers                     
 
--------+-------------------+---------------------------------------------------
 ID     | Integer           | NOT null default nextval (' Mytb1_id_seq ':: regclass)
 name   | character Varying | 
 Age    | integer           | 
Indexes:
    "Mytb1_name_index" Btree (name)

Insert Two records:

postgres=# INSERT INTO MYTB1 (name,age) VALUES (' Zhangsan ', a), (' Lisi ', 23);

2. Use created table as Select

postgres=# CREATE TABLE MYTB2 as SELECT * from MYTB1;
SELECT 2

View the table structure of the MYTB2 and the data in the table:

postgres=# \d mytb2
          Table "public.mytb2"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 ID     | 
 Name   | character Varying | 
 Age    | integer           | 

postgres=# select * from MYTB2;
 ID |   Name   | Age 
----+----------+-----
  1 | zhangsan |
  2 | Lisi     |
(2 rows)

You can see that with the CREATE table as select, the data in the table is copied all over, and the primary key, index, constraint, and so on in the table structure are not moved, just the fields are copied past.

3. Use CREATE TABLE like

postgres=# CREATE TABLE mytb3 (like mytb1);
CREATE TABLE

View the table structure of the MYTB3 and the data in the table:

postgres=# \d mytb3
          Table "public.mytb3"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 ID     | integer           | NOT null
 name   | Character varying | 
 Age    | integer           

postgres=# select * from MYTB3;
 ID | name | Age 
----+------+-----
(0 rows)

The index primary key in the table structure also does not come together.

CREATE table like when through the including to include indexes, constraints, and so on.

postgres=# CREATE TABLE mytb5 (like MYTB1 including defaults including constraints);
CREATE TABLE

Look at the table structure

postgres=# \d mytb5
                              Table "public.mytb5"
 Column |       Type        |                     Modifiers                     
 
--------+-------------------+---------------------------------------------------
 ID     | Integer           | NOT null default nextval (' Mytb1_id_seq ':: regclass)
 name   | character Varying | 
 Age    | integer           | 
Indexes:
    "Mytb5_name_idx" Btree (name)

Now indexes, constraints, and so on are all copied over.

Conclusion:

CREATE TABLE AS Select: Only data in the table is replicated, indexes in the table structure, constraints, and so on are not copied.

CREATE TABLE like: Do not copy the data, you need to copy the index, constraints, etc. also want to including in.