Execute the CREATE TABLE AS statement to create a table by copying data from the source table - Hologres

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 new 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 new 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.

Billing method	CREATE TABLE AS	CREATE TABLE LIKE (function)
Copies table structures (schemas and data types)	Supported.	Supported.
Copies table properties (nullability, 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 primary keys and indexes	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 new table, but the atomicity of data import cannot be guaranteed.
If the source table contains columns of the VARCHR, BPCHAR, NUMERIC (DECIMAL), BIT, or VARBIT type, 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 columns of the INTERVAL, TIME, TIMETZ, TIMESTAMP, or TIMESTAMPTZ type, 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 CREATE TABLE AS statement uses the following syntax 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 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 to be created. 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 source table or view that you want to copy. Note Hologres V2.1.21 and later allow you to use a view as a data source. You can create a table by copying the schema and data of a view.
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, the default value `WITH DATA` is used.

Examples

Create a non-partitioned table from 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 from the source table with data automatically synchronized from the source table.

CREATE TABLE public.new_table AS TABLE public.src_table;

Query data from the new table.

SELECT * FROM public.new_table;

-------
a | b
--|-----
1 | qaz
2 | wsx

Query the data definition language (DDL) statement of the new table. The DDL statement indicates that the new table does not inherit the primary key and NOT NULL property 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 from the source table with data automatically synchronized from the source 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 of the source table is not synchronized to the new table.
```
CREATE TABLE public.new_table AS TABLE public.src_table WITH NO DATA;
```
Scenario 4: Create a table by copying the 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 from a partitioned table or a partition of the partitioned table. You can create only a non-partitioned table from a partitioned table.

In this example, a partitioned table and multiple partitions of the partitioned table are created, and data is inserted into the partitions 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 from the partitioned table with data in all partitions synchronized.
```
CREATE TABLE public.new_table_2 AS TABLE public.src_table_partitioned;
```
Query data from the new table. The result indicates that the new table contains data in all partitions of the source table and the new 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 from a partition with data in the partition synchronized.

-- Create a table from a partition of the partitioned table with data in the partition automatically synchronized. This statement synchronizes only data of the specified partition.
CREATE TABLE public.new_table_3 AS TABLE public.src_table_child1;

Create a table by copying the result of a SELECT query and configure the properties of the new 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 result of the SELECT query with data automatically synchronized and configure properties of the new table.
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;