1-- 2-- UPDATE syntax tests 3-- 4CREATE TABLE update_test ( 5 a INT DEFAULT 10, 6 b INT, 7 c TEXT 8); 9CREATE TABLE upsert_test ( 10 a INT PRIMARY KEY, 11 b TEXT 12); 13INSERT INTO update_test VALUES (5, 10, 'foo'); 14INSERT INTO update_test(b, a) VALUES (15, 10); 15SELECT * FROM update_test; 16 a | b | c 17----+----+----- 18 5 | 10 | foo 19 10 | 15 | 20(2 rows) 21 22UPDATE update_test SET a = DEFAULT, b = DEFAULT; 23SELECT * FROM update_test; 24 a | b | c 25----+---+----- 26 10 | | foo 27 10 | | 28(2 rows) 29 30-- aliases for the UPDATE target table 31UPDATE update_test AS t SET b = 10 WHERE t.a = 10; 32SELECT * FROM update_test; 33 a | b | c 34----+----+----- 35 10 | 10 | foo 36 10 | 10 | 37(2 rows) 38 39UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10; 40SELECT * FROM update_test; 41 a | b | c 42----+----+----- 43 10 | 20 | foo 44 10 | 20 | 45(2 rows) 46 47-- 48-- Test VALUES in FROM 49-- 50UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j) 51 WHERE update_test.b = v.j; 52SELECT * FROM update_test; 53 a | b | c 54-----+----+----- 55 100 | 20 | foo 56 100 | 20 | 57(2 rows) 58 59-- fail, wrong data type: 60UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i, j) 61 WHERE update_test.b = v.j; 62ERROR: column "a" is of type integer but expression is of type record 63LINE 1: UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i... 64 ^ 65HINT: You will need to rewrite or cast the expression. 66-- 67-- Test multiple-set-clause syntax 68-- 69INSERT INTO update_test SELECT a,b+1,c FROM update_test; 70SELECT * FROM update_test; 71 a | b | c 72-----+----+----- 73 100 | 20 | foo 74 100 | 20 | 75 100 | 21 | foo 76 100 | 21 | 77(4 rows) 78 79UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo'; 80SELECT * FROM update_test; 81 a | b | c 82-----+----+------- 83 100 | 20 | 84 100 | 21 | 85 10 | 31 | bugle 86 10 | 32 | bugle 87(4 rows) 88 89UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10; 90SELECT * FROM update_test; 91 a | b | c 92-----+----+----- 93 100 | 20 | 94 100 | 21 | 95 11 | 41 | car 96 11 | 42 | car 97(4 rows) 98 99-- fail, multi assignment to same column: 100UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10; 101ERROR: multiple assignments to same column "b" 102-- uncorrelated sub-select: 103UPDATE update_test 104 SET (b,a) = (select a,b from update_test where b = 41 and c = 'car') 105 WHERE a = 100 AND b = 20; 106SELECT * FROM update_test; 107 a | b | c 108-----+----+----- 109 100 | 21 | 110 11 | 41 | car 111 11 | 42 | car 112 41 | 11 | 113(4 rows) 114 115-- correlated sub-select: 116UPDATE update_test o 117 SET (b,a) = (select a+1,b from update_test i 118 where i.a=o.a and i.b=o.b and i.c is not distinct from o.c); 119SELECT * FROM update_test; 120 a | b | c 121----+-----+----- 122 21 | 101 | 123 41 | 12 | car 124 42 | 12 | car 125 11 | 42 | 126(4 rows) 127 128-- fail, multiple rows supplied: 129UPDATE update_test SET (b,a) = (select a+1,b from update_test); 130ERROR: more than one row returned by a subquery used as an expression 131-- set to null if no rows supplied: 132UPDATE update_test SET (b,a) = (select a+1,b from update_test where a = 1000) 133 WHERE a = 11; 134SELECT * FROM update_test; 135 a | b | c 136----+-----+----- 137 21 | 101 | 138 41 | 12 | car 139 42 | 12 | car 140 | | 141(4 rows) 142 143-- *-expansion should work in this context: 144UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 100)) AS v(i, j) 145 WHERE update_test.a = v.i; 146-- you might expect this to work, but syntactically it's not a RowExpr: 147UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) AS v(i, j) 148 WHERE update_test.a = v.i; 149ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression 150LINE 1: UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) ... 151 ^ 152-- if an alias for the target table is specified, don't allow references 153-- to the original table name 154UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10; 155ERROR: invalid reference to FROM-clause entry for table "update_test" 156LINE 1: UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a... 157 ^ 158HINT: Perhaps you meant to reference the table alias "t". 159-- Make sure that we can update to a TOASTed value. 160UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car'; 161SELECT a, b, char_length(c) FROM update_test; 162 a | b | char_length 163----+-----+------------- 164 | | 165 21 | 100 | 166 41 | 12 | 10000 167 42 | 12 | 10000 168(4 rows) 169 170-- Check multi-assignment with a Result node to handle a one-time filter. 171EXPLAIN (VERBOSE, COSTS OFF) 172UPDATE update_test t 173 SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a) 174 WHERE CURRENT_USER = SESSION_USER; 175 QUERY PLAN 176------------------------------------------------------------------------ 177 Update on public.update_test t 178 -> Result 179 Output: ($1), ($2), t.c, ((SubPlan 1 (returns $1,$2))), t.ctid 180 One-Time Filter: (CURRENT_USER = SESSION_USER) 181 -> Seq Scan on public.update_test t 182 Output: $1, $2, t.c, (SubPlan 1 (returns $1,$2)), t.ctid 183 SubPlan 1 (returns $1,$2) 184 -> Seq Scan on public.update_test s 185 Output: s.b, s.a 186 Filter: (s.a = t.a) 187(10 rows) 188 189UPDATE update_test t 190 SET (a, b) = (SELECT b, a FROM update_test s WHERE s.a = t.a) 191 WHERE CURRENT_USER = SESSION_USER; 192SELECT a, b, char_length(c) FROM update_test; 193 a | b | char_length 194-----+----+------------- 195 | | 196 100 | 21 | 197 12 | 41 | 10000 198 12 | 42 | 10000 199(4 rows) 200 201-- Test ON CONFLICT DO UPDATE 202INSERT INTO upsert_test VALUES(1, 'Boo'), (3, 'Zoo'); 203-- uncorrelated sub-select: 204WITH aaa AS (SELECT 1 AS a, 'Foo' AS b) INSERT INTO upsert_test 205 VALUES (1, 'Bar') ON CONFLICT(a) 206 DO UPDATE SET (b, a) = (SELECT b, a FROM aaa) RETURNING *; 207 a | b 208---+----- 209 1 | Foo 210(1 row) 211 212-- correlated sub-select: 213INSERT INTO upsert_test VALUES (1, 'Baz'), (3, 'Zaz') ON CONFLICT(a) 214 DO UPDATE SET (b, a) = (SELECT b || ', Correlated', a from upsert_test i WHERE i.a = upsert_test.a) 215 RETURNING *; 216 a | b 217---+----------------- 218 1 | Foo, Correlated 219 3 | Zoo, Correlated 220(2 rows) 221 222-- correlated sub-select (EXCLUDED.* alias): 223INSERT INTO upsert_test VALUES (1, 'Bat'), (3, 'Zot') ON CONFLICT(a) 224 DO UPDATE SET (b, a) = (SELECT b || ', Excluded', a from upsert_test i WHERE i.a = excluded.a) 225 RETURNING *; 226 a | b 227---+--------------------------- 228 1 | Foo, Correlated, Excluded 229 3 | Zoo, Correlated, Excluded 230(2 rows) 231 232DROP TABLE update_test; 233DROP TABLE upsert_test; 234-- update to a partition should check partition bound constraint for the new tuple 235create table range_parted ( 236 a text, 237 b int 238) partition by range (a, b); 239create table part_a_1_a_10 partition of range_parted for values from ('a', 1) to ('a', 10); 240create table part_a_10_a_20 partition of range_parted for values from ('a', 10) to ('a', 20); 241create table part_b_1_b_10 partition of range_parted for values from ('b', 1) to ('b', 10); 242create table part_b_10_b_20 partition of range_parted for values from ('b', 10) to ('b', 20); 243insert into part_a_1_a_10 values ('a', 1); 244insert into part_b_10_b_20 values ('b', 10); 245-- fail 246update part_a_1_a_10 set a = 'b' where a = 'a'; 247ERROR: new row for relation "part_a_1_a_10" violates partition constraint 248DETAIL: Failing row contains (b, 1). 249update range_parted set b = b - 1 where b = 10; 250ERROR: new row for relation "part_b_10_b_20" violates partition constraint 251DETAIL: Failing row contains (b, 9). 252-- ok 253update range_parted set b = b + 1 where b = 10; 254-- cleanup 255drop table range_parted; 256