Use CREATE TABLE AS to copy table structure and data - Hologres

CREATE TABLE AS creates a new table from an existing table or a SELECT query result. Optionally, it copies the data at the same time.

Two syntax variants are supported:

`AS TABLE <src_table_name>` — copies the schema and optionally the data of an existing table or view
`AS <select_query>` — creates a table from the result set of a SELECT query

CREATE TABLE AS copies column schemas and data types, but not table properties such as primary keys, nullability constraints, indexes, or default values.

Prerequisites

Before you begin, ensure that you have:

A Hologres instance running V1.3.21 or later
An existing source table or view to copy from

Syntax

-- Variant 1: Copy from a source table or view
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS TABLE <src_table_name> [ WITH [ NO ] DATA ]

-- Variant 2: Create from a SELECT query result
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS <select_query> [ WITH [ NO ] DATA ]

Parameters

Parameter	Description
`new_table_name`	Name of the new table. Must be a fixed string — not a variable or function call. Foreign tables cannot be created with this statement.
`IF NOT EXISTS`	If a table with the same name already exists, skip table creation silently.
`src_table_name`	Name of the source table or view. Views are supported in Hologres V2.1.21 and later.
`select_query`	A SELECT statement. For syntax details, see SELECT.
`WITH [ NO ] DATA`	Controls whether source data is copied. `WITH DATA` (default) copies the data. `WITH NO DATA` creates an empty table with the same schema.

What is and isn't copied

CREATE TABLE AS copies column names and data types, but not table properties.

	CREATE TABLE AS	CREATE TABLE LIKE
Column schemas and data types	Copied	Copied
Table properties (nullability, default values, indexes, primary keys, comments)	Not copied	Partially copied
Source table data	Copied (with `WITH DATA`)	Not copied
Manual property configuration (indexes, distribution keys)	Limited support. Primary keys cannot be configured manually.	Limited support. Primary keys cannot be configured manually.
Non-partitioned table from a partitioned table	Supported	Supported
Partitioned table creation	Not supported	Partially supported (via `partition_clause`)

For details on CREATE TABLE LIKE, see CREATE TABLE LIKE.

Limitations

Only Hologres V1.3.21 and later support CREATE TABLE AS. To upgrade an earlier instance, manually upgrade your Hologres instance in the Hologres console or join a Hologres DingTalk group to apply for an instance upgrade. For details, see Instance upgrades or How to get online support?.
Data import atomicity is not guaranteed when using WITH DATA.
For columns with types that require a precision — VARCHAR, BPCHAR, NUMERIC (DECIMAL), BIT, or VARBIT — explicitly specify the precision in the CREATE TABLE AS statement. Omitting precision causes an error.
For columns of type INTERVAL, TIME, TIMETZ, TIMESTAMP, or TIMESTAMPTZ, do not specify precision. Specifying precision for these types causes an error.
CREATE TABLE AS cannot create a partitioned table. It always creates a non-partitioned table. When copying from a partitioned parent table, the statement automatically includes data from all child tables.
In Hologres V3.0.9 and later, serverless computing resources can be used to run CREATE TABLE AS. For details, see Work with serverless computing.

Query log behavior

Starting from Hologres V3.0.9, CREATE TABLE AS generates two records in hologres.hg_query_log: one for the CREATE TABLE AS statement itself, and one for the internal INSERT statement used to copy the data. The two records are linked by a transaction ID.

To retrieve both records for a given run:

SELECT
    query_id,
    query,
    extended_info
FROM
    hologres.hg_query_log
WHERE
    extended_info ->> 'source_trx' = '<transaction_id>'
ORDER BY
    query_start;

Replace <transaction_id> with the value from the trans_id field of the CREATE TABLE AS log record.

In versions earlier than V3.0.9, only one record is generated (for the CREATE TABLE AS statement).

Examples

Copy a non-partitioned table

The following examples use this 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');
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');

Copy with data (default)

CREATE TABLE public.new_table AS TABLE public.src_table;

Query the new table:

SELECT * FROM public.new_table;

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

The new table has the same data, but does not inherit the primary key or NOT NULL constraint from the source table:

SELECT hg_dump_script('public.new_table');

BEGIN;
CREATE TABLE public.new_table (
    a int,   -- int8 NOT NULL in source; inherited as int (nullable) here
    b text   -- text NOT NULL in source; inherited as text (nullable) here
);
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;

Copy schema only (no data)

CREATE TABLE public.new_table AS TABLE public.src_table WITH NO DATA;

Skip if table already exists

CREATE TABLE IF NOT EXISTS public.new_table AS TABLE public.src_table;

If new_table already exists, the statement does nothing and returns:

NOTICE: relation "new_table" already exists, skipping

Copy from a SELECT query result

CREATE TABLE public.new_table_2 AS SELECT * FROM public.src_table WHERE a = 1;

Copy a partitioned table

The following examples use this source partitioned table:

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');

CREATE TABLE AS always creates a non-partitioned table. It cannot replicate partition key constraints or inheritance relationships.

Copy from parent table (includes data from all partitions)

CREATE TABLE public.new_table_2 AS TABLE public.src_table_partitioned;

SELECT * FROM public.new_table_2;

 a |  b
---+-----
 2 | bbb
 1 | aaa
 3 | ccc

Copy from a single partition

CREATE TABLE public.new_table_3 AS TABLE public.src_table_child1;

Only the data of src_table_child1 is copied.

Copy from a SELECT query and set table properties

Create a table from a SELECT query result and configure its properties in the same transaction:

-- Create the 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 new table from the SELECT result and configure properties
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;

Hologres:CREATE TABLE AS