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.