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
*** a/src/backend/parser/analyze.c --- b/src/backend/parser/analyze.c *************** *** 730,742 **** transformInsertRow(ParseState *pstate, List *exprlist, list_length(icolumns)))))); if (stmtcols != NIL && list_length(exprlist) < list_length(icolumns)) ! ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("INSERT has more target columns than expressions"), parser_errposition(pstate, exprLocation(list_nth(icolumns, list_length(exprlist)))))); /* * Prepare columns for assignment to target table. */ --- 730,761 ---- list_length(icolumns)))))); if (stmtcols != NIL && list_length(exprlist) < list_length(icolumns)) ! { ! /* ! * If the expression only has a single column of type record, it's ! * possible that that wasn't intended. ! */ ! if (list_length(exprlist) == 1 && ! (IsA(linitial(exprlist), Var) && ! ((Var *) linitial(exprlist))->vartype == RECORDOID) || ! IsA(linitial(exprlist), RowExpr)) ! ereport(ERROR, ! (errcode(ERRCODE_SYNTAX_ERROR), ! errmsg("INSERT has more target columns than expressions"), ! errhint("Did you accidentally use extra parentheses?"), ! parser_errposition(pstate, ! exprLocation(list_nth(icolumns, ! list_length(exprlist)))))); ! else ! ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("INSERT has more target columns than expressions"), parser_errposition(pstate, exprLocation(list_nth(icolumns, list_length(exprlist)))))); + } + /* * Prepare columns for assignment to target table. */
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers