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

Create a table

  1. 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:

    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;
  2. Parameters
    1. 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.
    2. The following table describes whether the 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 -
      null Yes -
      unique No No
      check No No
      default Yes No
    3. You can call the set_table_property function to set properties for the table. For more information, see Set table properties.
  3. Limits
    1. 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 at a time in the 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. The following sample code shows you how to specify the id and ds columns to constitute the primary key of a table:
      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;
    2. Table names and column names are not case-sensitive. To define a table or column name that is in upper case or has special characters, you can enclose the name in double quotation marks (") to escape it. The following sample code shows you how to specify table names and column names in the CREATE TABLE statement:
      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);
      call set_table_property('tbl', 'clustering_key', '"C1,c2:desc"'); -- For more information about the set_table_property function, see the following text.
      call set_table_property('tbl', 'segment_key', '"C1",c2:desc');
      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%*";
    3. 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, Hologres displays a notice, skips the table creation step, and then returns a success response. The following table describes the rules.
      Situation Response for the situation with IF NOT EXISTS specified Response for 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
    4. A table name cannot exceed 64 bytes in length. Otherwise, the table name is truncated.
  4. Examples

    To create a standard table that contains a primary key, execute the following SQL statement:

    CREATE TABLE test (
     "id" bigint NOT NULL,
     "name" text NOT NULL,
     "age" bigint,
     "class" text NOT NULL,
    PRIMARY KEY (id)
    );

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.
  1. 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]'); 
    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{:[on|off]}[,...]]');
    call set_table_property('table_name', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
    call set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');
  2. 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
    orientation Default value: column. row
    distribution_key By default, the primary key is used as the distribution key. You can modify it as required. By default, the primary key is used as the distribution key.
    clustering_key By default, this property is empty. By default, the primary key is used as the clustering key.
    event_time_column By default, the first non-null timestamp column is used as the event time column. Not supported
    bitmap_columns Set this property as needed. Not supported
    dictionary_encoding_columns Set this property as needed. Not supported
    time_to_live_in_seconds Set this property as needed. Set this property as needed.
    1. orientation
      call set_table_property('<table_name>', 'orientation', '[column | row]');
      • The orientation property specifies whether the database table uses the column-oriented or row-oriented storage model in Hologres. The current Hologres version supports only one data storage model.
      • By default, database tables use the column-oriented storage model in Hologres. 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 row-oriented storage model is more applicable to scenarios where key-value pairs are used for queries and supports point queries and scans based on primary keys. 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, if tables have more columns, more storage space is occupied by indexes. You can disable the default indexes to free up some storage space. 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.
      • Examples
        // Create a row-oriented table.
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'orientation', 'row');
        commit;
        
        // Create a column-oriented table.
        begin;
        create table tb2 (a int not null, b text not null);
        call set_table_property('tb2', 'orientation', 'column');
        commit;
    2. 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. Entries with the same distribution key value are distributed to the same shard.
      • If you specify a single value for columnName, no extra spaces are allowed. If you specify multiple values for columnName, separate them with commas (,) and do not include extra spaces.
      • The distribution key does not support columns of the following data types: FLOAT, DOUBLE, 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. 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.
        • Increased 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 the filter condition. Otherwise, Hologres performs computing on all shards. This lowers the number of queries of that can be processed per second.
        • Improved join efficiency: If Table A and Table B belong to the same table group and the distribution key is specified as the join key, a local join can be performed on the current node 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
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'distribution_key', 'a');
        commit;
        
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'distribution_key', 'a,b');
        commit;
        
        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;
    3. clustering_key
      call set_table_property('tbl', '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 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, 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 columns in a clustered index. 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 col='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;
    4. event_time_column
      call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');
      • The segment_key property is renamed event_time_column in Hologres V0.9. However, this property can still be used in Hologres V0.9. 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 data that is strongly correlated with time, such as logs and traffic. Proper settings of this property can remarkably improve performance.
      • Before you set the event_time_column property, make sure that the orientation property is set to column, which indicates that the table uses the column-oriented storage model.
      • The columns that are specified as event time columns must meet the not null constraint. You cannot specify a column of one of the following data types as an event time column: FLOAT, DOUBLE, ARRAY, JSON, and specific complex data types.
      • By default, the first non-null TIMESTAMP or TIMESTAMPTZ column in the schema of a column-oriented table is used as an event time column. If no such column exists, the first non-null DATE column 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.
      Examples
      begin;
      create table tbl (a int not null, b text not null);
      call set_table_property('tbl', 'event_time_column', 'a,b');
      commit;
      -------------------------------------------------------------
      begin;
      create table tbl (a int not null, b time not null);
      call set_table_property('tbl', 'event_time_column', 'b:asc');
      commit;
    5. 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 column.
      off Indicates that no bitmap index is built for the current column.
      • The bitmap_columns property allows you to specify whether to build bitmap indexes for specific columns. Bitmaps can help filter data in a stored file. Therefore, we recommend that you convert filter conditions to bitmaps.
      • Before you set the bitmap_columns property, make sure that the orientation property is set to column, which indicates that 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 where the value is located.
      • 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 in both Hologres V0.8 and Hologres V0.9.
      • You can call the set_table_property function for the bitmap_columns property outside 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 the bitmap indexes for all columns. Bitmap indexes are automatically built for all TEXT columns, excluding the columns specified in the CALL statement.
        call set_table_property('tbl', 'bitmap_columns', 'a:off');
        -- Modify the bitmap indexes in an incremental manner. Specify only whether to build bitmap indexes for specific columns.
        call update_table_property('tbl', 'bitmap_columns', 'b:off');
    6. 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 column.
      off Indicates that no dictionary mappings are built for the current column.
      auto Indicates that Hologres automatically determines whether to build dictionary mappings for the current column. If you select the auto keyword for a column, Hologres automatically determines whether to build dictionary mappings for the column 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 column based on the characteristics of data.
      • The dictionary_encoding_columns property allows you to specify whether to build dictionary mappings for specific columns. Dictionary mappings can convert string comparisons to numeric comparisons to accelerate queries such as GROUP BY and FILTER.
      • Before you set the dictionary_encoding_columns property, make sure that the orientation property is set to column, which indicates that 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 column based on the characteristics of data.
      • You can call the set_table_property function for the dictionary_encoding_columns property outside 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 the dictionary mappings for all columns. Dictionary mappings are automatically built for all TEXT columns, excluding the columns specified in the CALL statement.
        call set_table_property('tbl', 'dictionary_encoding_columns', 'a:off');
        -- Modify the dictionary mappings in an incremental manner. Specify only whether to build dictionary mappings for specific columns.
        call update_table_property('tbl', 'dictionary_encoding_columns', 'b:off');
    7. 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 the time_to_live_in_seconds property must be a non-negative number, which can be an integer or a floating-point number.
      • You can call the set_table_property function for the time_to_live_in_seconds property outside the transaction of CREATE TABLE to modify the property.
      • Examples
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'time_to_live_in_seconds', '3.14159');
        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 specified point in time after the TTL period expires. Therefore, the business logic cannot highly depend on the TTL period. If you want Hologres to delete table data at a specified point in time, create a node in the Data Analytics module of HoloStudio and schedule the node to delete data.

Add comments

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

The following sample code shows you how to add comments:
-- 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:
-- This statement takes effect only in the current database. You need to execute the statement in the current database only once.
create extension hg_toolkit;
-- You can replace tablename in the statement with the name of the table.
select hg_dump_script('tablename');