INSERT

Function

INSERT inserts new rows into a table.

Precautions

• You must have the INSERT permission on a table in order to insert into it.
• Use of the RETURNING clause requires the SELECT permission on all columns mentioned in RETURNING.
• If you use the query clause to insert rows from a query, you of course need to have the SELECT permission on any table or column used in the query.
• If you use the OVERWRITE clause to insert, you must have the SELECT and TRUNCATE permissions on the table.
• When you connect to a database compatible to Teradata and td_compatible_truncation is on, a long character 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 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
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT [/*+ plan_hint */] [ IGNORE | OVERWRITE ] INTO table_name [ partition_clause ] [ AS alias ] [ ( column_name [, ...] ) ]
    { DEFAULT VALUES
    | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...] 
    | query }
    [ ON DUPLICATE KEY duplicate_action | ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];

where partition_clause can be:
    PARTITION ( partition_name )
    | PARTITION FOR ( partition_key_value [, ...] )

and duplicate_action can be:

    UPDATE { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
           } [, ...]

and conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] )
                  } [, ...]
              [ WHERE condition ]

Parameter Description

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

  The WITH clause allows you to specify one or more subqueries that can be referenced by name in the primary query, equal to temporary table.

  If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

  The with_query detailed format is as follows:

  with_query_name [ ( column_name [, ...] ) ] AS
  ( {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.

• plan_hint clause

  Following the keyword in the /*+ */ format, hints are used to optimize the plan generated by a specified statement block. For details, see Hint-based Tuning.

• IGNORE

  Specifies that the data that duplicates an existing primary key or unique key value will be ignored.

  For details, see UPSERT.

• OVERWRITE

  Specifies the overwrite mode. After this mode is used, the original data is cleared and only the newly inserted data exists.

  You can specify the columns on which OVERWRITE takes effect, and the other columns will keep their original data. If a column has no original data, its value is NULL.

  

  • Do not perform OVERWRITE and INSERT INTO operations at the same time. Otherwise, data written in real time may be unexpectedly cleared.
  • OVERWRITE applies to the scenario where a large amount of data is imported. You are not advised to use OVERWRITE to insert a small amount of data.
  • Do not concurrently perform insert overwrite operations on the same table. Otherwise, an error similar to "tuple concurrently updated." will be reported.
  • If the cluster is being scaled out and data redistribution is required for the table where INSERT OVERWRITE is performed, INSERT OVERWRITE clears the current data and automatically distributes the inserted data to the new nodes after scale-out. If INSERT OVERWRITE and the data redistribution of the table are performed at the same time, INSERT OVERWRITE will interrupt the data redistribution of the table.
  • OVERWRITE allows you to insert data into an OBS foreign table in overwrite mode, where the data in the original directory will be overwritten.
  • The foreign table supports OVERWRITE single-partition and multi-level Value partitioning. This function is supported only in 9.1.0.100 and later versions.

    Example: INSERT OVERWRITE into table_name partition for (p1_value, p2_value...) ...

• table_name

  Specifies the name of the target table.

  Value range: an existing table name

• AS

  Specifies an alias for the target table table_name. alias indicates the alias name.

• column_name

  Specifies the name of a column in a table.

  • The column name can be qualified with a subfield name or array subscript, 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 into only some fields of a composite column leaves the other fields 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 supplied by the value clause or query are associated with the explicit or implicit column list left-to-right.

  Value range: an existing column name

• partition_name

  Specifies the name of a partition. Only clusters of version 8.2.1 or later support this option.

  Value range: An existing partition name.

• partition_key_value

  Specifies the key value of a partition.

  The value specified by PARTITION FOR ( partition_key_value [, ...] ) can uniquely identify a partition.

  Value range: value range of the partition key for the partition to be renamed

• expression

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

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

  Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

  CREATE TABLE tt01 (id int,content varchar(50)); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE INSERT INTO tt01 values (1,'Jack say ''hello'''); INSERT 0 1 INSERT INTO tt01 values (2,'Rose do 50%'); INSERT 0 1 INSERT INTO tt01 values (3,'Lilei say ''world'''); INSERT 0 1 INSERT INTO tt01 values (4,'Hanmei do 100%'); INSERT 0 1 SELECT * FROM tt01; id | content ----+------------------- 3 | Lilei say 'world' 4 | Hanmei do 100% 1 | Jack say 'hello' 2 | Rose do 50% (4 rows)

• DEFAULT

  All columns will be filled with their default values. The value is NULL if no specified default value has been assigned to it.

• query

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

• ON DUPLICATE KEY

  Specifies that the data that duplicates an existing primary key or unique key value will be updated.

  duplicate_action specifies the columns and data to be updated.

  For details, see UPSERT.

• ON CONFLICT

  Specifies that the data that duplicates an existing primary key or unique key value will be ignored or updated.

  conflict_target specifies the column name index_column_name, expression index_expression that contains multiple column names, or constraint name constraint_name. It is used to infer whether there is a unique index from the column name, the expression that contains multiple column names, or the constraint name. index_column_name and index_expression must comply with the index column format of CREATE INDEX.

  conflict_action specifies the policy to be executed upon a primary key or unique constraint conflict. There are two available actions:

  • DO NOTHING: Ignore the conflict.
  • DO UPDATE SET: Update data upon a conflict. The columns and data to be updated must be specified.

  For details, see UPSERT.

• RETURNING

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

• output_expression

  An expression used to calculate the output of the INSERT command after each row is inserted.

  Value range: The expression can use any field in the table. Write * to return all columns of the inserted row(s).

• output_name

  A name to use for a returned column.

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

Examples

Create the reason_t1 table.

1
2
3
4
5
6

CREATE TABLE reason_t1
(
    TABLE_SK          INTEGER               ,
    TABLE_ID          VARCHAR(20)           ,
    TABLE_NA          VARCHAR(20)
);

Insert a record into a table.

1

INSERT INTO reason_t1(TABLE_SK, TABLE_ID, TABLE_NA) VALUES (1, 'S01', 'StudentA');

Insert a record into a table. This command is equivalent to the last one.

1

INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA');

Insert records whose TABLE_SK is less than 1 into the table.

1

INSERT INTO reason_t1 SELECT * FROM reason_t1 WHERE TABLE_SK < 1;

Insert records into the table.

1
2
3
4
5
6
7
8

INSERT INTO reason_t1 VALUES (1, 'S01', 'StudentA'),(2, 'T01', 'TeacherA'),(3, 'T02', 'TeacherB');
SELECT * FROM reason_t1 ORDER BY 1;
 TABLE_SK | TABLE_ID | TABLE_NAME
----------+----------+------------
        1 |      S01 |   StudentA
        2 |      T01 |   TeacherA
        3 |      T02 |   TeacherB
(3 rows)

Use INSERT OVERWRITE to update data in a table, that is, insert data to overwrite the old data.

1
2
3
4
5
6

INSERT OVERWRITE INTO reason_t1 values (4, 'S02', 'StudentB');
SELECT * FROM reason_t1 ORDER BY 1;
 TABLE_SK | TABLE_ID | TABLE_NAME
----------+----------+------------
        4 |      S02 |   StudentB
(1 rows)

Insert data back into the reason_t1 table.

INSERT INTO reason_t1 SELECT * FROM reason_t1;

Specify default values for independent columns.

INSERT INTO reason_t1 VALUES (5, 'S03', DEFAULT);

Insert some data in a table to another table: Use the WITH subquery to obtain a temporary table temp_t, and then insert all data in temp_t to another table reason_t1.

WITH temp_t AS (SELECT * FROM reason_t1) INSERT INTO reason_t1 SELECT * FROM temp_t ORDER BY 1;

Insert data into a partition of a partitioned table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE test_range_row(a int, d int)
DISTRIBUTE BY hash(a) PARTITION BY RANGE(d)
(
    PARTITION p1 values LESS THAN (60),
    PARTITION p2 values LESS THAN (75),
    PARTITION p3 values LESS THAN (90),
    PARTITION p4 VALUES LESS THAN (maxvalue)
);
INSERT OVERWRITE INTO test_range_row PARTITION(p1) VALUES(55,51);
INSERT OVERWRITE INTO test_range_row PARTITION(p3) VALUES(85,80);

DELETE FROM test_range_row PARTITION(p1);