Re: 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(dot)tiikkaja(at)cs(dot)helsinki(dot)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(at)gmail(dot)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(at)snowman(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +