Re: [PATCH] Implement INSERT SET syntax

On Thu, Nov 14, 2019 at 9:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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 ;

Best regards,
Pantelis Theodosiou