Thread: 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
Attachment
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. +