Re: [PATCH] Implement INSERT SET syntax

On Sun, Aug 18, 2019 at 11: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
>
> (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.

For what it's worth, I think this would be useful enough to justify
its existence. Back in days of yore when dragons roamed the earth and
I wrote database-driven applications instead of hacking on the
database itself, I often wondered why I had to write two
completely-different looking SQL statements, one to insert the data
which a user had entered into a webform into the database, and another
to update previously-entered data. This feature would allow those
queries to be written in the same way, which would have pleased me,
back in the day.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company