1--
2-- UPDATE syntax tests
3--
4
5CREATE TABLE update_test (
6    a   INT DEFAULT 10,
7    b   INT,
8    c   TEXT
9);
10
11CREATE TABLE upsert_test (
12    a   INT PRIMARY KEY,
13    b   TEXT
14);
15
16INSERT INTO update_test VALUES (5, 10, 'foo');
17INSERT INTO update_test(b, a) VALUES (15, 10);
18
19SELECT * FROM update_test;
20
21UPDATE update_test SET a = DEFAULT, b = DEFAULT;
22
23SELECT * FROM update_test;
24
25-- aliases for the UPDATE target table
26UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
27
28SELECT * FROM update_test;
29
30UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
31
32SELECT * FROM update_test;
33
34--
35-- Test VALUES in FROM
36--
37
38UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
39  WHERE update_test.b = v.j;
40
41SELECT * FROM update_test;
42
43-- fail, wrong data type:
44UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i, j)
45  WHERE update_test.b = v.j;
46
47--
48-- Test multiple-set-clause syntax
49--
50
51INSERT INTO update_test SELECT a,b+1,c FROM update_test;
52SELECT * FROM update_test;
53
54UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
55SELECT * FROM update_test;
56UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
57SELECT * FROM update_test;
58-- fail, multi assignment to same column:
59UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
60
61-- uncorrelated sub-select:
62UPDATE update_test
63  SET (b,a) = (select a,b from update_test where b = 41 and c = 'car')
64  WHERE a = 100 AND b = 20;
65SELECT * FROM update_test;
66-- correlated sub-select:
67UPDATE update_test o
68  SET (b,a) = (select a+1,b from update_test i
69               where i.a=o.a and i.b=o.b and i.c is not distinct from o.c);
70SELECT * FROM update_test;
71-- fail, multiple rows supplied:
72UPDATE update_test SET (b,a) = (select a+1,b from update_test);
73-- set to null if no rows supplied:
74UPDATE update_test SET (b,a) = (select a+1,b from update_test where a = 1000)
75  WHERE a = 11;
76SELECT * FROM update_test;
77-- these should work, but don't yet:
78UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 100)) AS v(i, j)
79  WHERE update_test.a = v.i;
80UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 101)) AS v(i, j)
81  WHERE update_test.a = v.i;
82
83-- if an alias for the target table is specified, don't allow references
84-- to the original table name
85UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
86
87-- Make sure that we can update to a TOASTed value.
88UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
89SELECT a, b, char_length(c) FROM update_test;
90
91-- Check multi-assignment with a Result node to handle a one-time filter.
92EXPLAIN (VERBOSE, COSTS OFF)
93UPDATE update_test t
94  SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
95  WHERE CURRENT_USER = SESSION_USER;
96UPDATE update_test t
97  SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a)
98  WHERE CURRENT_USER = SESSION_USER;
99SELECT a, b, char_length(c) FROM update_test;
100
101-- Test ON CONFLICT DO UPDATE
102INSERT INTO upsert_test VALUES(1, 'Boo'), (3, 'Zoo');
103-- uncorrelated  sub-select:
104WITH aaa AS (SELECT 1 AS a, 'Foo' AS b) INSERT INTO upsert_test
105  VALUES (1, 'Bar') ON CONFLICT(a)
106  DO UPDATE SET (b, a) = (SELECT b, a FROM aaa) RETURNING *;
107-- correlated sub-select:
108INSERT INTO upsert_test VALUES (1, 'Baz'), (3, 'Zaz') ON CONFLICT(a)
109  DO UPDATE SET (b, a) = (SELECT b || ', Correlated', a from upsert_test i WHERE i.a = upsert_test.a)
110  RETURNING *;
111-- correlated sub-select (EXCLUDED.* alias):
112INSERT INTO upsert_test VALUES (1, 'Bat'), (3, 'Zot') ON CONFLICT(a)
113  DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a)
114  RETURNING *;
115
116DROP TABLE update_test;
117DROP TABLE upsert_test;
118