Hologres:INSERT

Syntax

INSERT INTO <schema>.<table> [( <column> [, ...] )]
   VALUES ( {<expression>}  [, ...] )
   [, ...] | <query>}

How it works

The storage modes of Hologres tables include row-oriented storage, column-oriented storage, and row-column hybrid storage. The three storage modes share the same write principle.

Limits

• If you insert data into a partitioned table, make sure that the data is inserted into a child table rather than the parent table. The value of the partitioned field in the data to be inserted must be the same as the partitioned field value of the child table.

• You can customize the order in which the names of the columns are listed. If you use the INSERT INTO SELECT statement to insert data, make sure that the data types of the required columns are consistent with the data types of the columns that you query.

Examples

• Insert data into an internal table.

  CREATE TABLE  holotest (
      a int,
      b bigint,
      c bool,
      e decimal(38,10),
      f text,
      g timestamp,
      h timestamptz,
      i jsonb,
      j int[]
  );
  
  -- Optional. We recommend that you use the Serverless Computing feature to import a large amount of data offline and run extract, transform, and load (ETL) jobs.
  SET hg_computing_resource = 'serverless';
  
  -- Import data.
  INSERT INTO holotest VALUES (1,9223372036854775807,false,123.123456789123,'john','2020-01-01 01:01:01.123456',
  '2004-10-19 10:23:54+08','{"a":2}',ARRAY[1, 2, 3, 4]);
  
  -- Reset the configurations. This ensures that serverless computing resources are not used for subsequent SQL statements. 
  RESET hg_computing_resource;

• Insert data of table a into table b.

  CREATE TABLE holotest2(
      a int,
      b bigint,
      c bool);
  
  -- Optional. We recommend that you use the Serverless Computing feature to import a large amount of data offline and run ETL jobs.
  SET hg_computing_resource = 'serverless';
  
  -- Import data.
  INSERT INTO holotest2 (a,b,c) SELECT a,b,c FROM holotest;
  
  -- Reset the configurations. This ensures that serverless computing resources are not used for subsequent SQL statements. 
  RESET hg_computing_resource;

• Insert data into a partitioned table.

  -- Create a parent table that does not have a primary key and its child tables in the public schema.
  BEGIN;
  CREATE TABLE public.hologres_parent(
    a text,
    b int,
    c timestamp,
    d text
  )
    PARTITION BY list(a);
  CALL set_table_property('public.hologres_parent', 'orientation', 'column');
  
  CREATE TABLE public.hologres_2022 partition of public.hologres_parent for values in('2022');
  
  CREATE TABLE public.hologres_2021 partition of public.hologres_parent for values in('2021');
  
  CREATE TABLE public.hologres_2020 partition of public.hologres_parent for values in('2020');
  COMMIT;
  
  -- Optional. We recommend that you use the Serverless Computing feature to import a large amount of data offline and run ETL jobs.
  SET hg_computing_resource = 'serverless';
  
  -- Insert data into a child table.
  INSERT INTO public.hologres_2022 values('2022',1,now(),'a')
  
  -- Reset the configurations. This ensures that serverless computing resources are not used for subsequent SQL statements. 
  RESET hg_computing_resource;

FAQ

• Question 1: When I write a large amount of data, the CPU utilization and memory usage of the instance are 100%. This negatively affects data writes and queries on the instance. What do I do?

  We recommend that you use the Serverless Computing feature when you want to write a large amount of data.

  Note

  Hologres V2.1.17 and later support the Serverless Computing feature. The Serverless Computing feature is suitable for scenarios in which you want to import a large amount of data offline, run large-scale ETL jobs, or query a large amount of data by using foreign tables. You can use serverless computing resources rather than reserved resources of instances to perform the preceding operations. No reserved computing resources are required. This improves instance stability and reduces the occurrences of out of memory (OOM) errors. You are charged only for the serverless computing resources used by tasks. For more information about the Serverless Computing feature, see Overview of Serverless Computing. For more information about how to use the Serverless Computing feature, see User guide on Serverless Computing.

• Question 2: The metric that indicates storage usage increases significantly when I write data but decreases after the data is written. Why?

  Based on the data write principle, to accelerate data writes, Hologres writes the data at the backend, and compresses and sorts the data when the asynchronous compaction is performed. Therefore, the storage usage increases significantly during the data write process. After the data write process and the compaction process are complete, the storage usage decreases.

• Question 3: Why does the latency increase if multiple INSERT statements are executed in parallel on the same table?

  If the INSERT statement is executed on a table without using a fixed plan, the table is locked. Parallel execution of INSERT statements increases the lock wait duration, resulting in increased latency.

• Question 4: Why is the error message ERROR: no partition of relation "<table_name>" found for row returned when I write data to a parent table?

  • Problem description: The error message ERROR: no partition of relation "<table_name>" found for row is reported.

  • Cause: No child table exists.

  • Solution: Before you write data, create a child table by executing the following statement:

    CREATE TABLE <child_table_name> partition of <parent_table_name> for values in (<value>);

• Question 5: Why is the error message Currently inserting into parent table is not supported returned when I import data?

  • Problem description: The error message Currently inserting into parent table is not supported is reported when I import data.

  • Cause: The table to which you want to insert data is a parent table. Hologres does not allow you to write data to a parent table.

  • Solution: Create a child table and write data to the child table.