diff options
| -rw-r--r-- | src/test/regress/expected/update_1.out | 110 |
1 files changed, 101 insertions, 9 deletions
diff --git a/src/test/regress/expected/update_1.out b/src/test/regress/expected/update_1.out index d4db0c6fea..0b0ac862b6 100644 --- a/src/test/regress/expected/update_1.out +++ b/src/test/regress/expected/update_1.out @@ -6,6 +6,10 @@ CREATE TABLE update_test ( 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 ORDER BY a, b, c; @@ -59,6 +63,16 @@ SELECT * FROM update_test ORDER BY a, b, c; -- -- Test multiple-set-clause syntax -- +INSERT INTO update_test SELECT a,b+1,c FROM update_test; +SELECT * FROM update_test; + a | b | c +----+----+----- + 5 | 10 | foo + 5 | 11 | foo + 10 | 20 | + 10 | 21 | +(4 rows) + UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo'; ERROR: could not plan this distributed update DETAIL: correlated UPDATE or updating distribution column currently not supported in Postgres-XL. @@ -66,8 +80,10 @@ SELECT * FROM update_test ORDER BY a, b, c; a | b | c ----+----+----- 5 | 10 | foo + 5 | 11 | foo 10 | 20 | -(2 rows) + 10 | 21 | +(4 rows) UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10; ERROR: could not plan this distributed update @@ -76,18 +92,62 @@ SELECT * FROM update_test ORDER BY a, b, c; a | b | c ----+----+----- 5 | 10 | foo + 5 | 11 | foo 10 | 20 | -(2 rows) + 10 | 21 | +(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" --- XXX this should work, but doesn't yet: -UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = 'foo') - WHERE a = 10; -ERROR: syntax error at or near "select" -LINE 1: UPDATE update_test SET (a,b) = (select a,b FROM update_test ... - ^ +-- 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; +ERROR: could not plan this distributed update +DETAIL: correlated UPDATE or updating distribution column currently not supported in Postgres-XL. +SELECT * FROM update_test; + a | b | c +----+----+----- + 5 | 10 | foo + 5 | 11 | foo + 10 | 20 | + 10 | 21 | +(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); +ERROR: could not plan this distributed update +DETAIL: correlated UPDATE or updating distribution column currently not supported in Postgres-XL. +SELECT * FROM update_test; + a | b | c +----+----+----- + 5 | 10 | foo + 5 | 11 | foo + 10 | 20 | + 10 | 21 | +(4 rows) + +-- fail, multiple rows supplied: +UPDATE update_test SET (b,a) = (select a+1,b from update_test); +ERROR: could not plan this distributed update +DETAIL: correlated UPDATE or updating distribution column currently not supported in Postgres-XL. +-- 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; +ERROR: could not plan this distributed update +DETAIL: correlated UPDATE or updating distribution column currently not supported in Postgres-XL. +SELECT * FROM update_test; + a | b | c +----+----+----- + 5 | 10 | foo + 5 | 11 | foo + 10 | 20 | + 10 | 21 | +(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; @@ -101,7 +161,39 @@ SELECT a, b, char_length(c) FROM update_test ORDER BY a; a | b | char_length ----+----+------------- 5 | 10 | 3 + 5 | 11 | 3 10 | 20 | -(2 rows) + 10 | 21 | +(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; |