INSERT

功能描述

向表中添加一行或多行数据。

注意事项

  • 本章节只包含shark新增的语法，原openGauss的语法未做删除和修改。原openGauss的INSERT语法请参考章节INSERT。
  • 新增支持table_hint子句。

语法格式

1. [ WITH [ RECURSIVE ] with_query [, ...] ]
2. INSERT [/*+ plan_hint */] [INTO] table_name [partition_clause] [ AS alias ] [table_hint_clause] [ ( column_name [, ...] ) ]
3. { DEFAULT VALUES
4. | VALUES {( { expression | DEFAULT } [, ...] ) }[, ...]
5. | query }
6. [ ON DUPLICATE KEY UPDATE { NOTHING | { column_name = { expression | DEFAULT } } [, ...] [ WHERE condition ] }]
7. [ RETURNING {* | {output_expression [ [ AS ] output_name ] }[, ...]} ];
  • 其中table_hint子句table_hint_clause为：

1. WITH ( <table_hint> [, ...] )

参数说明

  • JOIN

JOIN包含 INNER JOIN，LEFT JOIN，RIGHT JOIN，FULL JOIN，CROSS JOIN。 - WITH ( <table_hint> [, …] )

  • 不同于SELECT子句，针对单个hint，WITH可选，针对INSERT子句，WITH必选，table_hint支持给出一个列表选项，列表通过逗号或者空格分隔，即WITH (hint1)、WITH (hint1, hint2, …)、WITH (hint1 hint2 …)均支持，(hint1)不支持。
  • 支持的hint包括NOLOCK、READUNCOMMITTED、UPDLOCK、REPEATABLEREAD、SERIALIZABLE、READCOMMITTED、TABLOCK、TABLOCKX、PAGLOCK、ROWLOCK、NOWAIT、READPAST、XLOCK、SNAPSHOT、NOEXPAND。
  • 当上述hint需要当做标识符，用于列名、变量名等，需要设置d_format_behavior_compat_options = ‘enable_table_hint_identifier’，该变量默认值d_format_behavior_compat_options = ‘’。
  • 所有的hint仅语法支持，无实际含义。
  • 针对hint，会打印相关NOTICE信息。

table_hint子句示例

1. create table t1(c1 int, c2 int);

3. insert into t1 values(1, 2);

5. insert into t1 with (nowait) values(3, 4);
6. NOTICE:  The nowait option is currently ignored

8. insert into t1 with (nowait) (c1, c2) values(5, 6);
9. NOTICE:  The nowait option is currently ignored

11. insert into t1 with (xlock, nowait) values(7, 8);
12. NOTICE:  The xlock option is currently ignored
13. NOTICE:  The nowait option is currently ignored

15. insert into t1 as table_t1 with (nowait) (c1, c2) values(9, 10);
16. NOTICE:  The nowait option is currently ignored

18. -- no into in insert statement
19. insert t1 values(1, 2);

21. insert t1 with (nowait) values(3, 4);
22. NOTICE:  The nowait option is currently ignored

24. insert t1 with (nowait) (c1, c2) values(5, 6);
25. NOTICE:  The nowait option is currently ignored

27. insert t1 with (xlock, nowait) values(7, 8);
28. NOTICE:  The xlock option is currently ignored
29. NOTICE:  The nowait option is currently ignored

31. insert t1 as table_t1 with (nowait, nolock) (c1, c2) values(9, 10);
32. NOTICE:  The nowait option is currently ignored
33. NOTICE:  The nolock option is currently ignored

35. CREATE TABLE partition_table1
36. (
37. WR_RETURNED_DATE_SK       INTEGER,
38. WR_RETURNED_TIME_SK       INTEGER
39. )
40. PARTITION BY RANGE(WR_RETURNED_DATE_SK)
41. (
42. PARTITION P1 VALUES LESS THAN(2450815),
43. PARTITION P2 VALUES LESS THAN(2451179),
44. PARTITION P8 VALUES LESS THAN(MAXVALUE)
45. );

47. insert into partition_table1 with (nolock, nowait) values(2451176, 1);
48. NOTICE:  The nolock option is currently ignored
49. NOTICE:  The nowait option is currently ignored

51. insert into partition_table1 partition (p1) with (nolock, nowait) values(2450000, 1);
52. NOTICE:  The nolock option is currently ignored
53. NOTICE:  The nowait option is currently ignored

55. insert into partition_table1 partition for (2451176) with (nolock, nowait) values(2451176, 1);
56. NOTICE:  The nolock option is currently ignored
57. NOTICE:  The nowait option is currently ignored

59. insert into partition_table1 partition for (2451176) as table1_alias with (nolock, nowait) values(2451176, 1);
60. NOTICE:  The nolock option is currently ignored
61. NOTICE:  The nowait option is currently ignored

相关链接

INSERT