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 into it. If a user is granted 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. You cannot specify values for generated columns in INSERT, but you can specify the keyword DEFAULT.

Syntax

INSERT [/*+ plan_hint */] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE column_name = {expression | DEFAULT}[, ...]]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

INSERT [/*+ plan_hint */] [IGNORE] [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET column_name = {expression | DEFAULT}[, ...]
    [ON DUPLICATE KEY UPDATE column_name = {expression | DEFAULT}[, ...]]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

INSERT [/*+ plan_hint */] [IGNORE] [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    query
    [ON DUPLICATE KEY UPDATE column_name = {expression | DEFAULT}[, ...]]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ]; 

INSERT [/*+ plan_hint */] [IGNORE] [INTO] view_name
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list), ...]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

INSERT [/*+ plan_hint */] [IGNORE] [INTO] view_name
    SET column_name = {expression | DEFAULT}[, ...]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

INSERT [/*+ plan_hint */] [IGNORE] [INTO] view_name
    [(col_name [, col_name] ...)]
    query
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

Parameters

• plan_hint clause

  Follows the INSERT keyword in the /*+ */ format. It is used to optimize the plan of an INSERT statement block. 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 downgrade scenarios:

  • Violation of 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
  • sql_mode set to loose
• table_name

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

  Value range: existing table names.

• partition_name

  Specifies the name of the partition.

• 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 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 (specified by 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 joined with the corresponding columns from left to right in the table.

  Value range: existing column names.

• 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), 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.

  • 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 there is no default value.

• 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.

• 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. When using UPDATE, you can use VALUES() to select the column corresponding to the source data.

  • 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.
  • expression does not support subquery expressions.
  • If query is a subquery, the column name in the subquery cannot be used.
  • When the ON DUPLICATE KEY UPDATE clause is used to update multiple columns, the result of the previous UPDATE statement does not affect the subsequent results. In addition, the same column cannot be set for multiple times.
• view_name

  Specifies the target view to be inserted.

  

  The restrictions on inserting views are as follows:

  1. Only columns that directly reference user columns in the base table can be inserted.
  2. A view must contain at least one updatable column. For details about updatable columns, see CREATE VIEW.
  3. Views that contain the DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clause at the top layer are not supported.
  4. Views that contain set operations (UNION and EXCEPT) at the top layer are not supported.
  5. Views 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. The ON DUPLICATE KEY UPDATE function is not supported.
  7. Table types supported in views include ordinary tables, temporary tables, global temporary tables, partitioned tables, Ustore tables, and Astore tables.
  8. In a multi-table join view, only one base table can be inserted at a time.
  9. When a view is joined, explicitly specified columns to be inserted or implicitly specified columns (columns specified during view creation) cannot reference columns of non-key-preserved tables For details about the key-preserved table, see CREATE VIEW.
  10. System views cannot be inserted.

Example

• Insert a data record.
  Example:

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

• Insert multiple data records.
  Example:

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

• ON DUPLICATE KEY UPDATE

  Example:

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

• INSERT IGNORE

  Example 1: Violation of the NOT NULL constraint

  -- Create a table.
  m_db=# CREATE TABLE test_t5(f1 INT NOT NULL);
  CREATE TABLE
  
  -- Use the IGNORE keyword.
  m_db=# 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.
  m_db=# SELECT * FROM test_t5;
   f1
  ----
    0
  (1 row)
  
  -- Drop the table.
  m_db=# DROP TABLE test_t5;

  Example 2: UNIQUE KEY conflict

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

  Example 3: No partition found for the inserted value

  -- Create a table.
  m_db=# 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.
  m_db=# 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.
  m_db=# SELECT * FROM test_t7;
   f1 | f2
  ----+----
  (0 rows)
  
  -- Drop the table.
  m_db=# DROP TABLE test_t7;
  DROP TABLE

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

  -- Create a table.
  m_db=# 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.
  m_db=# 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.
  m_db=# SELECT * FROM test_t8;
   f1 | f2 | f3
  ----+----+----
  (0 rows)
  
  -- Drop the table.
  m_db=# DROP TABLE test_t8;
  DROP TABLE

  Example 5: Multiple rows returned for a subquery

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

• Insert a view.
  Example:

  -- Create a schema.
  m_db=# CREATE SCHEMA ins_view;
  CREATE SCHEMA
  m_db=# SET CURRENT_SCHEMA = 'ins_view';
  SET
  
  -- Create tables.
  m_db=# CREATE TABLE t1 (x1 int, y1 int);
  CREATE TABLE
  m_db=# CREATE TABLE t2 (x2 int PRIMARY KEY, y2 int);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2"
  CREATE TABLE
  
  -- Create a single table view.
  m_db=# CREATE VIEW v_ins1 AS SELECT * FROM t1;
  CREATE VIEW
  m_db=# CREATE VIEW v_ins2 AS SELECT * FROM t1 WHERE y1 < 3;
  CREATE VIEW
  
  -- Insert data into t1 through a view.
  m_db=# INSERT INTO v_ins1 VALUES (1, 1);
  INSERT 0 1
  m_db=# INSERT INTO v_ins2 VALUES (5, 5);
  INSERT 0 1
  
  -- Create a multi-table view.
  m_db=# CREATE VIEW vv_ins AS SELECT * FROM t1, t2 WHERE x1 = x2;
  CREATE VIEW
  
  -- Insert data into t1 through a multi-table view.
  m_db=# INSERT INTO vv_ins (x1, y1) VALUES (2, 2);
  INSERT 0 1
  
  -- Drop the schema.
  m_db=# DROP SCHEMA ins_view;
  NOTICE:  drop cascades to 5 other objects
  DETAIL:  drop cascades to table t1
  drop cascades to table t2
  drop cascades to view v_ins1
  drop cascades to view v_ins2
  drop cascades to view vv_ins
  DROP SCHEMA

Suggestions

• VALUES

  When you use 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);