Thread

1. Follow-up on INSERT INTO ... SET ...

   Sven Berkvens-Matthijsse <sven@postgresql.berkvens.net> — 2019-01-29T04:28:07Z

   Hi all,
   
   In 2016, a thread was started about implementing INSERT INTO ... SET ... 
   that included a patch and was basically ready for inclusion in 
   PostgreSQL. However, it seems as though it stagnated for some reason. 
   Does anybody remember this and is there perhaps someone who knows what 
   the current status is? If nobody is working on this any longer, I'd be 
   willing to try to revive the patch for the current code base.
   
   The thread that I'm talking about can be found at: 
   https://www.postgresql.org/message-id/flat/709e06c0-59c9-ccec-d216-21e38cb5ed61@joh.to
   
   -- 
   With kind regards,
   Sven Berkvens-Matthijsse
   
   
   
   

2. Re: Follow-up on INSERT INTO ... SET ...

   Tom Lane <tgl@sss.pgh.pa.us> — 2019-01-29T06:20:24Z

   Sven Berkvens-Matthijsse <sven@postgresql.berkvens.net> writes:
   > In 2016, a thread was started about implementing INSERT INTO ... SET ... 
   > that included a patch and was basically ready for inclusion in 
   > PostgreSQL. However, it seems as though it stagnated for some reason. 
   > Does anybody remember this and is there perhaps someone who knows what 
   > the current status is? If nobody is working on this any longer, I'd be 
   > willing to try to revive the patch for the current code base.
   > The thread that I'm talking about can be found at: 
   > https://www.postgresql.org/message-id/flat/709e06c0-59c9-ccec-d216-21e38cb5ed61@joh.to
   
   Looking at the thread, it seems like Marko lost interest for some
   reason, and never submitted a revised patch.
   
   I'm not really sure whether we'd want to support a nonstandard
   syntax for this.  I can see that it'd have some usefulness for wide
   tables, but is that enough of an argument to risk incompatibility
   with future SQL-spec extensions?
   
   Looking at the patch itself, I'd raise two major complaints:
   
   * It looks like the only supported syntax is "INSERT ... SET
   set_clause_list", which I guess is meant to be functionally
   the same as INSERT ... VALUES with a single values list.
   That's not terribly compelling.  I'd expect to be able to
   use this syntax for multiple inserted rows.  Maybe allow
   something like INSERT ... SET ... FROM ..., where the set-list
   entries can use variables emitted by the FROM clause?
   
   * If I'm reading it right, it blows off multiple-assignment
   syntax -- that is, "SET (a,b,c) = row-valued-expr" -- with
   the comment
   
   + * This is different from set_clause_list used in UPDATE because the SelectStmt
   + * syntax already does everything you might want to do in an in INSERT.
   
   I'm unimpressed with that reasoning, because the SQL-standard
   syntax already does everything you might want to do with this.
   
   Since this patch was originally submitted, we sweated pretty
   hard to upgrade our support of UPDATE's multiple-assignment
   syntax so that it handles all interesting cases; so I'd want
   INSERT ... SET to be fully on par with UPDATE ... SET if we
   do it at all.
   
   			regards, tom lane
   
   
   

