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

Reply via email to