Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes: > What I don't like about the syntax is that it kind of breaks the > notional processing model of INSERT in a fundamental way.
Agreed. I really don't like that this only works for a VALUES-like case (and only the one-row form at that). It's hard to see it as anything but a wart pasted onto the syntax. > Let's think about how we can achieve this using existing concepts in > SQL. What we really need here at a fundamental level is an option to > match $target to $table_source by column *name* rather than column > *position*. There is existing syntax in SQL for that, namely > a UNION b > vs > a UNION CORRESPONDING b A potential issue here --- and something that applies to Vik's question as well, now that I think about it --- is that CORRESPONDING breaks down in the face of ALTER TABLE RENAME COLUMN. Something that had been a legal query before the rename might be invalid, or mean something quite different, afterwards. This is really nasty for stored views/rules, because we have neither a mechanism for forbidding input-table renames nor a mechanism for revalidating views/rules afterwards. Maybe we could make it go by resolving CORRESPONDING in the rewriter or planner, rather than in parse analysis; but that seems quite unpleasant as well. Changing our conclusions about the data types coming out of a UNION really shouldn't happen later than parse analysis. The SET-style syntax doesn't have that problem, since it's explicit about which values go into which columns. Perhaps the way to resolve Peter's objection is to make the syntax more fully like UPDATE: INSERT INTO target SET c1 = x, c2 = y+z, ... FROM tables-providing-x-y-z (with the patch as-submitted corresponding to the case with an empty FROM clause, hence no variables in the expressions-to-be-assigned). Of course, this is not functionally distinct from INSERT INTO target(c1,c2,...) SELECT x, y+z, ... FROM tables-providing-x-y-z and it's fair to question whether it's worth supporting a nonstandard syntax just to allow the target column names to be written closer to the expressions-to-be-assigned. regards, tom lane