Обсуждение: INSERT and parentheses

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

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

* 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

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

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. +