4
CREATE TABLE update_test (
9
INSERT INTO update_test VALUES (5, 10, 'foo');
10
INSERT INTO update_test(b, a) VALUES (15, 10);
11
SELECT * FROM update_test;
18
UPDATE update_test SET a = DEFAULT, b = DEFAULT;
19
SELECT * FROM update_test;
26
-- aliases for the UPDATE target table
27
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
28
SELECT * FROM update_test;
35
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
36
SELECT * FROM update_test;
44
-- Test VALUES in FROM
46
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
47
WHERE update_test.b = v.j;
48
SELECT * FROM update_test;
56
-- Test multiple-set-clause syntax
58
UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
59
SELECT * FROM update_test;
66
UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
67
SELECT * FROM update_test;
74
-- fail, multi assignment to same column:
75
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
76
ERROR: multiple assignments to same column "b"
77
-- XXX this should work, but doesn't yet:
78
UPDATE update_test SET (a,b) = (select a,b FROM update_test where c = 'foo')
80
ERROR: syntax error at or near "select"
81
LINE 1: UPDATE update_test SET (a,b) = (select a,b FROM update_test ...
83
-- if an alias for the target table is specified, don't allow references
84
-- to the original table name
85
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
86
ERROR: invalid reference to FROM-clause entry for table "update_test"
87
LINE 1: UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a...
89
HINT: Perhaps you meant to reference the table alias "t".
90
-- Make sure that we can update to a TOASTed value.
91
UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
92
SELECT a, b, char_length(c) FROM update_test;
94
-----+----+-------------
99
DROP TABLE update_test;