aboutsummaryrefslogtreecommitdiff
path: root/src/test/regress/expected/update.out
blob: 33d52a55a0921e18091e3a2ee85848c9d1006fec (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
--
-- UPDATE syntax tests
--
CREATE TABLE update_test (
    a   INT DEFAULT 10,
    b   INT,
    c   TEXT
);
INSERT INTO update_test VALUES (5, 10, 'foo');
INSERT INTO update_test(b, a) VALUES (15, 10);
SELECT * FROM update_test;
 a  | b  |  c  
----+----+-----
  5 | 10 | foo
 10 | 15 | 
(2 rows)

UPDATE update_test SET a = DEFAULT, b = DEFAULT;
SELECT * FROM update_test;
 a  | b |  c  
----+---+-----
 10 |   | foo
 10 |   | 
(2 rows)

-- aliases for the UPDATE target table
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
SELECT * FROM update_test;
 a  | b  |  c  
----+----+-----
 10 | 10 | foo
 10 | 10 | 
(2 rows)

UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
 a  | b  |  c  
----+----+-----
 10 | 20 | foo
 10 | 20 | 
(2 rows)

--
-- Test VALUES in FROM
--
UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
  WHERE update_test.b = v.j;
SELECT * FROM update_test;
  a  | b  |  c  
-----+----+-----
 100 | 20 | foo
 100 | 20 | 
(2 rows)

--
-- Test multiple-set-clause syntax
--
UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
SELECT * FROM update_test;
  a  | b  |   c   
-----+----+-------
 100 | 20 | 
  10 | 31 | bugle
(2 rows)

UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
SELECT * FROM update_test;
  a  | b  |  c  
-----+----+-----
 100 | 20 | 
  11 | 41 | car
(2 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 ...
                                        ^
-- if an alias for the target table is specified, don't allow references
-- to the original table name
BEGIN;
SET LOCAL add_missing_from = false;
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
ERROR:  invalid reference to FROM-clause entry for table "update_test"
LINE 1: UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a...
                                        ^
HINT:  Perhaps you meant to reference the table alias "t".
ROLLBACK;
DROP TABLE update_test;