INSERT

Description

Inserts one or more rows of data into a table.

Precautions

• You must have the INSERT permission on a table to insert data to it. If a user is granted with the INSERT ANY TABLE permission, the user has the USAGE permission on all schemas except system schemas and the INSERT permission on tables in these schemas.
• 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.
• The generated column cannot be directly written. In the INSERT statement, values cannot be specified for generated columns, but the keyword DEFAULT can be specified.
• 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) to 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

[ 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, it allows to reference a SELECT subquery 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 'B', b_format_version set to '5.7', and b_format_dev_version set to 's1'.
  2. INSERT IGNORE does not support encrypted tables, foreign tables, and MOTs.
  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, YEAROID, and SET. The following table 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.

  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, SET	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

  Specifies the target view to be inserted.

  

  The restrictions on inserting views and subqueries are as follows:

  1. Only columns that directly reference user columns in the base table can be inserted.
  2. A subquery or view must contain at least one updatable column. For details about updatable columns, see CREATE VIEW.
  3. Views and subqueries that contain the DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clause at the top layer are not supported.
  4. Views and subqueries that contain set operations (UNION, INTERSECT, EXCEPT, and MINUS) at the top layer are not supported.
  5. 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.
  6. Views with BEFORE/AFTER triggers but without INSTEAD OF triggers or INSTEAD rules are not supported.
  7. The ON DUPLICATE KEY UPDATE function is not supported.
  8. 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.
  9. For a multi-table join view or subquery, only one base table can be inserted at a time.
  10. 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.
  11. System views cannot be inserted.
• 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 to the specified partition.
• partition_clause

  Inserts data to a specified partition.

  PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } |
  SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_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.

  For details, see CREATE TABLE SUBPARTITION.

• 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 supplied by the VALUE clause or query.
  • The values provided by the VALUE clause and query are associated 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 in 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 on the conflicting row to complete the update. For a table without a unique constraint, only the insert operation is performed. If NOTHING is specified for UPDATE, this insertion is ignored. You can use EXCLUDED. or VALUES() to select the column corresponding to the source data.

  • 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.)
  • If multiple rows are inserted and these rows conflict with the same row in the table, the system inserts or updates the first row and then updates other rows in sequence.
  • 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');
  
  -- Insert data into 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)
  
  -- Drop the table.
  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 the table.
  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 the table.
  gaussdb=# SELECT * FROM test_t3;
   col1 | col2 
  ------+------
     10 | AA
     20 | BB
     30 | CC
  (3 rows)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t2;
  gaussdb=# 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 the table.
  gaussdb=# SELECT * FROM test_t4;
   id | info 
  ----+------
    1 | AA
    2 | BB
    4 | EE
    3 | DD
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_t4;

