Re: HINTing on UPDATE foo SET foo.bar = ..;

On 2014-11-22 18:02, Tom Lane wrote:
> Marko Tiikkaja <marko(at)joh(dot)to> writes:
>> 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.
>
> Perhaps, but this hint is not much of an improvement:
>
>> HINT: Target column references in UPDATE must not be qualified
>
> because target column references *can* be "qualified", for example
> if you have a composite column datatype you can assign directly to
> one of its fields. (This ambiguity is exactly why we don't simply
> allow the case.) So I don't think that "qualified" is a sufficiently
> precise phrase to be helpful. Possibly something along the lines of
>
> HINT: Do not qualify an UPDATE target column with the name of the table.

Sounds good to me. I didn't expect anyone to like the wording of the
hint in the first place ;-)

> Also, the coding technique used here is poor, because the hint text
> will not be exposed for translation. The usual pattern is
>
> need_hint ? errhint("Message text here.") : 0

Oops. I just copied what the first relevant grep of errhint did, which
was from postgres_fdw. But its hints will already have been translated.
Will fix.

> Also, as far as Peter's point goes, it would likely make sense to
> issue this hint if the column basename is *either* the alias name
> or the underlying table name.

Yeah, I thought about that too, but I thought that might be weird if
there's an alias in FROM with the name of the table, e.g:

UPDATE foo f1 SET foo.a = 1 FROM foo;

But I don't really care too much either way.

.marko