All Products
Search
Document Center

Hologres:CREATE TABLE AS

Last Updated:Jul 18, 2023

In Hologres V1.3.21 and later, you can execute the CREATE TABLE AS statement to create a table by copying the structure of a source table. You can choose to synchronize data from the source table. This topic describes how to execute the CREATE TABLE AS statement in Hologres.

Background information

You can execute the CREATE TABLE AS statement to create a table with the same structure as a source table or the table obtained by executing the specified SELECT query. You can choose to automatically synchronize data from the source table to the created table. However, this statement does not copy table properties.

The following table describes the syntax differences between CREATE TABLE AS and CREATE TABLE LIKE. You can select an appropriate statement based on your business requirements. For more information about the CREATE TABLE LIKE statement, see CREATE TABLE LIKE.

Item

CREATE TABLE AS

CREATE TABLE LIKE (function)

Copies table structures (schemas and data types)

Supported.

Supported.

Copies table properties (nullable, default values, indexes, primary keys, or comments)

Not supported.

Limited support.

Copies source table data

Supported.

Not supported.

Copies source tables and allows users to manually configure new table properties such as indexes and primary keys

Limited support. Primary keys cannot be manually configured.

Limited support. Primary keys cannot be manually configured.

Creates non-partitioned tables by copying the structures of partitioned tables

Supported.

Supported.

Creates partitioned tables

Not supported.

Limited support. You can manually create a partitioned table by using partition_clause.

Limits

  • Only Hologres V1.3.21 and later support the CREATE TABLE AS statement. If the version of your Hologres instance is earlier than V1.3.21, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For more information about how to manually upgrade a Hologres instance, see Instance upgrades. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

  • You can execute the CREATE TABLE AS statement to copy only table structures, but not table properties such as primary keys and indexes.

  • When you create a table, the CREATE TABLE AS statement allows you to automatically synchronize data from the source table to the created table, but the atomicity of data import cannot be guaranteed.

  • If the source table contains VARCHR, BPCHAR, NUMERIC (DECIMAL), BIT, or VARBIT columns, you must explicitly specify the precision for these columns in the CREATE TABLE AS statement when you use this statement. Otherwise, an error message is returned.

  • If the source table contains INTERVAL, TIME, TIMETZ, TIMESTAMP, or TIMESTAMPTZ columns, you cannot specify the precision for these columns in the CREATE TABLE AS statement when you use this statement. Otherwise, an error message is returned.

  • You can execute the CREATE TABLE AS statement to create a non-partitioned table from a parent table or a child table. You can copy only the table structure and synchronize data. When you copy a parent table, this statement automatically synchronizes the data of all its child tables. You cannot create a partitioned table by copying the partition structure such as partition key constraints and inheritance relationships.

Syntax

The following CREATE TABLE AS statement syntax is used to create tables in Hologres:

-- Create a table by copying a source table.
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS TABLE <src_table_name> [ WITH [ NO ] DATA ]

-- Create a table by copying the execution result of a SELECT query.
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS <select_query> [ WITH [ NO ] DATA ]

Parameters

Parameter

Description

new_table_name

The name of the table that you want to create. You must set this parameter to a fixed string but not a string of variables or a function that is used to generate a table name. You cannot create a foreign table by executing the CREATE TABLE AS statement.

[ IF NOT EXISTS ]

Checks whether or not a table with the same name already exists. If a table with the same name already exists, the table creation step is skipped.

src_table_name

The name of the table that you want to copy.

select_query

The SQL statement that is used to query data. For more information, see SELECT.

[ WITH [ NO ] DATA ]

Specifies whether to automatically synchronize data from the source table to the table that you want to create. Valid values:

  • WITH DATA: automatically synchronizes data.

  • WITH NO DATA: does not automatically synchronize data.

If you do not specify this parameter, data is synchronized by default.

