Re: [PATCH] Implement INSERT SET syntax

Pantelis Theodosiou <ypercube(at)gmail(dot)com> writes:
> On 19/08/2019, at 3:00 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

> 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.: ...
> 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.

I was supposing that this syntax should be just another way to spell

INSERT INTO target (columnlist) SELECT ...

So everything past FROM would work exactly like it does in SELECT.

> On a related not, column aliases can be used in ORDER BY, e.g:

As proposed, there's no option equivalent to writing output-column aliases
in the INSERT ... SELECT form, so the question doesn't come up.

regards, tom lane