All Products
Search
Document Center

Hologres:CREATE TABLE LIKE

Last Updated:Oct 14, 2026

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 LIKE statement 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 LIKE statement 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 the SELECT * FROM <table_name> statement in the CREATE TABLE LIKE statement. Other SELECT statements, such as SELECT <column_1>, <column_2> FROM <table_name>, are not supported.

    set hg_experimental_enable_create_table_like_properties=true;
  • The CREATE TABLE LIKE statement 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 LIKE statement, 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');
Note

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 LIKE also 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:

  1. Go to the HoloWeb console. For more information, see Connect to HoloWeb and run SQL queries.

  2. In the top menu bar of the HoloWeb page, click Metadata Management.

  3. From the Instances Connected list on the Metadata Management page, right-click the table to replicate and select Replicate Table Schema.

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

    Note

    Replicating source table properties is supported only on Hologres instances running V0.10 or later. For earlier versions, select No or upgrade your instance.

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