CREATE TABLE LIKE creates a new table with the same schema as the result of a SELECT query, without copying any data. Use it to duplicate a table's structure for testing, staging, or schema reuse.
How it works
In Hologres, CREATE TABLE LIKE is implemented as a stored procedure:
CALL hg_create_table_like('new_table_name', 'select_query');The procedure creates a table named new_table_name based on the schema of the select_query result. No data is inserted.
To copy table properties (primary key, indexes, and more) in addition to the schema, use SELECT * FROM <source_table> as the query and enable property copying at the session level:
-- Enable property copying for this session (Hologres V0.10 and later)
SET hg_experimental_enable_create_table_like_properties = true;
CALL hg_create_table_like('new_table_name', 'select * from source_table_name');What gets copied
The following table summarizes which properties CREATE TABLE LIKE copies, depending on the query syntax and Hologres version.
| Property | SELECT * + GUC enabled (V0.10+) | Other SELECT syntax | Notes |
|---|---|---|---|
| Column schema (names, types) | Yes | Yes | Always copied |
| Primary key | Yes | No | Requires SELECT * + GUC parameter |
| Indexes (bitmap, etc.) | Yes | No | Requires SELECT * + GUC parameter |
| Distribution key | Yes | No | Requires SELECT * + GUC parameter |
| Column comments | Yes | Yes | Always copied |
| Binary logging (Binlog) properties | Yes | Yes | Always copied |
| Table comments | Yes (V3.0.33+) | Yes (V3.0.33+) | Not supported before V3.0.33 |
| Dynamic partition properties | No | No | Never copied |
| Data | No | No | Never copied |
In Hologres V0.9 and earlier, CREATE TABLE LIKE copies only the column schema. Table properties such as primary keys and indexes are not copied, regardless of syntax.
Syntax
Standard tables
-- Copy schema only (all versions)
CALL hg_create_table_like('new_table_name', 'select_query');
-- Copy schema and properties (V0.10 and later)
SET hg_experimental_enable_create_table_like_properties = true;
CALL hg_create_table_like('new_table_name', 'select * from source_table_name');If the query contains single quotation marks, use $$ delimiters to avoid escaping:
CALL hg_create_table_like('table_name', $$query_sql$$ [, 'partition_clause']);Partitioned tables
-- Copy schema and create a partitioned table (all versions)
CALL hg_create_table_like('new_table_name', 'select_query', 'partition_clause');Child partition tables are not created automatically. Create them manually after creating the parent table.
Parameters
| Parameter | Description |
|---|---|
new_table_name | Name of the table to create. Must be a fixed string — string concatenation and function-generated names are not supported. Cannot be a foreign table. |
select_query | A SQL query string. If the query is exactly SELECT * FROM <table_name>, and the GUC parameter is enabled, all table properties are copied. Use $$...$$ delimiters to avoid escaping single quotes inside the query. |
partition_clause | (Partitioned tables only) The clause that defines the partition structure, such as partition by list(col). Child partitions must be created manually. |
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;Copy a table with its properties
-- Enable property copying for this session
SET hg_experimental_enable_create_table_like_properties = true;
CALL hg_create_table_like('new_table', 'select * from src_table');The resulting table has the same schema, primary key, indexes, and other properties as src_table.
Add a column based on the source table
-- Create a table with an extra column "c" derived from column "b"
CALL hg_create_table_like('holo_table_1', $$select *, "b" as c from src_table$$);Each column in the query must have a unique alias. If two columns share the same alias, the statement fails with an error like column "c" specified more than once.
Create a partitioned table
-- Add a column "ds" and partition the table by it
CALL hg_create_table_like('new_table', $$select *, "b" as ds from src_table$$, 'partition by list(ds)');After creating the parent table, create child partitions manually:
CREATE TABLE new_table_child_20201213 PARTITION OF new_table FOR VALUES IN ('20201213');
CREATE TABLE new_table_child_20201214 PARTITION OF new_table FOR VALUES IN ('20201214');Copy a table using the HoloWeb console
HoloWeb provides a UI for copying tables without writing SQL.
Go to the HoloWeb page. For more information, see Connect to HoloWeb and run a query.
In the top menu bar, click Metadata Management.
In the Logged-in Instances list, right-click the table you want to copy and select Replicate Table Schema.
On the Replicate Table Schema tab, configure the following parameters.
Category Parameter Description Target location Table name Name of the target table. Defaults to <source_table_name>_copy.Target location Description Optional description for the target table. Target location Schema Schema for the target table. Defaults to public.Advanced options Synchronize source table properties Whether to copy table properties to the target table. Requires Hologres V0.10 or later. If your version is earlier than V0.10, select No or upgrade your instance. 
Click Submit.
Limitations
V0.9 and earlier: Only the column schema is copied. Table properties such as primary keys and indexes are not copied. Check your instance version on the instance product page in the Hologres console.
Property copying syntax restriction: When
hg_experimental_enable_create_table_like_propertiesis enabled, property copying only works withSELECT * FROM <table_name>. Partial column selections such asSELECT col1, col2 FROM <table_name>do not copy properties.`new_table_name` must be a fixed string: String concatenation and function-generated names are not supported.
No foreign tables: The target table cannot be a foreign table.
No automatic child partitions: Child partition tables must be created manually.
Dynamic partition properties are never copied, regardless of version or GUC settings.