Examples

  • Create a non-partitioned table by copying a source non-partitioned table.

    • In this example, a source table is created and data is inserted into the table by executing the following statements:

      BEGIN;
      CREATE TABLE public.src_table (
       "a" int8 NOT NULL,
       "b" text NOT NULL,
      PRIMARY KEY (a)
      );
      CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column');
      CALL SET_TABLE_PROPERTY('public.src_table', 'bitmap_columns', 'b');
      CALL SET_TABLE_PROPERTY('public.src_table', 'dictionary_encoding_columns', 'b:auto');
      CALL SET_TABLE_PROPERTY('public.src_table', 'time_to_live_in_seconds', '3153600000');
      CALL SET_TABLE_PROPERTY('public.src_table', 'distribution_key', 'a');
      CALL SET_TABLE_PROPERTY('public.src_table', 'storage_format', 'segment');
      COMMIT;
      INSERT INTO public.src_table VALUES (1,'qaz'),(2,'wsx');
    • Scenario 1: Create a table by copying the source table and automatically synchronize data from the source table to the created table.

      CREATE TABLE public.new_table AS TABLE public.src_table;

      After the preceding statement is executed, query data in the created table.

      SELECT * FROM public.new_table;
      
      -------
      a | b
      --|-----
      1 | qaz
      2 | wsx

      Query the DDL statement of the created table. The following result shows that the created table does not inherit primary key and NOT NULL properties of the source table.

      -- DDL statement of the new table:
      select hg_dump_script('public.new_table');
      -------------------------------------------
      BEGIN;
      CREATE TABLE public.new_table (
          a int,
          b text
      );
      CALL set_table_property('public.new_table', 'orientation', 'column');
      CALL set_table_property('public.new_table', 'storage_format', 'orc');
      CALL set_table_property('public.new_table', 'bitmap_columns', 'b');
      CALL set_table_property('public.new_table', 'dictionary_encoding_columns', 'b:auto');
      CALL set_table_property('public.new_table', 'time_to_live_in_seconds', '3153600000');
      COMMENT ON TABLE public.new_table IS NULL;
      END;
    • Scenario 2: Create a table by copying the source table and automatically synchronize data from the source table to the created table. If a table with the same name already exists, the table creation step is skipped and the data in the source table is not synchronized.

      CREATE TABLE IF NOT EXISTS public.new_table AS TABLE public.src_table;
      
      NOTICE: relation "new_table" already exists, skipping
    • Scenario 3: Create a table by copying only the structure of the source table. Data in the source table is not synchronized to the created table.

      CREATE TABLE public.new_table AS TABLE public.src_table WITH NO DATA;
    • Scenario 4: Create a table by copying the execution result of a SELECT query and automatically synchronize data.

      CREATE TABLE public.new_table_2 AS SELECT * FROM public.src_table WHERE a = 1 ;
  • Create a non-partitioned table by copying a parent table or a child table. You cannot create a partitioned table by copying a partitioned table. Instead, you can create a non-partitioned table by copying a partitioned table.

    • In this example, a parent table and its child tables are created and data is inserted into the parent table and the child tables by executing the following statements:

      BEGIN;
      CREATE TABLE public.src_table_partitioned (
       "a" int NOT NULL,
       "b" text ,
      PRIMARY KEY (a)
      ) PARTITION BY LIST(a);
      CREATE TABLE public.src_table_child1 PARTITION OF public.src_table_partitioned FOR VALUES IN (1);
      CREATE TABLE public.src_table_child2 PARTITION OF public.src_table_partitioned FOR VALUES IN (2);
      CREATE TABLE public.src_table_child3 PARTITION OF public.src_table_partitioned FOR VALUES IN (3);
      COMMIT;
      
      INSERT INTO src_table_child1 VALUES (1,'aaa');
      INSERT INTO src_table_child2 VALUES (2,'bbb');
      INSERT INTO src_table_child3 VALUES (3,'ccc');
    • Scenario 1: Create a non-partitioned table by copying a parent table and synchronize data from the parent table to the created table.

      CREATE TABLE public.new_table_2 AS TABLE public.src_table_partitioned;

      Query data in the created table. The following result shows that the created table contains data in the parent table and the created table is a non-partitioned table.

      SELECT * FROM public.new_table_2;
      ----------------------------------
      a | b
      --|-----
      2 | bbb
      1 | aaa
      3 | ccc
    • Scenario 2: Create a non-partitioned table by copying a child table and synchronize the data from the child table to the created table.

      -- To create a table by copying a child table and automatically synchronize the data from the child table to the created table, execute the following statement. This statement is used to synchronize only the data of this child table.
      CREATE TABLE public.new_table_3 AS TABLE public.src_table_child1;
  • Create a table by copying the execution result of a SELECT query and configure the properties of the created table.

    -- Create a source table.
    BEGIN;
    CREATE TABLE public.src_table (
     "a" int8 NOT NULL,
     "b" text NOT NULL,
    PRIMARY KEY (a)
    );
    CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column');
    COMMIT;
    
    -- Create a table by copying the execution result of a SELECT query, configure the properties of the created table, and automatically synchronize data.
    BEGIN;
    CREATE TABLE public.new_table AS select * from public.src_table;
    CALL SET_TABLE_PROPERTY('public.new_table', 'bitmap_columns', 'b');
    CALL SET_TABLE_PROPERTY('public.new_table', 'dictionary_encoding_columns', 'b:auto');
    CALL SET_TABLE_PROPERTY('public.new_table', 'time_to_live_in_seconds', '3153600');
    CALL SET_TABLE_PROPERTY('public.new_table', 'distribution_key', 'a');
    COMMIT;