Hologres:INSERT

Syntax

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

How it works

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

Limits

• If you insert data into a parent table, make sure that the data is inserted into a child table of 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 method to insert data, make sure that the data types of the required columns are consistent with those of the columns that you query.

Examples

• Insert data to 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[]
  );
  
  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]);

• Insert data of table a to table b.

  CREATE TABLE holotest2(
      a int,
      b bigint,
      c bool);
  
  INSERT INTO holotest2 (a,b,c) SELECT a,b,c FROM holotest;

• Insert data to 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;
  
  -- Insert data to a child table.
  INSERT INTO public.hologres_2022 values('2022',1,now(),'a')

FAQ

• Question 1: Why does the monitoring metric storage usage increase significantly when I write data but then decrease after the data is written?

  Based on the data write principle, to accelerate the data write process, Hologres writes the data at the backend, and then 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 2: 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 3: 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 returned when I write data to a parent table.

  • Cause: No child table exists.

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

    create table <child_table_name> partition of <parent_table_name> for values in (<value>);

• Question 4: 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 returned when I import data.

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

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