Peter Eisentraut <peter.eisentr...@2ndquadrant.com> writes: > On 2020-03-24 18:57, Tom Lane wrote: >> No doubt that's all fixable, but the realization that some cases of >> this syntax are*not* just syntactic sugar for standards-compliant >> syntax is giving me pause. Do we really want to get out front of >> the SQL committee on extending INSERT in an incompatible way?
> What is the additional functionality that we are considering adding here? > The thread started out proposing a more convenient syntax, but it seems > to go deeper now and perhaps not everyone is following. AIUI, the proposal is to allow INSERT commands to be written using an UPDATE-like syntax, for example INSERT INTO table SET col1 = value1, col2 = value2, ... [ FROM ... ] where everything after FROM is the same as it is in SELECT. My initial belief was that this was strictly equivalent to what you could do with a target-column-names list in standard INSERT, viz INSERT INTO table (col1, col2, ...) VALUES (value1, value2, ...); or INSERT INTO table (col1, col2, ...) SELECT value1, value2, ... FROM ... but it's arguably more legible/convenient because the column names are written next to their values. However, that rewriting falls down for certain multiassignment cases where you have a row source that can't be decomposed, such as my example INSERT INTO table SET (col1, col2) = (SELECT value1, value2 FROM ...), ... [ FROM ... ] So, just as we found for UPDATE, multiassignment syntax is strictly stronger than plain column-by-column assignment. There are some secondary issues about which variants of this syntax will allow a column value to be written as DEFAULT, and perhaps about whether set-returning functions work. But the major point right now is about whether its's possible to rewrite to standard syntax. regards, tom lane