# ERROR: INSERT has more target columns than expressions **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [July 29, 2021, 3:59pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357 "2021-07-29T15:59:24Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![arjunrajas](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@arjunrajas](https://forum.inductiveautomation.com/u/arjunrajas)\ **Post date:** [July 29, 2021, 3:59pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/1 "2021-07-29T15:59:24Z")
i am using postgresql Database and i am trying to insert a row into a table using script given below system.db.runPrepUpdate(“INSERT INTO ABC(ROW\_ID,TRXN\_DATE,SHIFT,WORK\_STATION\_ID,PLANT\_ID,SUB\_LOCATION,CUSTOMER\_NAME,OA\_NUM,PARENT\_MNO,CHILD\_MNO,QUALITY,UNIT\_WGT,TRACE\_NO,BIN\_NO,BIN\_POSITION,STATION\_NAME,STATUS,GB\_STATUS,OPR\_NAME,SUPR\_NAME,COMPLETED\_ON,CREATED\_BY,CREATED\_ON,ACTIVE,PF\_ROW\_ID)SELECT(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) WHERE NOT EXISTS (SELECT \* FROM ABC WHERE PLANT\_ID = ? AND TRACE\_NO = ?)”,[unicode(‘null’),timestamp,unicode(shift),unicode(workstationid),unicode(plant\_id),unicode(sub\_location),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(traceno),unicode(binnum),unicode(‘null’),unicode(1),unicode(status),unicode(‘PENDING’),unicode(1),unicode(supervisor),timestamp,unicode(createdby),timestamp,unicode(active),unicode(v\_PF\_ROW\_ID),unicode(plant\_id),unicode(traceno)]) which shows a error report as given below in the image ![error](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/1/4184fdf56f6cdfe6b010cf0cdcda04ed22f55de6.png) can anyone help me to over come this error thanks in advance ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 29, 2021, 4:08pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/2 "2021-07-29T16:08:24Z")
The SQL itself isn’t valid. It should be something like this: ```auto INSERT INTO mytablename (mycolumn1, myothercolumn, onemorecolumn) VALUES (?, ?, ?) ``` Note the VALUE keyword that you do not have in your SQL. When using the INSERT … SELECT … syntax, you wouldn’t have question marks in the select list. ---
**Author:** ![arjunrajas](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@arjunrajas](https://forum.inductiveautomation.com/u/arjunrajas)\ **Post date:** [July 29, 2021, 4:28pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/3 "2021-07-29T16:28:19Z")
i tried something like this system.db.runPrepUpdate(“INSERT INTO ABC(ROW\_ID,TRXN\_DATE,SHIFT,WORK\_STATION\_ID,PLANT\_ID,SUB\_LOCATION,CUSTOMER\_NAME,OA\_NUM,PARENT\_MNO,CHILD\_MNO,QUALITY,UNIT\_WGT,TRACE\_NO,BIN\_NO,BIN\_POSITION,STATION\_NAME,STATUS,GB\_STATUS,OPR\_NAME,SUPR\_NAME,COMPLETED\_ON,CREATED\_BY,CREATED\_ON,ACTIVE,PF\_ROW\_ID)SELECT (int(ROW\_ID),timestamp,unicode(shift),unicode(workstationid),unicode(plant\_id),unicode(sub\_location),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(traceno),unicode(binnum),unicode(‘null’),unicode(‘null1’),unicode(status),unicode(‘PENDING’),unicode(‘null2’),unicode(supervisor),timestamp,unicode(createdby),timestamp,unicode(active),int(v\_PF\_ROW\_ID)) WHERE NOT EXISTS (SELECT \* FROM ABC WHERE PLANT\_ID = ? AND TRACE\_NO = ?)”,[unicode(plant\_id),unicode(traceno)]) and this gives a error as given below ![error1](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/5/055720e907c447d6cc5991392198e9323eab156e.png) ---
**Author:** ![arjunrajas](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@arjunrajas](https://forum.inductiveautomation.com/u/arjunrajas)\ **Post date:** [July 29, 2021, 4:33pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/4 "2021-07-29T16:33:54Z")
when i try removing the type cast it gives error like it should be type casted as in the table ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 29, 2021, 4:51pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/5 "2021-07-29T16:51:20Z")
You are trying to use SELECT where you should be using VALUES. ---
**Author:** ![arjunrajas](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@arjunrajas](https://forum.inductiveautomation.com/u/arjunrajas)\ **Post date:** [July 29, 2021, 4:52pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/6 "2021-07-29T16:52:33Z")
i tried that too system.db.runPrepUpdate(“INSERT INTO DEFLASKING\_TRANSACTIONS(ROW\_ID,TRXN\_DATE,SHIFT,WORK\_STATION\_ID,PLANT\_ID,SUB\_LOCATION,CUSTOMER\_NAME,OA\_NUM,PARENT\_MNO,CHILD\_MNO,QUALITY,UNIT\_WGT,TRACE\_NO,BIN\_NO,BIN\_POSITION,STATION\_NAME,STATUS,GB\_STATUS,OPR\_NAME,SUPR\_NAME,COMPLETED\_ON,CREATED\_BY,CREATED\_ON,ACTIVE,PF\_ROW\_ID)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) WHERE NOT EXISTS (SELECT \* FROM DEFLASKING\_TRANSACTIONS WHERE PLANT\_ID = ? AND TRACE\_NO = ?)”,[int(23),timestamp,unicode(shift),unicode(workstationid),unicode(plant\_id),unicode(sub\_location),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(‘null’),unicode(traceno),unicode(binnum),unicode(‘null’),unicode(1),unicode(status),unicode(‘PENDING’),unicode(1),unicode(supervisor),timestamp,unicode(createdby),timestamp,unicode(active),int(89),unicode(plant\_id),unicode(traceno)]) which shows error like caused by org.python.core.PyException Traceback (most recent call last): File “function:runAction”, line 70, in runAction java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO DEFLASKING\_TRANSACTIONS(ROW\_ID,TRXN\_DATE,SHIFT,WORK\_STATION\_ID,PLANT\_ID,SUB\_LOCATION,CUSTOMER\_NAME,OA\_NUM,PARENT\_MNO,CHILD\_MNO,QUALITY,UNIT\_WGT,TRACE\_NO,BIN\_NO,BIN\_POSITION,STATION\_NAME,STATUS,GB\_STATUS,OPR\_NAME,SUPR\_NAME,COMPLETED\_ON,CREATED\_BY,CREATED\_ON,ACTIVE,PF\_ROW\_ID)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) WHERE NOT EXISTS (SELECT \* FROM DEFLASKING\_TRANSACTIONS WHERE PLANT\_ID = ? AND TRACE\_NO = ?), [23, Thu Jul 29 22:20:23 IST 2021, None, 45, 2001, Plant 2, null, null, null, null, null, null, 120579/6, 2396, null, 1, DEFLASKING COMPLETED, PENDING, 1, admin, Thu Jul 29 22:20:23 IST 2021, admin, Thu Jul 29 22:20:23 IST 2021, Yes, 89, 2001, 120579/6], NC01\_2001\_MES, , false, false) ``` caused by Exception: Error executing system.db.runPrepUpdate(INSERT INTO DEFLASKING_TRANSACTIONS(ROW_ID,TRXN_DATE,SHIFT,WORK_STATION_ID,PLANT_ID,SUB_LOCATION,CUSTOMER_NAME,OA_NUM,PARENT_MNO,CHILD_MNO,QUALITY,UNIT_WGT,TRACE_NO,BIN_NO,BIN_POSITION,STATION_NAME,STATUS,GB_STATUS,OPR_NAME,SUPR_NAME,COMPLETED_ON,CREATED_BY,CREATED_ON,ACTIVE,PF_ROW_ID)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) WHERE NOT EXISTS (SELECT * FROM DEFLASKING_TRANSACTIONS WHERE PLANT_ID = ? AND TRACE_NO = ?), [23, Thu Jul 29 22:20:23 IST 2021, None, 45, 2001, Plant 2, null, null, null, null, null, null, 120579/6, 2396, null, 1, DEFLASKING COMPLETED, PENDING, 1, admin, Thu Jul 29 22:20:23 IST 2021, admin, Thu Jul 29 22:20:23 IST 2021, Yes, 89, 2001, 120579/6], NC01_2001_MES, , false, false) caused by PSQLException: ERROR: syntax error at or near "WHERE" ``` Position: 386 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 29, 2021, 5:05pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/7 "2021-07-29T17:05:07Z")
I don’t understand what you are trying to do. Your SQL is mixing the syntax for inserting values with the syntax for inserting rows copied from elsewhere in the DB. The value style should follow my example given in the comment above. Copying from elsewhere would follow this pattern: ```auto INSERT INTO mytablename (mycolumn1, myothercolumn, onemorecolumn) SELECT mycolumn1, myothercolumn, onemorecolumn FROM sometable WHERE ..... ``` Pick one style or the other. ---
**Author:** ![arjunrajas](https://avatars.discourse-cdn.com/v4/letter/a/6de8d8/32.png) [@arjunrajas](https://forum.inductiveautomation.com/u/arjunrajas)\ **Post date:** [July 29, 2021, 5:18pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/8 "2021-07-29T17:18:13Z")
I am trying to insert a row .if row a does not exist with the provided plantid and traceno in the table ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [July 29, 2021, 6:31pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/9 "2021-07-29T18:31:33Z")
You’ll get a primary key exception. Catch it with a `try:` … `except:` block. No need for a where clause. If you actually need to update if it already exists, then you would use [PostgreSQL’s ON CONFLICT](https://www.postgresql.org/docs/11/sql-insert.html#SQL-ON-CONFLICT) syntax. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [July 29, 2021, 7:05pm UTC](https://forum.inductiveautomation.com/t/error-insert-has-more-target-columns-than-expressions/49357/10 "2021-07-29T19:05:40Z")
This post might be of help > [@Insert into database only if the entry doesn't exist](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/2): > > There are two ways to do this - check for a duplicate manually with python before inserting, and if there’s a problem show the screen Make UNIQUE INDEX definitions on your table in the database, and then just try/except inserting into it. Personally I like to leave data integrity to the database where appropriate, and ensuring UNIQUE-ness is one of those times in my opinion. I use this method for a MySQL DB and it looks like this in practice. My personal solution would be to make your n…