INSERT

Description

Inserts one or more rows of data into a table.

Precautions

• The owner of a table, users granted with the INSERT permission on the table, or users granted with the INSERT ANY TABLE permission can insert data into the table. The system administrator has the permission to insert data into the table by default when separation of duties is disabled.
• Use of the RETURNING clause requires the SELECT permission on all columns mentioned in RETURNING.
• If ON DUPLICATE KEY UPDATE is used, you must have the INSERT and UPDATE permissions on the table and the SELECT permission on the columns of the UPDATE clause.
• If you use the query clause to insert rows from a query, you need to have the SELECT permission on any table or column used in the query.
• If you use the query clause to insert data from the dynamic data anonymization column, the inserted result is the anonymized value and cannot be restored.
• When you connect to a database compatible to Teradata and td_compatible_truncation is on, a long string will be automatically truncated. If later INSERT statements (not involving foreign tables) insert long strings to columns of CHAR- and VARCHAR-typed columns in the target table, the system will truncate the long strings to ensure no strings exceed the maximum length defined in the target table.
  

  If inserting multi-byte character data (such as Chinese characters) into a database with the character set byte encoding (SQL_ASCII, LATIN1), and the character data crosses the truncation position, the string is truncated based on its bytes instead of characters. Unexpected result will occur in tail after the truncation. If you want correct truncation result, you are advised to adopt encoding set such as UTF8, which has no character data crossing the truncation position.

Syntax

1
2
3
4
5
6
7

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT [/*+ plan_hint */] [ IGNORE ] INTO table_name [ { [alias_name] [ ( column_name [, ...] ) ] } | { [partition_clause] [ AS alias ] [ ( column_name [, ...] ) ] } ] 
    { DEFAULT VALUES
    | { VALUES | VALUE } {( { expression | DEFAULT } [, ...] ) }[, ...] 
    | query }
    [ ON DUPLICATE KEY UPDATE { NOTHING | { column_name = { expression | DEFAULT } } [, ...] [ WHERE condition ] } ]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

Parameters

• WITH [ RECURSIVE ] with_query [, ...]

  Specifies one or more subqueries that can be referenced by name in the main query, which is equivalent to a temporary table.

  If RECURSIVE is specified, a SELECT subquery can be referenced by name.

  Format of with_query:

  with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ]
  ( {SELECT | VALUES | INSERT | UPDATE | DELETE} )

  – with_query_name specifies the name of the result set generated by a subquery. Such names can be used to access the result sets of subqueries in a query.

  -- column_name specifies the column name displayed in the subquery result set.

  – Each subquery can be a SELECT, VALUES, INSERT, UPDATE or DELETE statement.

  

  If a subquery is a DML statement with a RETURNING statement, the number of command output records of the INSERT statement is determined by the subquery. Assume that table T1 exists. Run the following statement:

  WITH CTE AS (INSERT INTO T1 VALUES(1,2) RETURNING *) INSERT INTO T1 SELECT * FROM CTE;

  The number of command output records of the preceding statement is determined by the following part instead of the entire statement. That is, the command output is "INSERT 0 1" instead of "INSERT 0 2".

  INSERT INTO T1 VALUES(1,2) RETURNING *

  – You can use MATERIALIZED or NOT MATERIALIZED to modify the CTE.

  • If MATERIALIZED is specified, the WITH query will be materialized, and a copy of the subquery result set is generated. The copy is directly queried at the reference point. Therefore, the WITH subquery cannot be jointly optimized with the SELECT statement trunk (for example, predicate pushdown and equivalence class transfer). In this scenario, you can use NOT MATERIALIZED for modification. If the WITH query can be executed as a subquery inline, the preceding optimization can be performed.
  • If the user does not explicitly declare the materialized attribute, comply with the following rules: If the CTE is referenced only once in the trunk statement to which it belongs and semantically supports inline execution, it will be rewritten as subquery inline execution. Otherwise, the materialized execution will be performed in CTE Scan mode.
  

  INSERT ON DUPLICATE KEY UPDATE does not support the WITH or WITH RECURSIVE clauses.

• plan_hint clause

  Follows the INSERT keyword in the /*+ */ format. It is used to optimize the plan of an INSERT statement block. For details, see Hint-based Optimization. In each statement, only the first /*+ plan_hint */ comment block takes effect as a hint. Multiple hints can be written.

