Re: [PATCH] Implement INSERT SET syntax - Mailing list pgsql-hackers

From Ibrar Ahmed
Subject Re: [PATCH] Implement INSERT SET syntax
Date
Msg-id CALtqXTc8vxdFrZL9DYOkovdKEw6_jwub3WBsGyOLJx562kLRSQ@mail.gmail.com
Whole thread Raw
In response to Re: [PATCH] Implement INSERT SET syntax  (Amit Kapila <amit.kapila16@gmail.com>)
List pgsql-hackers


On Fri, Aug 16, 2019 at 8:19 AM Amit Kapila <amit.kapila16@gmail.com> wrote:
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.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com




--
Ibrar Ahmed

pgsql-hackers by date:

Previous
From: Ibrar Ahmed
Date:
Subject: Re: [Proposal] Table-level Transparent Data Encryption (TDE) and KeyManagement Service (KMS)
Next
From: Peter Eisentraut
Date:
Subject: Re: Unexpected "shared memory block is still in use"