Re: [PATCH] Implement INSERT SET syntax

From: Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com>
To: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Gareth Palmer <gareth(at)internetnz(dot)net(dot)nz>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [PATCH] Implement INSERT SET syntax
Date: 2019-08-16 13:03:47
Message-ID: CALtqXTc8vxdFrZL9DYOkovdKEw6_jwub3WBsGyOLJx562kLRSQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, Aug 16, 2019 at 8:19 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Wed, Jul 17, 2019 at 10:00 AM Gareth Palmer <gareth(at)internetnz(dot)net(dot)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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2019-08-16 13:09:57 Re: Unexpected "shared memory block is still in use"
Previous Message Ibrar Ahmed 2019-08-16 12:58:59 Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)