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
|
--
-- 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)
--
-- 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)
-- 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)
-- 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;
|
