Postgres CREATE TABLE as Select & CREATE TABLE like

Source: Internet
Author: User
Tags create index modifiers

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.