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