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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
# name: test/sql/insert/insert_by_name.test
# description: Test parallel insert from many groups
# group: [insert]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE integers(i INTEGER, j INTEGER);
# partial names
# i=NULL, j=42
statement ok
INSERT INTO integers BY NAME SELECT 42 AS j
# i=84, j=NULL
statement ok
INSERT INTO integers BY NAME SELECT 84 AS i
# re-ordered names
# i=9, j=99
statement ok
INSERT INTO integers BY NAME SELECT 99 AS j, 9 AS i
# i=1, j=10
statement ok
INSERT INTO integers BY POSITION SELECT 1 AS j, 10 AS i
query II
FROM integers
----
NULL 42
84 NULL
9 99
1 10
# column that needs to be quoted
statement ok
CREATE TABLE "My Table"("My Column 1" INT, "My Column 2" INT);
statement ok
INSERT INTO "My Table" BY NAME SELECT 1 AS "My Column 2"
query II
FROM "My Table"
----
NULL 1
# name does not exist
statement error
INSERT INTO integers BY NAME SELECT 1 AS xxx
----
xxx
# duplicate names
statement error
INSERT INTO integers BY NAME SELECT 1 AS i, 2 AS i
----
i
statement error
INSERT INTO integers (i, i) SELECT 1, 2
----
i
# rowid
statement error
INSERT INTO integers BY NAME SELECT 1 AS rowid
----
rowid
# generated column
statement ok
CREATE TABLE tbl (
price INTEGER,
total_price AS ((price)::DATE)
);
statement error
INSERT INTO tbl BY NAME SELECT 1 AS total_price
----
generated
# insert by name with values list
statement error
INSERT INTO integers BY NAME VALUES (42, 84);
----
INSERT BY NAME
# insert by name with columns list
statement error
INSERT INTO integers BY NAME (i) SELECT 1 AS j
----
explicit column list
statement ok
INSERT INTO integers BY POSITION VALUES (42, 84);
statement ok
CREATE TABLE tbl2 (a INTEGER, b INTEGER PRIMARY KEY);
statement ok
INSERT INTO tbl2 BY NAME (SELECT 22 AS b);
query II
FROM tbl2
----
NULL 22
# INSERT OR REPLACE BY NAME with partial columns
statement ok
INSERT OR REPLACE INTO tbl2 BY NAME (SELECT 22 AS b);
query II
FROM tbl2
----
NULL 22
# INSERT OR REPLACE BY NAME with all columns
statement ok
INSERT OR REPLACE INTO tbl2 BY NAME (SELECT 22 AS b, 1 as a);
query II
FROM tbl2
----
1 22
|
