Re: Adding support for a fully qualified column-name in UPDATE ... SET

Jim Finnerty <jfinnert(at)amazon(dot)com> writes:
> The accepted syntax for UPDATE ... SET does not currently permit the column
> name to be qualified by schema.table or table or correlation-name, as is
> permitted in other systems. This is apparently due to the syntax that
> PostgreSQL accepts for composite columns, which would create an ambiguity in
> the grammar if both SET t.c [ opt_indirection ] = value, or SET c.f [
> opt_indirection ] = value, were both allowed.

> As a result, databases migrated from several other commercial database
> servers to PostgreSQL must be "cleaned up" to reconcile these differences.
> This can be time consuming and unnecessary.

> This can be disambiguated during semantic analysis in all but the most
> contrived cases.

I don't think it'd really be a good idea to allow "SET x.y = ..." to mean
two (or more?) completely different things depending on context. That's
just a recipe for shooting yourself in the foot. Your claim that
ambiguity would arise only in contrived cases seems way over-optimistic.

The case for doing something would be stronger if the SQL spec allowed
qualified column names here. But AFAICS it does not, for pretty much
the same reason we don't: it thinks "x.y" is an assignment to subcolumn
y of composite column x --- or at least I think that's what the
impenetrable verbiage around "mutated set clause" means.

regards, tom lane