Gareth Palmer <gareth@internetnz.net.nz> writes: >> On 19/08/2019, at 3:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> 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).
> Thanks for the feedback. Attached is version 3 of the patch that makes > the syntax work more like an UPDATE statement when a FROM clause is used.
Since nobody has objected to this, I'm supposing that there's general consensus that that design sketch is OK, and we can move on to critiquing implementation details. I took a look, and didn't like much of what I saw.
...
I'm setting this back to Waiting on Author.
regards, tom lane
Regarding syntax and considering that it makes INSERT look like UPDATE: there is another difference between INSERT and UPDATE. INSERT allows SELECT with ORDER BY and OFFSET/LIMIT (or FETCH FIRST), e.g.:
INSERT INTO t (a,b)
SELECT a+10. b+10
FROM t
ORDER BY a
LIMIT 3;
But UPDATE doesn't. I suppose the proposed behaviour of INSERT .. SET will be the same as standard INSERT. So we'll need a note for the differences between INSERT/SET and UPDATE/SET syntax.
On a related not, column aliases can be used in ORDER BY, e.g:
insert into t (a, b) select
a + 20,
b - 2 * a as f from t order by f desc limit 3 ;
Would that be expressed as follows?:
insert into t set
a = a + 20,
b = b - 2 * a as f from t order by f desc limit 3 ;