• INSERT IGNORE

  Example 1: Damage to the NOT NULL constraint

  -- Create a B-compatible database.
  gaussdb=# CREATE DATABASE test DBCOMPATIBILITY ='B';
  gaussdb=# \c test
  -- Set the pre-installation parameters.
  test=# set b_format_version = '5.7';
  test=# set b_format_dev_version = 's1';
  
  -- Create a table.
  test=# CREATE TABLE test_t5(f1 INT NOT NULL);
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  test=# 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.
  test=# SELECT * FROM test_t5;
   f1
  ----
    0
  (1 row)
  
  -- Delete the table.
  test=# DROP TABLE test_t5;

  Example 2: UNIQUE KEY conflict

  -- Create a table.
  test=# 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.
  test=# INSERT INTO test_t6 VALUES(1);
  INSERT 0 1
  
  -- Use the IGNORE keyword.
  test=# INSERT IGNORE INTO test_t6 VALUES(1);
  WARNING:  duplicate key value violates unique constraint "test_t6_pkey"
  INSERT 0 0
  
  -- Query the table.
  test=# SELECT * FROM test_t6;
   f1
  ----
    1
  (1 row)
  
  -- Delete the table.
  test=# DROP TABLE test_t6;
  DROP TABLE

  Example 3: No partition found for the inserted value

  -- Create a table.
  test=# CREATE TABLE test_t7(f1 INT, f2 INT) PARTITION BY LIST(f1) (PARTITION p0 VALUES(1, 4, 7), PARTITION p1 VALUES (2, 5, 8));
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  test=# INSERT IGNORE INTO test_t7 VALUES(3, 5);
  WARNING:  inserted partition key does not map to any table partition
  INSERT 0 0
  
  -- Query the table.
  test=# SELECT * FROM test_t7;
   f1 | f2
  ----+----
  (0 rows)
  
  -- Delete the table.
  test=# DROP TABLE test_t7;
  DROP TABLE

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

  -- Create a table.
  test=# 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));
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  test=# INSERT IGNORE INTO test_t8 PARTITION(p2) VALUES(20, 'Jan', 1);
  WARNING:  inserted partition key does not map to the table partition
  DETAIL:  N/A.
  INSERT 0 0
  
  -- Query the table.
  test=# SELECT * FROM test_t8;
   f1 | f2 | f3
  ----+----+----
  (0 rows)
  
  -- Delete the table.
  test=# DROP TABLE test_t8;
  DROP TABLE

  Example 5: Multiple rows returned for a subquery

  -- Create a table.
  test=# CREATE TABLE test_t9(f1 INT, f2 INT);
  CREATE TABLE
  
  -- Insert data.
  test=# INSERT INTO test_t9 VALUES(1, 1), (2, 2), (3, 3);
  INSERT 0 3
  
  -- Use the IGNORE keyword.
  test=# 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.
  test=# SELECT * FROM test_t9 WHERE f2 = 0;
   f1 | f2
  ----+----
      |  0
  (1 row)
  
  -- Delete the table.
  test=# DROP TABLE test_t9;
  DROP TABLE

  Example 6: Oversized data

  -- Create a table.
  test=# CREATE TABLE test_t10(f1 VARCHAR(5));
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  test=# 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.
  test=# SELECT * FROM test_t10;
    f1
  -------
   aaaaa
  (1 row)
  
  -- Delete the table.
  test=# DROP TABLE test_t10;
  DROP TABLE

  Example 7: Time function overflow

  -- Create a table.
  test=# CREATE TABLE test_t11(f1 DATETIME);
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  test=# 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.
  test=# SELECT * FROM test_t11;
   f1
  ----
  
  (1 row)
  
  -- Delete the table.
  test=# DROP TABLE test_t11;
  DROP TABLE

  Example 8: Division by 0

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

  Example 9: Incorrect value

  -- Create a table.
  test=# CREATE TABLE test_t13(f1 FLOAT);
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  test=# INSERT IGNORE INTO test_t13 VALUES('1.11aaa');
  WARNING:  invalid input syntax for type real: "1.11aaa"
  LINE 1: INSERT IGNORE INTO test_t13 VALUES('1.11aaa');
                                             ^
  CONTEXT:  referenced column: f1
  INSERT 0 1
  
  -- Query the table.
  test=# SELECT * FROM  test_t13;
    f1
  ------
   1.11
  (1 row)
  
  
  -- Delete the table.
  test=# DROP TABLE test_t13;
  -- Drop a database. (Change the database name based on the actual situation.)
  test=# \c test;
  test=# DROP DATABASE test;

• 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 ), 'math', '96' ),
  	  ( ( SELECT sid FROM student_sid ), 'chinese', '82' ),
  	  ( ( SELECT sid FROM student_sid ), 'english', '86' );
  
  -- Query the table.
  gaussdb=# SELECT * FROM student;
   sid | class | name  | sex 
  -----+-------+-------+-----
     1 |     1 | scott |   1
  (1 row)
  
  gaussdb=# SELECT * FROM grade;
   sid | course  | score 
  -----+---------+-------
     1 | math    |    96
     1 | chinese |    82
     1 | english |    86
  (3 rows)
  
  -- Delete the table.
  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);