You can execute the INSERT statement to insert new rows to a specified table. This topic describes how to use the INSERT statement in Hologres.

Syntax

You can insert one or more rows specified by value expressions, or zero or more rows resulting from a query. The INSERT statement uses the following syntax: 
INSERT INTO table [( column [, ...] )]
   VALUES ( {expression}  [, ...] ) 
   [, ...] | query}

Parameters

The following table describes the parameters in the syntax.
Parameter Description
table The name of the table to which you want to insert data.

You can also specify the table name in the Table.Schema format.

column The name of the column to which you want to insert data.

You can qualify the name of the column by using a subfield name or an array subscript. If you insert data only to some fields of a composite column, the other fields are left null.

expression The expression or value to assign to the corresponding column.
query The SELECT statement that supplies the rows to be inserted. For more information about the syntax of the SELECT statement, see SELECT.

You can customize the order in which the names of the columns are listed. If no list of column names is given at all, data is inserted to all the columns of the table in their declared order, or the first N columns if only N columns are supplied by the VALUES clause or query. The values supplied by the VALUES clause or query are associated with the explicit or implicit column list left-to-right.

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if no default value is declared. If the expression for a column does not produce a value of the correct data type, automatic data type conversion will be performed.

Examples

Hologres allows you to execute the following INSERT statements to insert data to a specified table:
  • INSERT INTO VALUES
    INSERT INTO rh_holo2mysqltest (cate_id, cate_name) VALUES
    (3, 'true'),
    (3, 'fale'),
    (3, 'trxxue'),
    (3, 'x'),
    (4, 'The Dinner Game');
  • INSERT INTO SELECT
    INSERT INTO test2
SELECT 2,'two';
  • Insert data to a partitioned table
    When you insert a row of data to a partitioned table, make sure that you directly insert the data to the corresponding child partitioned table. The value of the partition field in the data to be inserted must be the same as the partition field value of the child partitioned table. 
    -- Create a parent partitioned table that does not have a primary key and its child partitioned 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_child1 partition of public.hologres_parent for values in('v1');
create table public.hologres_child2 partition of public.hologres_parent for values in('v2');
create table public.hologres_child3 partition of public.hologres_parent for values in('v3');
commit;
-- Insert data to a child partitioned table.
INSERT INTO public.hologres_child1 values('v1',1,now(),'a')