Hi, I found "Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid." in the documentation.
Some people usually like to update table by alias. They want to add this feature. So I get the source in the gram.y, and I found that they just take the alias as the name of column. So I think we could analyze whether the "indirection" is alias or other. The attachment is my patch to support - update table by alias: CREATE TABLE update_test1 ( a INT, b INT, c TEXT ); INSERT INTO update_test1 VALUES (1, 10, 'aa'),(2, 11, 'bb'),(3, 12, 'cc'),(4, 13, 'dd'); SELECT * FROM update_test1; a | b | c ---+----+---- 1 | 10 | aa 2 | 11 | bb 3 | 12 | cc 4 | 13 | dd (4 rows) UPDATE update_test1 SET tb1.a = 4 WHERE a = 1; ERROR: column "a" of relation "tb1" does not exist LINE 1: UPDATE update_test1 SET tb1.a = 4 WHERE a = 1; ^ UPDATE update_test1 tb1 SET tb1.f = 4 WHERE tb1.a = 1; ERROR: column "f" of relation "tb1" does not exist LINE 1: UPDATE update_test1 tb1 SET tb1.f = 4 WHERE tb1.a = 1; ^ UPDATE update_test1 tb SET tb1.a = 4 WHERE tb.a = 1; ERROR: Perhaps you meant to reference the table alias "tb". UPDATE update_test1 tb SET tb1.f = 4 WHERE tb.a = 1; ERROR: Perhaps you meant to reference the table alias "tb". UPDATE update_test1 tb1 SET tb1.a = 5 WHERE tb1.a = 1; UPDATE update_test1 tb1 SET update_test1.a = 6 WHERE tb1.a = 2; UPDATE update_test1 SET update_test1.a = 7 WHERE a = 3; UPDATE update_test1 tb1 SET a = 8 WHERE a = 4; SELECT * FROM update_test1; a | b | c ---+----+---- 5 | 10 | aa 6 | 11 | bb 7 | 12 | cc 8 | 13 | dd (4 rows) 2016-04-07 WANGSHUO A PGer
updateforcolalias.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers