Thread

1. INSERT and parentheses

   Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-04-26T12:57:23Z

   Hi,
   
   This came up on IRC today and I recall several instances of this during
   the last two months or so, so I decided to send a patch.  The problem in
   question occurs when you have extra parentheses in an INSERT list:
   
   INSERT INTO foo(a,b,c) SELECT (a,b,c) FROM ..; or
   INSERT INTO foo(a,b,c) VALUES((0,1,2));
   
   Both of these give you the same error:
   ERROR:  INSERT has more target columns than expressions
   
   The first version is a lot more common and as it turns out, is sometimes
   very hard to spot.  This patch attaches a HINT message to these two
   cases.  The message itself could probably be a lot better, but I can't
   think of anything.
   
   Thoughts?
   
   
   Regards,
   Marko Tiikkaja
   

2. Re: INSERT and parentheses

   Robert Haas <robertmhaas@gmail.com> — 2010-04-26T13:08:04Z

   On Mon, Apr 26, 2010 at 8:57 AM, Marko Tiikkaja
   <marko.tiikkaja@cs.helsinki.fi> wrote:
   > Hi,
   >
   > This came up on IRC today and I recall several instances of this during
   > the last two months or so, so I decided to send a patch.  The problem in
   > question occurs when you have extra parentheses in an INSERT list:
   >
   > INSERT INTO foo(a,b,c) SELECT (a,b,c) FROM ..; or
   > INSERT INTO foo(a,b,c) VALUES((0,1,2));
   >
   > Both of these give you the same error:
   > ERROR:  INSERT has more target columns than expressions
   >
   > The first version is a lot more common and as it turns out, is sometimes
   > very hard to spot.  This patch attaches a HINT message to these two
   > cases.  The message itself could probably be a lot better, but I can't
   > think of anything.
   >
   > Thoughts?
   
   I suggest adding it to the next CommitFest.  Since I've never been
   bitten by this, I can't get excited about the change, but I'm also not
   arrogant enough to believe that everyone else's experiences are the
   same as my own.
   
   ...Robert
   
   

3. Re: INSERT and parentheses

   Stephen Frost <sfrost@snowman.net> — 2010-04-26T13:13:54Z

   * Robert Haas (robertmhaas@gmail.com) wrote:
   > > The first version is a lot more common and as it turns out, is sometimes
   > > very hard to spot.  This patch attaches a HINT message to these two
   > > cases.  The message itself could probably be a lot better, but I can't
   > > think of anything.
   > >
   > > Thoughts?
   > 
   > I suggest adding it to the next CommitFest.  Since I've never been
   > bitten by this, I can't get excited about the change, but I'm also not
   > arrogant enough to believe that everyone else's experiences are the
   > same as my own.
   
   Not to be a pain, but the hint really is kind of terrible..  It'd
   probably be better if you included somewhere that the insert appears to
   be a single column with a record-type rather than multiple columns of
   non-composite type..
   
   	Thanks,
   
   		Stephen
   

4. Re: INSERT and parentheses

   Tom Lane <tgl@sss.pgh.pa.us> — 2010-04-26T14:20:06Z

   Stephen Frost <sfrost@snowman.net> writes:
   > Not to be a pain, but the hint really is kind of terrible..  It'd
   > probably be better if you included somewhere that the insert appears to
   > be a single column with a record-type rather than multiple columns of
   > non-composite type..
   
   I don't much care for the test, either.  AFAICS, a hint like this would
   only be appropriate for a RowExpr item, *not* a Var.  It might also be
   worth checking the number of items in the RowExpr before deciding that
   the hint is appropriate.
   
   			regards, tom lane
   
   

5. Re: INSERT and parentheses

   Bruce Momjian <bruce@momjian.us> — 2010-05-31T19:52:56Z

   I have added this to the next commit-fest:
   
   	https://commitfest.postgresql.org/action/commitfest_view?id=6
   
   
   ---------------------------------------------------------------------------
   
   Marko Tiikkaja wrote:
   > Hi,
   > 
   > This came up on IRC today and I recall several instances of this during
   > the last two months or so, so I decided to send a patch.  The problem in
   > question occurs when you have extra parentheses in an INSERT list:
   > 
   > INSERT INTO foo(a,b,c) SELECT (a,b,c) FROM ..; or
   > INSERT INTO foo(a,b,c) VALUES((0,1,2));
   > 
   > Both of these give you the same error:
   > ERROR:  INSERT has more target columns than expressions
   > 
   > The first version is a lot more common and as it turns out, is sometimes
   > very hard to spot.  This patch attaches a HINT message to these two
   > cases.  The message itself could probably be a lot better, but I can't
   > think of anything.
   > 
   > Thoughts?
   > 
   > 
   > Regards,
   > Marko Tiikkaja
   
   [ Attachment, skipping... ]
   
   > 
   > -- 
   > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-hackers
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + None of us is going to be here forever. +