Hi,
A common mistake is to try and qualify column references on the LHS of
SET in UPDATE. The error message can be a bit cryptic at times, too.
I've attached a patch to try and improve the situation a bit:
=# create table foo(bar int);
CREATE TABLE
=# update foo set foo.bar = 1;
ERROR: column "foo" of relation "foo" does not exist
LINE 1: update foo set foo.bar = 1;
^
HINT: Target column references in UPDATE must not be qualified
=# update foo set notfoo.bar = 1; -- no hint
ERROR: column "notfoo" of relation "foo" does not exist
LINE 1: update foo set notfoo.bar = 1;
^
=# update foo set foo.bar[1] = 1;
ERROR: column "foo" of relation "foo" does not exist
LINE 1: update foo set foo.bar[1] = 1;
^
HINT: Target column references in UPDATE must not be qualified
I'll add this to the next CF, but feel free to bash it before that.
.marko
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 1989,2000 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
attrno = attnameAttNum(pstate->p_target_relation,
origTarget->name,
true);
if (attrno == InvalidAttrNumber)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" of relation
\"%s\" does not exist",
origTarget->name,
!
RelationGetRelationName(pstate->p_target_relation)),
parser_errposition(pstate,
origTarget->location)));
updateTargetListEntry(pstate, tle, origTarget->name,
attrno,
--- 1989,2019 ----
attrno = attnameAttNum(pstate->p_target_relation,
origTarget->name,
true);
if (attrno == InvalidAttrNumber)
+ {
+ const char *relname;
+ const char *message_hint = NULL;
+
+ /*
+ * Table-qualifying the LHS expression in SET is a
common mistake;
+ * provide a hint if that seems to be the problem.
+ */
+ relname =
RelationGetRelationName(pstate->p_target_relation);
+ if (strcmp(origTarget->name, relname) == 0 &&
+ origTarget->indirection != NIL)
+ {
+ Node *ind = linitial(origTarget->indirection);
+ if (IsA(ind, String))
+ message_hint = "Target column
references in UPDATE must not be qualified";
+ }
+
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" of relation
\"%s\" does not exist",
origTarget->name,
! relname),
! message_hint ? errhint("%s",
message_hint) : 0,
parser_errposition(pstate,
origTarget->location)));
+ }
updateTargetListEntry(pstate, tle, origTarget->name,
attrno,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
