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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
|
--
-- UPDATE syntax tests
--
CREATE TABLE update_test (
a INT DEFAULT 10,
b INT,
c TEXT
);
CREATE TABLE upsert_test (
a INT PRIMARY KEY,
b TEXT
);
INSERT INTO update_test VALUES (5, 10, 'foo');
INSERT INTO update_test(b, a) VALUES (15, 10);
SELECT * FROM update_test;
a | b | c
----+----+-----
5 | 10 | foo
10 | 15 |
(2 rows)
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
SELECT * FROM update_test;
a | b | c
----+---+-----
10 | | foo
10 | |
(2 rows)
-- aliases for the UPDATE target table
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
SELECT * FROM update_test;
a | b | c
----+----+-----
10 | 10 | foo
10 | 10 |
(2 rows)
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
a | b | c
----+----+-----
10 | 20 | foo
10 | 20 |
(2 rows)
--
-- Test VALUES in FROM
--
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
WHERE update_test.b = v.j;
SELECT * FROM update_test;
a | b | c
-----+----+-----
100 | 20 | foo
100 | 20 |
(2 rows)
-- fail, wrong data type:
UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i, j)
WHERE update_test.b = v.j;
ERROR: column "a" is of type integer but expression is of type record
LINE 1: UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i...
^
HINT: You will need to rewrite or cast the expression.
--
-- Test multiple-set-clause syntax
--
INSERT INTO update_test SELECT a,b+1,c FROM update_test;
SELECT * FROM update_test;
a | b | c
-----+----+-----
100 | 20 | foo
100 | 20 |
100 | 21 | foo
100 | 21 |
(4 rows)
UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
SELECT * FROM update_test;
a | b | c
-----+----+-------
100 | 20 |
100 | 21 |
10 | 31 | bugle
10 | 32 | bugle
(4 rows)
UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
SELECT * FROM update_test;
a | b | c
-----+----+-----
100 | 20 |
100 | 21 |
11 | 41 | car
11 | 42 | car
(4 rows)
-- fail, multi assignment to same column:
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
ERROR: multiple assignments to same column "b"
-- uncorrelated sub-select:
UPDATE update_test
SET (b,a) = (select a,b from update_test where b = 41 and c = 'car')
WHERE a = 100 AND b = 20;
SELECT * FROM update_test;
a | b | c
-----+----+-----
100 | 21 |
11 | 41 | car
11 | 42 | car
41 | 11 |
(4 rows)
-- correlated sub-select:
UPDATE update_test o
SET (b,a) = (select a+1,b from update_test i
where i.a=o.a and i.b=o.b and i.c is not distinct from o.c);
SELECT * FROM update_test;
a | b | c
----+-----+-----
21 | 101 |
41 | 12 | car
42 | 12 | car
11 | 42 |
(4 rows)
-- fail, multiple rows supplied:
UPDATE update_test SET (b,a) = (select a+1,b from update_test);
ERROR: more than one row returned by a subquery used as an expression
-- set to null if no rows supplied:
UPDATE update_test SET (b,a) = (select a+1,b from update_test where a = 1000)
WHERE a = 11;
SELECT * FROM update_test;
a | b | c
----+-----+-----
21 | 101 |
41 | 12 | car
42 | 12 | car
| |
(4 rows)
-- these should work, but don't yet:
UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 100)) AS v(i, j)
WHERE update_test.a = v.i;
ERROR: number of columns does not match number of values
LINE 1: UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 100)) ...
^
UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 101)) AS v(i, j)
WHERE update_test.a = v.i;
ERROR: syntax error at or near "ROW"
LINE 1: UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 101...
^
-- if an alias for the target table is specified, don't allow references
-- to the original table name
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
ERROR: invalid reference to FROM-clause entry for table "update_test"
LINE 1: UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a...
^
HINT: Perhaps you meant to reference the table alias "t".
-- Make sure that we can update to a TOASTed value.
UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
SELECT a, b, char_length(c) FROM update_test;
a | b | char_length
----+-----+-------------
21 | 101 |
| |
41 | 12 | 10000
42 | 12 | 10000
(4 rows)
-- Check multi-assignment with a Result node to handle a one-time filter.
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE update_test t
SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
WHERE CURRENT_USER = SESSION_USER;
QUERY PLAN
------------------------------------------------------------------
Update on public.update_test t
-> Result
Output: $1, $2, t.c, (SubPlan 1 (returns $1,$2)), t.ctid
One-Time Filter: ("current_user"() = "session_user"())
-> Seq Scan on public.update_test t
Output: t.c, t.a, t.ctid
SubPlan 1 (returns $1,$2)
-> Seq Scan on public.update_test s
Output: s.b, s.a
Filter: (s.a = t.a)
(10 rows)
UPDATE update_test t
SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
WHERE CURRENT_USER = SESSION_USER;
SELECT a, b, char_length(c) FROM update_test;
a | b | char_length
-----+----+-------------
101 | 21 |
| |
12 | 41 | 10000
12 | 42 | 10000
(4 rows)
-- Test ON CONFLICT DO UPDATE
INSERT INTO upsert_test VALUES(1, 'Boo');
-- uncorrelated sub-select:
WITH aaa AS (SELECT 1 AS a, 'Foo' AS b) INSERT INTO upsert_test
VALUES (1, 'Bar') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT b, a FROM aaa) RETURNING *;
a | b
---+-----
1 | Foo
(1 row)
-- correlated sub-select:
INSERT INTO upsert_test VALUES (1, 'Baz') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT b || ', Correlated', a from upsert_test i WHERE i.a = upsert_test.a)
RETURNING *;
a | b
---+-----------------
1 | Foo, Correlated
(1 row)
-- correlated sub-select (EXCLUDED.* alias):
INSERT INTO upsert_test VALUES (1, 'Bat') ON CONFLICT(a)
DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a)
RETURNING *;
a | b
---+---------------------------
1 | Foo, Correlated, Excluded
(1 row)
DROP TABLE update_test;
DROP TABLE upsert_test;
|
