On Wed, Jul 17, 2019 at 10:00 AM Gareth Palmer <gareth@internetnz.net.nz> wrote: > > Hello, > > Attached is a patch that adds the option of using SET clause to specify > the columns and values in an INSERT statement in the same manner as that > of an UPDATE statement. > > A simple example that uses SET instead of a VALUES() clause: > > INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz'; > > Values may also be sourced from a CTE using a FROM clause: > > WITH x AS ( > SELECT 'foo' AS c1, 'bar' AS c2, 'baz' AS c3 > ) > INSERT INTO t SET c1 = x.c1, c2 = x.c2, c3 = x.c3 FROM x; > > The advantage of using the SET clause style is that the column and value > are kept together, which can make changing or removing a column or value from > a large list easier. > > Internally the grammar parser converts INSERT SET without a FROM clause into > the equivalent INSERT with a VALUES clause. When using a FROM clause it becomes > the equivalent of INSERT with a SELECT statement. > > There was a brief discussion regarding INSERT SET on pgsql-hackers in late > August 2009 [1]. > > INSERT SET is not part of any SQL standard (that I am aware of), however this > syntax is also implemented by MySQL [2]. Their implementation does not support > specifying a FROM clause. >
I think this can be a handy feature in some cases as pointed by you, but do we really want it for PostgreSQL? In the last round of discussions as pointed by you, there doesn't seem to be a consensus that we want this feature. I guess before spending too much time into reviewing this feature, we should first build a consensus on whether we need this.
I agree with you Amit, that we need a consensus on that. Do we really need that
feature or not. In the previous discussion, there was no resistance to have that
in PostgreSQL, but some problem with the patch. Current patch is very simple
and not invasive, but still, we need a consensus on that.
Along with users, I request some senior hackers/committers to also weigh in about the desirability of this feature.