• IGNORE

  When the INSERT statement uses the IGNORE keyword, some ERROR-level errors can be degraded to WARNING-level errors, and invalid values can be adjusted to the closest values based on error scenarios. GaussDB supports the following error codes for error downgrade:

  • Damage to the NOT NULL constraint
  • UNIQUE KEY conflict
  • No partition found for the inserted value
  • Unmatch between the inserted data and the specified partition
  • Multiple rows returned for a subquery
  • Oversized data
  • Time function overflow
  • Division by 0
  • Incorrect value
  

  1. INSERT IGNORE is supported only when sql_compatibility is set to 'MySQL', b_format_version set to '5.7', and b_format_dev_version set to 's1'.
  2. INSERT IGNORE does not support encrypted tables and foreign tables.
  3. INSERT IGNORE does not support PRIMARY KEY constraints or UNIQUE KEY constraints that take effect with a delay.
  4. The OIDs of each data type that supports damage to NOT NULL constraints with NULL values are TIMESTAMPOID, TIMESTAMPTZOID, TIMEOID, TIMETZOID, RELTIMEOID, INTERVALOID, TINTERVALOOID, SMALLDATETIMEOID, DATEOID, NAMEOID, POINTOID, PATHOID, POLYGONOID, CIRCLEOID, LSEGOID, BOXOID, JSONOID, JSONBOID, XMLOID, XMLTYPEOID, VARBITOID, NUMERICOID, CIDROID, INETOID, MACADDROID, NUMRANGEOID, INT8RANGEOID, INT4RANGEOID, TSRANGEOID, TSTZRANGEOID, DATERANGEOID, ABSTIMEOID, BOOLOID, INT8OID, INT4OID, INT2OID, INT1OID, FLOAT4OID, FLOAT8OID, CASHOID, UINT1OID, UINT2OID, UINT4OID, UINT8OID, BPCHAROID, VARCHAROID, NVARCHAR2OID, CHAROID, BYTEAOID, RAWOID, BLOBOID, CLOBOID, TEXTOID, and YEAROID. Table 1 lists the zero values of each data type.
  5. When the default zero value is used for calculation, exercise caution when using IGNORE to ignore the NOT NULL constraint.
  6. INSERT IGNORE does not support tables with global secondary indexes.
  7. An error generated on a CN is degraded to the warning information while an error generated on a DN is not degraded to the warning information.

  Table 1 Default zero values for the data type

  OID of Each Data Type	Default Zero Value
  INT8OID, INT4OID, INT2OID, INT1OID, UINT1OID, UINT2OID, UINT4OID, UINT8OID, FLOAT4OID, FLOAT8OID, NUMERICOID	0 or 0.00 (The number of 0s after the decimal point is specified by a parameter.)
  BPCHAROID, VARCHAROID, CHAROID, BYTEAOID, RAWOID, BLOBOID, NVARCHAR2OID, CLOBOID, TEXTOID, VARBITOID, NAMEOID	Empty string
  NUMRANGEOID, INT8RANGEOID, INT4RANGEOID, TSRANGEOID, TSTZRANGEOID, DATERANGEOID	empty
  TIMEOID	time '00:00:00'
  TIMETZOID	timetz '00:00:00'
  INTERVALOID	interval '00:00:00'
  TINTERVALOOID	tinterval(abstime '1970-01-01 00:00:00', abstime '1970-01-01 00:00:00')
  SMALLDATETIMEOID	smalldatetime '1970-01-01 00:00:00'
  ABSTIMEOID	abstime '1970-01-01 00:00:00'
  RELTIMEOID	reltime '00:00:00'
  TIMESTAMPOID, TIMESTAMPTZOID	1970-01-01 00:00:00
  DATEOID	1970-01-01
  YEAROID	0000
  POINTOID	(0,0)
  PATHOID, POLYGONOID	((0,0))
  CIRCLEOID	<(0,0),0>
  LSEGOID	[(0,0),(0,0)]
  BOXOID	(0,0),(0,0)
  JSONOID, JSONBOID, XMLOID	'null'
  XMLTYPEOID	'<null/>'
  CIDROID	0.0.0.0/32
  INETOID	0.0.0.0
  MACADDROID	00:00:00:00:00:00
  BOOLOID	f
  CASHOID	$0.00