3. Re: Follow-up on INSERT INTO ... SET ...

   Sven Berkvens-Matthijsse <sven@postgresql.berkvens.net> — 2019-01-29T16:25:53Z

   Hi Tom,
   
   On 29/01/2019 07.20, Tom Lane wrote:
   > Sven Berkvens-Matthijsse <sven@postgresql.berkvens.net> writes:
   >> In 2016, a thread was started about implementing INSERT INTO ... SET ...
   >> that included a patch and was basically ready for inclusion in
   >> PostgreSQL. However, it seems as though it stagnated for some reason.
   >> Does anybody remember this and is there perhaps someone who knows what
   >> the current status is? If nobody is working on this any longer, I'd be
   >> willing to try to revive the patch for the current code base.
   >> The thread that I'm talking about can be found at:
   >> https://www.postgresql.org/message-id/flat/709e06c0-59c9-ccec-d216-21e38cb5ed61@joh.to
   > 
   > Looking at the thread, it seems like Marko lost interest for some
   > reason, and never submitted a revised patch.
   
   That was my conclusion too, but I didn't know whether there had been 
   some off-list discussion that eventually led to the abandonment of the 
   patch and proposal.
   
   > I'm not really sure whether we'd want to support a nonstandard
   > syntax for this.  I can see that it'd have some usefulness for wide
   > tables, but is that enough of an argument to risk incompatibility
   > with future SQL-spec extensions?
   
   I've seen mulitple concerns for this in some messages that I found while 
   Googling. But this is something that always plays a role when one 
   decides to deivate from the SQL standard, isn't it?
   
   PostgreSQL would not be the first database system to support the INSERT 
   INTO ... SET ... syntax, MySQL has had it for a very long time (not that 
   I've ever used MySQL, but I gathered this from what I've Googled). I 
   have no idea whether the SQL standard folks take that sort of thing into 
   account when proposing new features for the SQL standard. But if they 
   do, there is less risk of running into problems here because the syntax 
   has already been available in the field for a very long time.
   
   > Looking at the patch itself, I'd raise two major complaints:
   > 
   > * It looks like the only supported syntax is "INSERT ... SET
   > set_clause_list", which I guess is meant to be functionally
   > the same as INSERT ... VALUES with a single values list.
   > That's not terribly compelling.  I'd expect to be able to
   > use this syntax for multiple inserted rows.  Maybe allow
   > something like INSERT ... SET ... FROM ..., where the set-list
   > entries can use variables emitted by the FROM clause?
   
   Yes, I've thought about this myself. What I ended up thinking about was 
   allowing both the syntax
   
   INSERT INTO whatever SET a = 1, b = 2, c = 3;
   
   and
   
   INSERT INTO whatever SET (a = 1, b = 2, c = 3), (a = 2, b = 1, d = 5);
   
   Then I decided that that isn't all that great. And I dropped the thought.
   
   Thinking more about your proposal for INSERT INTO ... SET ... FROM ... 
   something like the following comes to mind. It looks like a nice idea, 
   but specifically for wide tables, for which this proposal would make the 
   most sense, you end up writing things like:
   
   INSERT INTO whatever SET a = a, b = b, c = c, d = d, e = e
           FROM (SELECT 1 AS a, 2 AS b, 3 AS c, 4 AS d, 5 AS e UNION ALL
                 SELECT 2 AS a, 1 AS b, 6 AS c, 8 AS d, 0 AS e);
   
   Which does not look very nice in my opinion. The SELECT ... UNION ALL 
   SELECT is not really the problem here because the rows could've come 
   from some other table or a function call for example. The mostly silly 
   SET list is what bugs me personally here.
   
   I would already be very happy if the INSERT INTO syntax would support 
   something like this:
   
   INSERT INTO whatever NATURAL SELECT 1 AS c, 2 AS a, 3 AS b, 4 AS d;
   
   Where the NATURAL (or some other keyword) would mean: look at the 
   returned columns from the query (or VALUES) and map the values in the 
   resulting rows to the correct columns in the target table, so that it 
   doesn't matter in which order you select them. Produced columns that 
   don't exist in the target table would produce an error. Missing columns 
   would use defaults in the target table as usual.
   
   Anybody with any thoughts, ideas and/or concerns about this last 
   proposal with the NATURAL keyword?
   
   The only thing it would not support is explicit DEFAULT values, which 
   VALUES does allow in an INSERT INTO statement. Not much of a concern 
   though, INSERT INTO ... SELECT ... doesn't allow it either.
   
   > * If I'm reading it right, it blows off multiple-assignment
   > syntax -- that is, "SET (a,b,c) = row-valued-expr" -- with
   > the comment
   > 
   > + * This is different from set_clause_list used in UPDATE because the SelectStmt
   > + * syntax already does everything you might want to do in an in INSERT.
   > 
   > I'm unimpressed with that reasoning, because the SQL-standard
   > syntax already does everything you might want to do with this.
   
   Yes, I agree, why specifically disallow some behavior because something 
   else already supplies that behavior when you're proposing something that 
   doesn't really supply any new functionality itself.
   
   > Since this patch was originally submitted, we sweated pretty
   > hard to upgrade our support of UPDATE's multiple-assignment
   > syntax so that it handles all interesting cases; so I'd want
   > INSERT ... SET to be fully on par with UPDATE ... SET if we
   > do it at all.
   
   Agreed, it'd have to work in the same way.
   
   > 			regards, tom lane
   
   With kinds regards,
   Sven Berkvens-Matthijsse
   
   
   

4. Re: Follow-up on INSERT INTO ... SET ...

   Marko Tiikkaja <marko@joh.to> — 2019-01-29T16:49:29Z

   On Tue, Jan 29, 2019 at 6:26 PM Sven Berkvens-Matthijsse <
   sven@postgresql.berkvens.net> wrote:
   
   > On 29/01/2019 07.20, Tom Lane wrote:
   > > Looking at the thread, it seems like Marko lost interest for some
   > > reason, and never submitted a revised patch.
   >
   > That was my conclusion too, but I didn't know whether there had been
   > some off-list discussion that eventually led to the abandonment of the
   > patch and proposal.
   >
   
   There has not.  I just don't have the energy or interest to pursue this at
   the moment.
   
   
   .m