You can execute the CREATE TABLE LIKE statement to create a table that has the same schema as the table obtained by executing the specified SELECT statement. This topic describes how to execute the CREATE TABLE LIKE statement.
Limits
-
In Hologres V0.9 and earlier, you can execute the
CREATE TABLE LIKEstatement to copy only table schemas, but not table properties such as the primary key and index. You can view the version of a Hologres instance on the instance details page in the Hologres console. -
In Hologres V0.10 and later, you can execute the
CREATE TABLE LIKEstatement to copy both table schemas and table properties such as the primary key and index. To copy table properties from another table when you create a table, you must run the following command to set the related Grand Unified Configuration (GUC) parameter to true. Then, you must use theSELECT * FROM <table_name>statement in theCREATE TABLE LIKEstatement. Other SELECT statements, such asSELECT <column_1>, <column_2> FROM <table_name>, are not supported.set hg_experimental_enable_create_table_like_properties=true; -
The
CREATE TABLE LIKEstatement cannot synchronize data from the source table. -
If you specify columns in the SELECT statement, you must specify a unique alias for each column. If you specify the same alias for different columns, the CREATE TABLE LIKE statement creates multiple columns with the same name for the table. As a result, an error message is returned. Sample statements:
CALL hg_create_table_like('new_table', 'select *, 1 as c, ''a'' as c from src_table'); ERROR: column "c" specified more than once CONTEXT: SQL statement "create table new_table ( "a" integer, "b" text, "c" integer, "c" text );" PL/pgSQL function hg_create_table_like(text,text) line 22 at EXECUTE -
When you execute the
CREATE TABLE LIKEstatement, you can copy column comments and binary log properties. You cannot copy table comments or the dynamic partitioning property. Starting from V3.0.33, copying table comments is supported.
Syntax
The CREATE TABLE LIKE statement uses the following syntax in Hologres:
-- Copy a standard table without its properties. This method does not copy the partition properties of a logical partitioned table.
CALL hg_create_table_like('new_table_name', 'select_query');
-- Copy a physical partitioned table. Other table properties are not copied.
CALL hg_create_table_like('new_table_name', 'select_query', 'partition_clause');
-- Copy a table and its properties. Use this method to copy the partition properties of a logical partitioned table.
set hg_experimental_enable_create_table_like_properties=true; -- Session-level parameter.
CALL hg_create_table_like('new_table_name', 'select * from old_table_name');
When you call hg_create_table_like, Hologres creates a table named new_table_name whose schema matches the result of select_query. No data is inserted into the new table.
Parameter Description
-
new_table_name: the name of the table to create. The value must be a literal string. Variables, string concatenation, and functions are not supported. You cannot create a foreign table with CREATE TABLE LIKE.
-
select_query: the query statement. If the statement is exactly
select * from tablename,CREATE TABLE LIKEalso copies all properties of the source table, including the primary key and indexes. If the statement contains many single quotation marks ('), we recommend that you wrap it as$$query_sql$$so that single quotation marks are escaped automatically. Example:CALL HG_CREATE_TABLE_LIKE ('table_name', $$query_sql$$ [, 'partition_clause']) -
partition_clause: the partitioning clause that specifies the partition key. Child partitions are not created automatically. You must create them manually.
-
old_table_name: the name of the source table to copy.
Examples
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;
Use CREATE TABLE LIKE to create tables based on the source table:
-
Create a table with the same schema and properties as the source table:
-- Copy the table schema and properties. set hg_experimental_enable_create_table_like_properties=true; CALL hg_create_table_like('new_table', 'select * from src_table'); -
Create a table that adds a column to the source table schema:
-- Add column c, which has the same definition as column b. CALL hg_create_table_like('holo_table_1', $$select *, "b" as c from src_table$$); -
Create a physical partitioned table and then create its child partitions manually:
-- Add column ds and use it as the partition key of a physical partitioned table. CALL hg_create_table_like('new_table', $$select *, "b" as ds from src_table$$, 'partition by list(ds)'); -- Create child partitions. create table new_table_child_20201213 partition of new_table for values in('20201213'); -- Partition value: 20201213 create table new_table_child_20201214 partition of new_table for values in('20201214'); -- Partition value: 20201214 -
Create temporary tables from a partitioned table and attach them as child partitions:
-- Create a partitioned parent table. BEGIN; CREATE TABLE public.src_parent_table ( "a" int NOT NULL, "b" text NOT NULL, PRIMARY KEY (a,b) ) PARTITION BY LIST(a); CALL SET_TABLE_PROPERTY('public.src_parent_table', 'orientation', 'column'); CALL SET_TABLE_PROPERTY('public.src_parent_table', 'bitmap_columns', 'b'); CALL SET_TABLE_PROPERTY('public.src_parent_table', 'dictionary_encoding_columns', 'b:auto'); CALL SET_TABLE_PROPERTY('public.src_parent_table', 'time_to_live_in_seconds', '3153600000'); CALL SET_TABLE_PROPERTY('public.src_parent_table', 'distribution_key', 'a'); CALL SET_TABLE_PROPERTY('public.src_parent_table', 'storage_format', 'segment'); COMMIT; -- Create a child partition. CREATE TABLE public.src_child_table PARTITION OF public.src_parent_table FOR VALUES IN (20220101); -- Create tables that inherit properties from the parent table and from the child partition. set hg_experimental_enable_create_table_like_properties=true; CALL hg_create_table_like('like_parent_table', 'select * from src_parent_table'); CALL hg_create_table_like('like_child_table', 'select * from src_child_table'); -- Attach the new tables to the parent table as child partitions. ALTER TABLE src_parent_table ATTACH PARTITION like_parent_table FOR VALUES IN (20220102); ALTER TABLE src_parent_table ATTACH PARTITION like_child_table FOR VALUES IN (20220103);
Copy a table in the HoloWeb console
You can use HoloWeb to copy a table in a visualized manner, without the need to write SQL statements. Procedure:
-
Go to the HoloWeb console. For more information, see Connect to HoloWeb and run SQL queries.
-
In the top menu bar of the HoloWeb page, click Metadata Management.
-
From the Instances Connected list on the Metadata Management page, right-click the table to replicate and select Replicate Table Schema.
-
On the Replicate Table Schema tab, configure the following parameters.
Section
Parameter
Description
Destination Location
Table name
The name of the destination table. You can configure a custom name. Default value: Source table name_copy.
Description
The description of the destination table. This parameter is optional.
Schema
The name of the schema in which the destination table resides. Default value: public.
Advanced Settings
Replicate Properties of Source Table
Specifies whether to copy the properties of the source table to the destination table.
NoteReplicating source table properties is supported only on Hologres instances running V0.10 or later. For earlier versions, select No or upgrade your instance.
-
Click Submit in the upper-right corner to create the replicated table.
Inherited properties
In Hologres V2.0 and later, CREATE TABLE LIKE handles table properties as follows.
|
Property |
Inherited |
Description |
|
binlog.level |
Yes |
Specifies whether binary logging is enabled. |
|
binlog.ttl |
Yes |
The time-to-live (TTL) of binary logs, in seconds. Expired binary logs are deleted asynchronously. |
|
clustering_key |
Yes |
The columns used to sort data in storage. |
|
distribution_key |
Yes |
The columns used to distribute data for parallel queries. |
|
encryption_options |
Yes |
The table-level encryption settings. |
|
online_config_* |
Yes |
Table properties that take effect without a restart. Use them to optimize storage or accelerate queries. |
|
orientation |
Yes |
The storage format: row-oriented or column-oriented. |
|
primary_key |
Yes |
The primary key. |
|
proxima_vectors |
Yes |
The vector index. |
|
Serial columns |
Yes |
No new sequence is created. The new table uses the sequence of the source table. |
|
segment_key (event_time_column) |
Yes |
The columns used to filter storage files. |
|
storage_format |
Yes |
The storage file format: sst or orc. |
|
table_group |
Yes |
The table group of the table. |
|
table_storage_mode |
Yes |
The hot or cold storage mode. |
|
time_to_live_in_seconds |
Yes |
The TTL of table data, in seconds. Expired data is deleted asynchronously. |
|
mutate_type |
No |
A table on which a single-table materialized view is created must have mutate_type set to appendonly. |
|
shared_readonly |
No |
Specifies whether the table is read-only. |
|
readonly |
No |
Specifies whether modifications to the table schema and data are blocked. |
|
matview |
No |
Single-table materialized views are not inherited. |