• table_name

  Specifies the name of the target table where data will be inserted.

  Value range: an existing table name

  

  You can use database links to perform operations on remote tables. For details, see DATABASE LINK.

• subquery

  The inserted object can be a subquery. When a subquery is inserted, the subquery is regarded as a temporary view. The CHECK OPTION option can be added to the end of the subquery.

  [ WITH [ RECURSIVE ] with_query [, ...] ]
  SELECT [/*+ plan_hint */] [ ALL ]
  { * | {expression [ [ AS ] output_name ]} [, ...] }
  [ into_option ]
  [ FROM from_item [, ...] ]
  [ WHERE condition ]
  [ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ]
  [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
  [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
  [ into_option ];

  The specified subquery source from_item is as follows:

  {[ ONLY ] {table_name | view_name} [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
  |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
  |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

  If a subquery contains only one table, data is inserted into the table. If a subquery contains multiple tables or has nested relationships, check whether a key-preserved table exists to determine whether data can be inserted. For details about key-preserved tables and WITH CHECK OPTION, see CREATE VIEW.

• view_name

  Indicates the target view to be inserted.

  

  The restrictions on inserting views and subqueries are as follows:

  • The INSERT operation can be performed only on columns that directly reference user columns in the base table.
  • A subquery or view must contain at least one updatable column. For details about updatable columns, see CREATE VIEW.
  • Views and subqueries that contain the DISTINCT, GROUP BY, HAVING, LIMIT or OFFSET clause at the top layer are not supported.
  • Views and subqueries that contain set operations (UNION, INTERSECT, EXCEPT, and MINUS) at the top layer are not supported.
  • Views and subqueries whose target lists contain aggregate functions, window functions, or return set functions (such as array_agg, json_agg, and generate_series) are not supported.
  • Views with BEFORE/AFTER triggers but without INSTEAD OF triggers or INSTEAD rules are not supported.
  • The ON DUPLICATE KEY UPDATE function is not supported.
  • Table types supported in views and subqueries include ordinary tables, temporary tables, global temporary tables, partitioned tables, level-2 partitioned tables, Ustore tables, and Astore tables.
  • For a multi-table join view or subquery, only one base table can be inserted at a time.
  • When INSERT joins a view or subquery, explicitly specified columns to be inserted or implicitly specified columns (columns specified during view or subquery creation) cannot reference columns of non-key-preserved tables; if the WITH CHECK OPTION clause is used, INSERT operations cannot be performed on join columns in join views or subqueries. For details about the key-preserved table, see CREATE VIEW.
  • The INSERT operation cannot be performed on the system view.
• alias_name

  Specifies the table alias when the INSERT statement is used without AS alias.

  

  1. When the INSERT statement is used without AS alias, the table alias cannot be a keyword (such as SELECT and VALUE) or an expression. The alias must comply with the identifier naming rule.
  2. When the INSERT statement is used without AS alias, the table alias cannot be in the INSERT INTO table_name alias_name(alias_name.col1, ...,alias_name.coln) VALUES(xxx); format.
  3. When the INSERT statement is used without AS alias, data cannot be inserted into the specified partition.
• partition_clause

  Inserts data to a specified partition.

  PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } 

  For details about the keywords, see SELECT.

  If the value of the VALUE clause is inconsistent with that of the specified partition, an exception is displayed.

• column_name

  Specifies the name of a column in a table.

  • The column name can be qualified with a subcolumn name or array index, if needed.
  • 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 there is none. Inserting data into only some columns of a composite type leaves the other columns NULL.
  • The target column names column_name can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.
  • The target columns are the first N column names, if there are only N columns provided by the VALUE clause and QUERY.
  • The values provided by the VALUE clause and QUERY are joined with the corresponding columns from left to right in the table.

  Value range: an existing column

• expression

  Specifies an expression or a value to assign to the corresponding column.

  • In the INSERT ON DUPLICATE KEY UPDATE statement, expression can be VALUES(column_name) or EXCLUDED.column_name, indicating that the value of column_name corresponding to the conflict row is referenced. VALUES(column_name) can be nested in a compound expression, for example, VALUES(column_name)+1, VALUES(column_name)+VALUES(column_name), and function_name(VALUES(column_name)).
    

    • VALUES(column_name) can be used only in the ON DUPLICATE KEY UPDATE clause.
    • VALUES(column_name) cannot be used in the IN or NOT IN expressions.
  • If single-quotation marks are inserted into a column, the single-quotation marks need to be used for escape.
  • If the expression for any column is not of the correct data type, automatic type conversion will be attempted. If the attempt fails, data insertion fails, and the system returns an error message.
• DEFAULT

  Specifies the default value of a column. The value is NULL if no default value is assigned to it.

• query

  Specifies a query statement (SELECT statement) that uses the query result as the inserted data.

• RETURNING

  Returns the inserted rows. The syntax of the RETURNING list is identical to that of the output list of SELECT.

• output_expression

  Specifies an expression used to calculate the output result of the INSERT statement after each row is inserted.

  Value range: The expression can use any column in the table. You can use the asterisk (*) to return all columns of the inserted row.

• output_name

  Specifies a name to use for a returned column.

  Value range: a string. It must comply with the naming convention.

• ON DUPLICATE KEY UPDATE

  For a table with a unique constraint (UNIQUE INDEX or PRIMARY KEY), if the inserted data violates the unique constraint, the UPDATE clause is executed to update the conflicting rows. If NOTHING is specified for the UPDATE clause, no operation will be performed. You can use EXCLUDED. or VALUES() to select the column corresponding to the source data.

  For a table without a unique constraint, only insert is performed.

  • Triggers are supported. The execution sequence of triggers is determined by the actual execution process.
    • Executing INSERT will trigger the BEFORE INSERT and AFTER INSERT triggers.
    • Executing UPDATE will trigger the BEFORE INSERT, BEFORE UPDATE, and AFTER UPDATE triggers.
    • Executing UPDATE NOTHING will trigger the BEFORE INSERT trigger.
  • The unique constraint or primary key of DEFERRABLE is not supported.
  • If a table has multiple unique constraints and the inserted data violates multiple unique constraints, only the first row that has a conflict is updated. (The check sequence is closely related to index maintenance. Generally, the conflict check is performed on the index that is created first.)
  • Primary keys and unique index columns cannot be updated.
  • Foreign tables are not supported.
  • The WHERE clause and expression of UPDATE cannot contain sublinks.

Examples

• Insert a data record.
  Example:

  -- Create a table.
  gaussdb=# CREATE TABLE test_t1(col1 INT,col2 VARCHAR);
  
  -- Insert data.
  gaussdb=# INSERT INTO test_t1 (col1, col2) VALUES (1,'AB');
  
  -- Add values to some columns of the table.
  gaussdb=# INSERT INTO test_t1 (col1) VALUES (2);
  
  -- There is no parenthesis on the left of the VALUES keyword. Values must be added to all fields in the parenthesis on the right according to the table structure sequence.
  gaussdb=# INSERT INTO test_t1 VALUES (3,'AC');
  
  -- Query the table.
  gaussdb=# SELECT * FROM test_t1;
   col1 | col2 
  ------+------
      1 | AB
      2 | 
      3 | AC
  (3 rows)
  
  -- Delete.
  gaussdb=# DROP TABLE test_t1;

• Insert multiple data records.
  Example:

  -- Create a table.
  gaussdb=# CREATE TABLE test_t2(col1 INT,col2 VARCHAR);
  gaussdb=# CREATE TABLE test_t3(col1 INT,col2 VARCHAR);
  
  -- Insert multiple data records.
  gaussdb=# INSERT INTO test_t2 (col1, col2) VALUES (10,'AA'),(20,'BB'),(30,'CC');
  
  -- Query.
  gaussdb=# SELECT * FROM test_t2;
   col1 | col2 
  ------+------
     10 | AA
     20 | BB
     30 | CC
  (3 rows)
  
  -- Insert data in test_t2 into test_t3.
  gaussdb=# INSERT INTO test_t3 SELECT * FROM test_t2;
  
  -- Query.
  gaussdb=# SELECT * FROM test_t3;
   col1 | col2 
  ------+------
     10 | AA
     20 | BB
     30 | CC
  (3 rows)
  
  -- Delete.
  gaussdb=# DROP TABLE test_t2;
  DROP TABLE test_t3;

• ON DUPLICATE KEY UPDATE

  Example:

  -- Create a table.
  gaussdb=# CREATE TABLE test_t4 (id INT PRIMARY KEY, info VARCHAR(10));
  gaussdb=# INSERT INTO test_t4 VALUES (1, 'AA'), (2,'BB'), (3, 'CC');
  
  -- Use the ON DUPLICATE KEY UPDATE keyword.
  gaussdb=# INSERT INTO test_t4 VALUES (3, 'DD'), (4, 'EE') ON DUPLICATE KEY UPDATE info = VALUES(info);
  
  -- Query.
  gaussdb=# SELECT * FROM test_t4;
   id | info 
  ----+------
    1 | AA
    2 | BB
    4 | EE
    3 | DD
  
  -- Delete.
  gaussdb=# DROP TABLE test_t4;

• INSERT IGNORE

  Example 1: Damage to the NOT NULL constraint

  -- Create a table.
  gaussdb=# CREATE TABLE test_t5(f1 INT NOT NULL);
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  gaussdb=# INSERT IGNORE INTO test_t5 VALUES(NULL);
  WARNING:  null value in column "f1" violates not-null constraint
  DETAIL:  Failing row contains (null).
  INSERT 0 1
  
  -- Query the table.
  gaussdb=# SELECT * FROM test_t5;
   f1
  ----
    0
  (1 row)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t5;
  DROP TABLE

  Example 2: UNIQUE KEY conflict

  -- Create a table.
  gaussdb=# CREATE TABLE test_t6(f1 INT PRIMARY KEY);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_t6_pkey" for table "test_t6"
  CREATE TABLE
  
  -- Insert data.
  gaussdb=# INSERT INTO test_t6 VALUES(1);
  INSERT 0 1
  
  -- Use the IGNORE keyword.
  gaussdb=# INSERT IGNORE INTO test_t6 VALUES(1);
  INSERT 0 0
  
  -- Query the table.
  gaussdb=# SELECT * FROM test_t6;
   f1
  ----
    1
  (1 row)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t6;
  DROP TABLE

  Example 3: No partition found for the inserted value

  -- Create a table.
  gaussdb=# CREATE TABLE test_t7(f1 INT, f2 INT) PARTITION BY LIST(f1) (PARTITION p0 VALUES(1, 4, 7), PARTITION p1 VALUES (2, 5, 8));
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  gaussdb=# INSERT IGNORE INTO test_t7 VALUES(3, 5);
  INSERT 0 0
  
  -- Query the table.
  gaussdb=# SELECT * FROM test_t7;
   f1 | f2
  ----+----
  (0 rows)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t7;
  DROP TABLE

  Example 4: Unmatch between the inserted data and the specified partition

  -- Create a table.
  gaussdb=# CREATE TABLE test_t8(f1 INT NOT NULL, f2 TEXT, f3 INT) PARTITION BY RANGE(f1)(PARTITION p0 VALUES LESS THAN(5), PARTITION p1 VALUES LESS THAN(10), PARTITION p2 VALUES LESS THAN(15), PARTITION p3 VALUES LESS THAN(MAXVALUE));
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  gaussdb=# INSERT IGNORE INTO test_t8 PARTITION(p2) VALUES(20, 'Jan', 1);
  INSERT 0 0
  
  -- Query the table.
  gaussdb=# SELECT * FROM test_t8;
   f1 | f2 | f3
  ----+----+----
  (0 rows)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t8;
  DROP TABLE

  Example 5: Multiple rows returned for a subquery

  -- Create a table.
  gaussdb=# CREATE TABLE test_t9(f1 INT, f2 INT);
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  
  -- Insert data.
  gaussdb=# INSERT INTO test_t9 VALUES(1, 1), (2, 2), (3, 3);
  INSERT 0 3
  
  -- Use the IGNORE keyword.
  gaussdb=# INSERT IGNORE INTO test_t9 VALUES((SELECT f1 FROM test_t9), 0);
  WARNING:  more than one row returned by a subquery used as an expression
  CONTEXT:  referenced column: f1
  INSERT 0 1
  
  -- Query the table.
  gaussdb=# SELECT * FROM test_t9 WHERE f2 = 0;
   f1 | f2
  ----+----
      |  0
  (1 row)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t9;
  DROP TABLE

  Example 6: Oversized data

  -- Create a table.
  gaussdb=# CREATE TABLE test_t10(f1 VARCHAR(5));
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  gaussdb=# INSERT IGNORE INTO test_t10 VALUES('aaaaaaaaa');
  WARNING:  value too long for type character varying(5)
  CONTEXT:  referenced column: f1
  INSERT 0 1
  
  -- Query the table.
  gaussdb=# SELECT * FROM test_t10;
    f1
  -------
   aaaaa
  (1 row)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t10;
  DROP TABLE

  Example 7: Time function overflow

  -- Create a table.
  gaussdb=# CREATE TABLE test_t11(f1 DATETIME);
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f1' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  gaussdb=# INSERT IGNORE INTO test_t11 VALUES(date_sub('2000-01-01', INTERVAL 2001 YEAR));
  WARNING:  Datetime function: datetime field overflow
  CONTEXT:  referenced column: f1
  INSERT 0 1
  
  -- Query the table.
  gaussdb=# SELECT * FROM test_t11;
   f1
  ----
  
  (1 row)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t11;
  DROP TABLE

  Example 8: Division by 0

  -- Create a table.
  gaussdb=# CREATE TABLE test_t12(f1 INT);
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  gaussdb=# INSERT IGNORE INTO test_t12 VALUES(1/0);
  WARNING:  division by zero
  CONTEXT:  referenced column: f1
  INSERT 0 1
  
  -- Query the table.
  gaussdb=# SELECT * FROM test_t12;
   f1
  ----
  
  (1 row)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t12;
  DROP TABLE

  Example 9: Incorrect value

  -- Create a table.
  gaussdb=# CREATE TABLE test_t13(f0 INT, f1 FLOAT);
  NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'f0' as the distribution column by default.
  HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  gaussdb=# INSERT IGNORE INTO test_t13 VALUES(1, '1.11aaa');
  WARNING:  invalid input syntax for type real: "1.11aaa"
  LINE 1: INSERT IGNORE INTO test_t13 VALUES(1, '1.11aaa');
                                                ^
  CONTEXT:  referenced column: f1
  INSERT 0 1
  
  -- Query the table.
  gaussdb=# SELECT * FROM  test_t13;
   f0 |  f1
  ----+------
    1 | 1.11
  (1 row)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t13;
  DROP TABLE

• WITH [ RECURSIVE ] with_query [, ...]
  Example:

  -- Grade table.
  gaussdb=# CREATE TABLE grade (
      sid INT,
      course VARCHAR(20),
      score FLOAT
  );
  -- Student table.
  gaussdb=# CREATE TABLE student(
      sid INT PRIMARY KEY,
      class INT,
      name  VARCHAR(50),
      sex INT CHECK (sex = 0 or sex = 1)
  );
  
  -- Insert data.
  gaussdb=# WITH student_sid AS ( INSERT INTO student ( sid, CLASS, NAME, sex ) VALUES ( 1, 1, 'scott', 1 ) RETURNING sid ) 
      INSERT INTO grade ( sid, course, score )
      VALUE ( ( SELECT sid FROM student_sid ), 'match', '96' ),
  	  ( ( SELECT sid FROM student_sid ), 'chinese', '82' ),
  	  ( ( SELECT sid FROM student_sid ), 'english', '86' );
  
  -- Query data.
  gaussdb=# SELECT * FROM student;
   sid | class | name  | sex 
  -----+-------+-------+-----
     1 |     1 | scott |   1
  (1 row)
  
  gaussdb=# SELECT * FROM grade;
   sid | course  | score 
  -----+---------+-------
     1 | match   |    96
     1 | chinese |    82
     1 | english |    86
  (3 rows)
  
  -- Delete.
  gaussdb=# DROP TABLE student;
  gaussdb=# DROP TABLE grade;

Suggestions

• VALUES

  When you run the INSERT statement to insert data in batches, you are advised to combine multiple records into one statement to improve data loading performance.

  Example:

  INSERT INTO sections VALUES (30, 'Administration', 31, 1900),(40, 'Development', 35, 2000), (50, 'Development' , 60 , 2001);

  If values of an INSERT statement are distributed on a DN, GaussDB can push the statement down to the corresponding DN for execution. Currently, only constants, simple expressions, and pushdown functions (provolatile in pg_proc is set to 'i') are supported. If a column in the table has a default value, the value must be a constant or a simple expression. Neither a single-value statement nor a multi-value statement can be pushed down to a single DN.