You can execute the CREATE TABLE statement to create a table. This topic describes how to execute the CREATE TABLE statement in Hologres.

Limits

  • When you configure the primary key of a table, you can specify multiple columns to constitute a composite primary key. The values of the columns that constitute the primary key must be unique and cannot be null. In addition, you must specify all these columns in one CREATE TABLE statement. The primary key does not support columns of the following data types: FLOAT, DOUBLE, NUMERIC, ARRAY, JSON, DATE, and specific complex data types. You cannot modify the primary key. If you need to modify the primary key, create another table. The following sample code shows you how to specify the id and ds columns to constitute the primary key of a table:
    -- Correct example
    BEGIN;
    CREATE TABLE public.test (
     "id" text NOT NULL,
     "ds" text NOT NULL,
    PRIMARY KEY (id,ds)
    );
    CALL SET_TABLE_PROPERTY('public.test', 'orientation', 'column');
    COMMIT;
  • Table names and column names are not case-sensitive. To define a table or column name that is in uppercase or has special characters, you can enclose the name in double quotation marks (") to escape the name. Examples:
    create table "TBL" (a int);
    select relname from pg_class where relname = 'TBL';
    insert into "TBL" values (-1977);
    select * from "TBL";
    ------------------------------------------------------------------
    begin;
    create table tbl ("C1" int not null);
    call set_table_property('tbl', 'clustering_key', '"C1"');
    commit;
    ------------------------------------------------------------------
    begin;
    create table tbl ("C1" int not null, c2 text not null);
    call set_table_property('tbl', 'clustering_key', '"C1,c2:desc"'); -- For more information about the set_table_property function, see the "Set table properties" section of this topic.
    commit;
    ------------------------------------------------------------------
    create table "Tab_$A%*" (a int);
    select relname from pg_class where relname = 'Tab_$A%*';
    insert into "Tab_$A%*" values (-1977);
    select * from "Tab_$A%*";
  • IF NOT EXISTS: When you create a table, if no table with the same name exists and the semantics is correct, the table can be created. If the IF NOT EXISTS option is not specified and a table with the same name exists, an error is returned. If the IF NOT EXISTS option is specified and a table with the same name exists, Hologres displays a notice, skips the table creation step, and then returns a success response. The following table describes the rules.
    Situation Response in the situation with IF NOT EXISTS specified Response in the situation without IF NOT EXISTS specified
    A table with the same name exists. NOTICE: relation ''xx''already exists, skippingSUCCEED ERROR: relation is already exists.
    No table with the same name exists. SUCCEED SUCCEED
  • A table name cannot exceed 64 bytes in length. Otherwise, the table name is truncated.
  • You cannot modify the data type of a column after the table is created. If you need to modify the data type of a column, create another table.
  • You must specify a primary key for a row-oriented table or a table that is both row-oriented and column-oriented. You do not need to specify a primary key for a column-oriented table.
  • The orientation, distribution_key, clustering_key, and event_time_column properties cannot be modified after a table is created. If you want to modify these properties, create another table. The bitmap_columns and dictionary_encoding_columns properties can be modified after a table is created.

Create a table

  • Syntax
    The CREATE TABLE statement in Hologres supports only specific features of the CREATE TABLE statement in PostgreSQL. The following syntax applies when you execute the CREATE TABLE statement in Hologres:
    Note In Hologres, transactions support only DDL statements. DML statements cannot be executed in transactions.
    begin;
    create table [if not exists] [schema_name.]table_name ([
      {
       column_name column_type [column_constraints, [...]]
       | table_constraints
       [, ...]
      }
    ]);
    
    call set_table_property('<table_name>', property, value);
    commit;
  • Parameters
    • The column_type parameter specifies the data type of a column to be created in the new table. For more information about the data types that are supported by Hologres, see Data types.
    • The following table describes whether specific constraints are supported as column constraints or table constraints.
      Constraint Supported as a column constraint Supported as a table constraint
      primary key Yes Yes
      not null Yes N/A
      null Yes N/A
      unique No No
      check No No
      default Yes No
    • You can call the set_table_property function to set properties for the table. For more information, see Set table properties.
    • If the default constraint is declared on a table, you cannot implement high-throughput data writes or updates to the table by using fixed plans. For more information about fixed plans, see Accelerate the execution of SQL statements by using fixed plans.

Set table properties

Hologres allows you to call the set_table_property function to set table properties. Proper settings of table properties help Hologres efficiently sort and query data. If you want to set properties related to the data storage layout, you must execute related statements together with the CREATE TABLE statement. In the current Hologres version, the orientation, distribution_key, clustering_key, and event_time_column properties cannot be modified after a table is created.
  • Syntax
    call set_table_property('<table_name>', property, value);
    Note You must call the set_table_property function in the same transaction as the CREATE TABLE statement.
    In the current Hologres version, you can execute one of the following SQL statements to set table properties:
    call set_table_property('table_name', 'orientation', '[column | row | row,column]');
    call set_table_property('table_name', 'table_group', '[tableGroupName]'); 
    call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
    call set_table_property('table_name', 'clustering_key', '[columnName{:[desc|asc]} [,...]]'); 
    call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');
    call set_table_property('table_name', 'bitmap_columns', '[columnName [,...]]');
    call set_table_property('table_name', 'dictionary_encoding_columns', '[columnName [,...]]');
    call set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');
  • Properties
    The following table describes the properties and their settings in column-oriented tables and row-oriented tables.
    Property Column-oriented table Row-oriented table Table that is both column-oriented and row-oriented Recommendation Whether the property can be modified after a table is created
    orientation Default value: column. row row,column column No. If you need to modify the property, create another table.
    table_group The default value is the name of the default table group. The default value is the name of the default table group. The default value is the name of the default table group. Use the default value. No. If you need to modify the property, create another table or reshard the existing table.
    distribution_key By default, the primary key is used as the distribution key. You can modify the distribution key as required. By default, the primary key is used as the distribution key. By default, the primary key is used as the distribution key. The subset of the columns that constitute the primary key. We recommend that you specify only one column. No. If you need to modify the property, create another table.
    clustering_key By default, the property is empty. By default, the primary key is used as the clustering key. By default, the property is empty. We recommend that you specify only one column. No. If you need to modify the property, create another table.
    event_time_column By default, the first non-null timestamp column is used as the event time column. Not supported. By default, the first non-null timestamp column is used as the event time column. We recommend that you specify a timestamp column. No. If you need to modify the property, create another table.
    bitmap_columns Set this property based on your business requirements. Not supported. Set this property based on your business requirements. We recommend that you specify no more than 10 columns among which you want to check whether the values are equal. Yes. For more information, see ALTER TABLE.
    dictionary_encoding_columns Set this property based on your business requirements. Not supported. Set this property based on your business requirements. We recommend that you specify no more than 10 columns with low cardinality. Yes. For more information, see ALTER TABLE.
    time_to_live_in_seconds Set this property based on your business requirements. Set this property based on your business requirements. Set this property based on your business requirements. We recommend that you use the default value. Yes. For more information, see ALTER TABLE.
    • orientation
      call set_table_property('table_name', 'orientation', '[column | row |row,column]');
      • The orientation property specifies whether a database table uses the column-oriented or row-oriented storage model in Hologres. Hologres V1.1 and later support a storage model that is both row-oriented and column-oriented.
      • By default, database tables use the column-oriented storage model in Hologres. The following table describes the storage models.
        Storage model Scenario Description
        Column-oriented The column-oriented storage model is more applicable to online analytical processing (OLAP) scenarios and supports complex queries, data association, scans, filtering, and statistics collection. The efficiency of inserting and updating data in column-oriented tables is lower than that in row-oriented tables. Compared with the row-oriented storage model, the column-oriented storage model generates more default indexes, such as the bitmap indexes that are created for data of the STRING type. These indexes can remarkably accelerate queries, filtering, and statistics collection. However, more storage space is occupied by indexes if tables have more columns. You can disable the default indexes by modifying table properties to free up some storage space.
        Row-oriented The row-oriented storage model is more applicable to scenarios in which key-value pairs are used for queries and supports point queries and scans based on primary keys. The efficiency of inserting and updating data in row-oriented tables is higher than that in column-oriented tables. If a table uses the row-oriented storage model, only the index for the primary key is created. In this case, less storage space is occupied. However, only queries based on the primary key can be accelerated.
        Both column-oriented and row-oriented The storage model that is both row-oriented and column-oriented combines the advantages of the row-oriented and column-oriented storage models. This composite storage model supports both point queries and OLAP. However, this storage model occupies more storage space, and the synchronization of internal data status brings extra overhead. The efficiency of updating specific columns in tables that are both column-oriented and row-oriented is significantly higher than that in column-oriented tables and slightly lower than that in row-oriented tables. If a table uses the storage model that is both row-oriented and column-oriented, you must specify a primary key for the table. Other indexes that are created for the table are the same as those created for a column-oriented table.
      • Examples
        -- Create a row-oriented table.
        begin;
        create table public.tbl_row (
            a integer NOT NULL,
            b text NOT NULL
            ,PRIMARY KEY (a)
        );
        call set_table_property('public.tbl_row', 'orientation', 'row');
        commit;
        
        
        -- Create a column-oriented table.
        begin;
        create table tbl_col (
          a int not null,
          b text not null);
        call set_table_property('tbl_col', 'orientation', 'column');
        commit;
        
        -- Create a table that is both column-oriented and row-oriented.
        begin;
        create table tbl_col_row (
            pk  text  not null,
            col1 text,
            col2 text,
            col3 text,
            PRIMARY KEY (pk));
        call set_table_property('tbl_col_row', 'orientation', 'row,column');
        commit;
    • table_group
      call set_table_property('table_name', 'table_group', '[tableGroupName]');
      • The table_group property specifies the table group to which a table belongs in Hologres.
      • By default, a table is created in the default table group in Hologres. If you do not create a table group after you create a database, a default table group is automatically created when you create the first table in the database. The default table group is named in the <Database name>_tg_default format. In most cases, you do not need to create a table group. For more information, see User guide of table groups and shard counts.
    • distribution_key
      call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
      • The distribution_key property specifies one or more columns that constitute a distribution key, which is used to distribute data in a table. Hologres distributes data to each shard based on the distribution key. Hologres distributes data with the same distribution key value to the same shard by using the hash(distribution_key)%shard_count algorithm. The result of the algorithm is the shard ID.
      • If you specify a single value for the columnName parameter, no extra spaces are allowed. If you specify multiple values for the columnName parameter, separate them with commas (,) and do not include extra spaces.
      • The distribution key does not support columns of the following data types: FLOAT, DOUBLE, DECIMAL (NUMERIC), DATE, TIMESTAMP, TIMESTAMPTZ, ARRAY, JSON, SERIAL, BYTEA, and specific complex data types.
      • If a table has a primary key, the primary key is used as the distribution key by default. The distribution key must consist of one or more columns that constitute the primary key and cannot be null. Entries with the same distribution key value must be distributed to the same shard. If no primary key is specified for the table, the distribution key can be null. In other words, you do not need to specify a column as the distribution key. If the distribution key is null, data can be randomly distributed to different shards. If a distribution key value is null, the value is regarded as an empty string.
      • In Hologres, the distribution key is important for distributed computing. Proper settings of the distribution key can achieve the following effects:
        • Improved performance: Parallel computing can be performed on different shards. This improves performance.
        • Improved queries per second (QPS): You can filter data based on the distribution key. In this case, Hologres scans data only in the shards that meet filter conditions. Otherwise, Hologres performs computing on all shards. This decreases the QPS.
        • Improved join efficiency: If Table A and Table B belong to the same table group and the distribution key of each table is specified as the join key, a local join can be performed in the table group to join the data in a shard of Table A and the data in the corresponding shard of Table B. This greatly improves execution efficiency.
      • Examples
        -- Run the hash(distribution_key)%shard_count algorithm for Column a. The return result is the shard ID. Entries that have the same return result are distributed to the same shard.
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'distribution_key', 'a');
        commit;
        
        -- Run the hash(distribution_key)%shard_count algorithm for Column a and Column b. The return result is the shard ID. Entries that have the same return result are distributed to the same shard.
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'distribution_key', 'a,b');
        commit;
        
        -- Entries in Table tbl1 are distributed based on the return result of Column a, whereas entries in Table tbl2 are distributed based on the return result of Column c. If Table tbl1 and Table tbl2 are joined by using the a=c join condition, the matched entries in the two tables are distributed to the same shard. This way, you can perform a local join operation on the tables and accelerate queries.
        begin;
        create table tbl1(a int not null, b text not null);
        call set_table_property('tbl1', 'distribution_key', 'a');
        create table tbl2(c int not null, d text not null);
        call set_table_property('tbl2', 'distribution_key', 'c');
        commit;
        
        select b, count(*) from tbl1 join tbl2 on tbl1.a = tbl2.c group by b;
    • clustering_key
      call set_table_property('table_name', 'clustering_key', '[columnName{:asc} [,...]]');
      • The clustering_key property specifies the columns for Hologres to create clustered indexes. Hologres sorts data based on clustered indexes. Hologres allows you to use clustered indexes to accelerate RANGE and FILTER queries on indexed columns. The clustering_key property specifies the order relationship of data in a stored file at the underlying layer. Therefore, you can use the clustering_key property to accelerate range queries.
      • The columns that constitute the clustering key must meet the not null constraint. The clustering key does not support columns of the following data types: FLOAT, DOUBLE, DECIMAL (NUMERIC), ARRAY, JSON, and specific complex data types.
      • When you use the clustering_key property to specify a column, you can append :asc to the column name to sort the indexes to be built in ascending order.
      • By default, the clustering key of a row-oriented table is the primary key. No clustering key is specified in the versions earlier than Hologres V0.9. If the clustering key is not the primary key of a table, Hologres generates two sorting methods for this table: sorting based on the primary key and sorting based on the clustering key. This causes redundant data.
      • By default, the clustering key of a column-oriented table is null.
      • The clustering key is used for sorting. The first column among the columns that constitute the clustering key has the highest priority. We recommend that you retain only one or two columns to constitute the clustering key.
      • The clustering key allows you to accelerate RANGE and FILTER queries on the first few indexed columns. Queries must follow the leftmost matching principle. Otherwise, you cannot use the clustering key to accelerate the queries.
        For example, the clustering_key property of Table table1 specifies Column col1 and Column col2 as indexed columns. The following examples are some queries that can be accelerated and some that cannot:
        -- The query can be accelerated.
        select * from table1 where col1='abc'; 
        -- The query can be accelerated.
        select * from table1 where col1>'xxx' and col1<'abc';
        -- The query can be accelerated.
        select * from table1 where col1 in ('abc','def');
        -- The query can be accelerated.
        select * from table1 where col1='abc' and col2='def'; 
        -- The query cannot be accelerated.
        select col1,col4 from table1 where col2='def';
      • Examples
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'clustering_key', 'a,b');
        commit;
        -------------------------------------------------------------
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'clustering_key', 'a,b:asc');
        commit;
    • event_time_column
      call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');
      • The event_time_column property allows you to specify a column that contains time data as an event time column. This column must be of a time data type. If data changes, this column must be strongly correlated with the update time. If event time columns are involved in query conditions, Hologres can find the storage location of data with ease based on the event time columns. This property is applicable to logs, traffic, and data that is strongly correlated with time. Proper settings of this property can remarkably improve performance. The event_time_column property specifies the boundary conditions between stored files at the underlying layer and records the maximum and minimum boundaries for each file. Therefore, you can use the event_time_column property to accelerate range-based scans on files.
      • The event time column must meet the not null constraint. The event time column does not support the following data types: FLOAT, DOUBLE, DECIMAL (NUMERIC), ARRAY, JSON, and specific complex data types.
      • You cannot set the event_time_column property for a row-oriented table.
      • Before you set the event_time_column property, make sure that the orientation property is set to column. This way, the table uses the column-oriented storage model.
      • By default, the first non-null TIMESTAMP or TIMESTAMPTZ field in the schema of a column-oriented table is used as an event time column. If no such field exists, the first non-null DATE field is used as an event time column. By default, no event time column is specified for a table in the versions earlier than Hologres V0.9.
      • The segment_key property is renamed event_time_column in Hologres V0.9. However, the segment_key property can still be used in Hologres V0.9.
      • Examples
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'event_time_column', 'a,b');
        commit;
    • bitmap_columns
      call set_table_property('table_name', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
      The following table describes the required parameters.
      Parameter Description
      table_name The name of the table.
      on Indicates that a bitmap index is built for the current field.
      off Indicates that no bitmap index is built for the current field.
      • The bitmap_columns property allows you to specify whether to build bitmap indexes for specific columns. Bitmap indexes can help filter data that equals a specified value in a stored file. Therefore, we recommend that you convert equality filter conditions to bitmap indexes. The bitmap_columns property uses an index schema that is independent from data storage. You can use the bitmap_columns property to accelerate equality comparisons based on a bitmap vector structure.
      • Before you set the bitmap_columns property, make sure that the orientation property is set to column. This way, the table uses the column-oriented storage model.
      • We recommend that you build bitmap indexes only for columns with a few values. This way, a binary string is constructed for each value to indicate the bitmap in which the value resides.
      • The columns specified by the bitmap_columns property can be null.
      • By default, the bitmap_columns property implicitly builds bitmap indexes for all TEXT columns.
      • You can call the set_table_property function for the bitmap_columns property separately from the transaction of CREATE TABLE to modify the property. The modified columns do not immediately take effect, and bitmap indexes are asynchronously built and deleted in the background. For more information, see ALTER TABLE.
      • Examples
        -- Create a table named tbl and configure bitmap indexes.
        begin;
        create table tbl (
          a int not null, 
          b text not null);
        call set_table_property('tbl', 'bitmap_columns', 'a:on,b:off');
        commit;
        
        -- Modify bitmap indexes.
        call set_table_property('tbl', 'bitmap_columns', 'a:off');// Modify the bitmap_columns property for all columns.
        call update_table_property('tbl', 'bitmap_columns', 'b:off');// Modify the bitmap_columns property only for Column b.
    • dictionary_encoding_columns
      call set_table_property('table_name', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
      The following table describes the required parameters.
      Parameter Description
      table_name The name of the table.
      on Indicates that dictionary mappings are built for the current field.
      off Indicates that dictionary mappings are not built for the current field.
      auto Specifies that Hologres determines whether to enable dictionary encoding for the field. If you select the auto keyword for a field, Hologres automatically determines whether to build dictionary mappings for the field based on the duplication degree of values. More duplicate values ensure higher efficiency of dictionary mappings. By default, dictionary mappings are built for all TEXT columns in Hologres V0.8 and earlier. In Hologres V0.9 and later, Hologres can automatically determine whether to build dictionary mappings for a field based on the characteristics of data.
      • The dictionary_encoding_columns property allows you to specify whether to build dictionary mappings for the values of specific columns. Dictionary mappings can convert string comparisons to numeric comparisons to accelerate queries such as GROUP BY and FILTER. The dictionary_encoding_columns property uses a compressed storage technology to encode and store raw data as numeric data. In addition, you can use the dictionary_encoding_columns property to maintain an encoding schema. During data reads, the system decodes data based on the encoding schema. The decoding process results in additional computing overhead.
      • Before you set the dictionary_encoding_columns property, make sure that the orientation property is set to column. This way, the table uses the column-oriented storage model.
      • The columns specified by the dictionary_encoding_columns property can be null.
      • We recommend that you build dictionary mappings only for columns with a few values. This can help compress storage.
      • By default, the dictionary_encoding_columns property implicitly builds dictionary mappings for all TEXT columns in Hologres V0.8 and earlier. In Hologres V0.9 and later, Hologres can automatically determine whether to build dictionary mappings for a field based on the characteristics of data.
      • You can call the set_table_property function for the dictionary_encoding_columns property separately from the transaction of CREATE TABLE to modify the property. The modified columns do not immediately take effect, and dictionary mappings are asynchronously built and deleted in the background. For more information, see ALTER TABLE.
      • Examples
        -- Create a table named tbl and configure dictionary mappings.
        begin;
        create table tbl (
          a int not null, 
          b text not null,
          c text not null
        );
        call set_table_property('tbl', 'dictionary_encoding_columns', 'a:on,b:off,c:auto');
        commit;
        
        -- Modify dictionary mappings.
        call set_table_property('tbl', 'dictionary_encoding_columns', 'a:off');-- Modify the dictionary_encoding_columns property for all columns.
        
        call update_table_property('tbl', 'dictionary_encoding_columns', 'b:off');-- Modify the dictionary_encoding_columns property only for Column b.
    • time_to_live_in_seconds
      call set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');
      The time_to_live_in_seconds property specifies the time-to-live (TTL) period of data in a table, in seconds. The value of this property must be a non-negative number, which can be an integer or a floating-point number.
      • Usage notes
        • By default, if the TTL period of data is not specified, the data is retained for 100 years.
        • The system counts the start of the TTL period from the time when the data is first written to the table, rather than the time when the data is last modified. If the TTL period expires, the data of the table is deleted at an unspecified point in time. The table is still retained.
        • You can call the set_table_property function for the time_to_live_in_seconds property separately from the transaction of CREATE TABLE to modify the property.
        • Compared with the DELETE FROM tbl WHERE xxx_time + TTL < now(); statement, the TTL period setting occupies almost no resources and allows data writes to the table. However, the consistency of the expired data cannot be ensured. In this case, the following situations may occur:
          • You may fail to read the expired data or read the expired data that was not last modified.
          • If you modify or delete the expired data, the operation may fail due to unexpected causes. For example, the data of a primary key column is duplicate.
          • If you specify the TTL period for the data, make sure that the expired data will not be read or modified, or that the consistency of the expired data is not required.
      • Examples
        -- Create a table and specify the TTL period.
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'time_to_live_in_seconds', '864000');
        commit;
        
        -- Modify the TTL period.
        call set_table_property('tbl', 'time_to_live_in_seconds', '86400');
        Note Hologres does not delete data from a table exactly based on the TTL period specified for the table but at a point in time after the TTL period expires. Therefore, the business logic cannot highly depend on the TTL period. If you want the data of the table to be deleted at a specific point in time, you can configure the scheduling properties of a node in DataWorks.

Add comments

Hologres allows you to add comments to internal tables, foreign tables, and columns.

The following examples show you how to add comments:
  • Add comments when you create a table.
    BEGIN;
    CREATE TABLE public."user" (
     "id" text NOT NULL,
     "name" text NOT NULL
    );
    COMMENT ON TABLE public."user" is 'the user property table';
    COMMENT ON COLUMN public."user".id is 'the ID card number';
    COMMENT ON COLUMN public."user".name is 'the name';
    COMMIT;
  • Add comments to an existing table.
    -- Add a comment to an internal table.
    COMMENT ON TABLE table_name IS 'my comments on table table_name.';
    
    -- Add a comment to a column.
    COMMENT ON COLUMN table_name.col1 IS 'This my first col1';
    
    -- Add a comment to a foreign table.
    COMMENT ON FOREIGN TABLE foreign_table IS ' comments on my foreign table';

For more information, see COMMENT.

Query the DDL statement of a table

You can use the following syntax to query the DDL statement of a table:
select hg_dump_script('tablename');

-- This statement takes effect on the entire database. You need to execute the statement in a database only once. In some earlier versions of Hologres, the system prompts that the function does not exist after you execute the statement. In this case, you can execute the following statement to manually load the function:
create extension hg_toolkit; 
Note You can also go to the Metadata Management tab in the HoloWeb console and view the DDL statement of the table on the DDL statement tab of the table details tab.

Examples

  • Create a column-oriented table and specify the primary key.
    Note The distribution key must consist of one or more columns that constitute the primary key.
    begin;
    CREATE TABLE tbl (
     "id" bigint NOT NULL,
     "name" text NOT NULL,
     "age" bigint,
     "class" text NOT NULL,
     "reg_timestamp" timestamptz NOT NULL,
    PRIMARY KEY (id,age)
    );
    call set_table_property('tbl', 'orientation', 'column');
    call set_table_property('tbl', 'distribution_key', 'id');
    call set_table_property('tbl', 'clustering_key', 'age');
    call set_table_property('tbl', 'event_time_column', 'reg_timestamp');
    call set_table_property('tbl', 'bitmap_columns', 'name,class');
    call set_table_property('tbl', 'dictionary_encoding_columns', 'class:auto');
    commit;
  • Create a partitioned table and specify the primary key.
    Note If a partitioned table has a primary key, the primary key must contain the partition fields.
    begin;
    CREATE TABLE www (
     name text NOT NULL,
     ds text NOT NULL,
     age text NOT NULL,
    PRIMARY KEY (name,ds)
    )
    PARTITION BY LIST(ds);
    CALL SET_TABLE_PROPERTY('www', 'orientation', 'column');
    commit;
  • Create a table and specify default values for the fields.
    -- Set the default value of a field to the current time.
    CREATE TABLE test(
      id TIMESTAMPTZ DEFAULT now()
    );
    
    -- Set the default value of specified fields.
    CREATE TABLE products (
        product_no integer,
        name text,
        price FLOAT  DEFAULT 1.99
    );

Create an internal table in the HoloWeb console

You can use HoloWeb to create an internal table in a visualized way, without the need to write SQL statements. To do so, perform the following steps:

  1. Log on to the HoloWeb console. For more information, see HoloWeb quick start.
  2. In the top navigation bar of the HoloWeb console, choose Metadata Management > Table.

    You can also click Instances Connected in the left-side navigation pane of the Metadata Management tab. Click the instance that you want to manage and click the database that you want to manage. Right-click the schema that you want to manage and select New table.

  3. On the New table tab, set the parameters as required. Configure the internal table
    Section Parameter Description
    Basic information Instance Name The name of the current instance.
    Database The name of the current database.
    Table name The name of the Hologres internal table.
    Description The description of the Hologres internal table.
    Schema The name of the schema.

    You can select the default schema public or a custom schema.

    Field Field name The name of the field in the internal table.
    Data type The data type of the field.
    PK Specifies whether to use the field as the primary key for the internal table.
    Nullable Specifies whether the field can be left empty.
    Array Specifies whether the field is an ordered array of elements.
    Description The description of the field.
    Operation The operations that you can perform on the field. You can click Delete to delete the field and click Up or Down to move the field up or down.
    Property Storage Format The storage model of the table. Valid values: Column storage and Row storage.

    Default value: Column storage.

    Lifecycle The TTL period of the table data. The system counts the start of the TTL period from the time when data is first written to the table. If the TTL period expires, the data of the table is deleted at an unspecified point in time.

    Default value: Forever.

    Binlog Specifies whether to enable binary logging for the table. For more information, see Subscribe to Hologres binlogs.
    Lifecycle of Binlog The TTL period of binlogs. For more information, see Subscribe to Hologres binlogs. Default value: Forever.
    Distribution Column Specifies whether to use the field as the distribution key for the internal table. For more information, see Set table properties.
    Event Time Column Specifies whether to use the filed as the event time column for the internal table. For more information, see Set table properties.
    Clustering Key Specifies whether to use the field as the clustering key for the internal table. For more information, see Set table properties.
    Dictionary Encoding Specifies whether to build dictionary mappings for the field. For more information, see Set table properties.
    Bitmap Column Specifies whether to build a bitmap index for the field. For more information, see Set table properties.
    Partition table N/A Select the partition fields of the internal table.
  4. In the upper-right corner, click Submit. After the internal table is submitted, you can refresh the left-side instance lists. The created internal table is displayed under the schema that you selected.
  5. Optional. Preview the data in the table.
    1. In the Instances Connected list, double-click the internal table whose data you want to preview.
    2. On the table information tab, click Data preview to preview the data in the table. Data preview tab
  6. Optional. View the DDL statements used to create the table.
    On the table information tab, click DDL statement to view the DDL statements used to create the table. DDL statement tab