ERROR: INSERT has more target columns than expressions

Ignition
1

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
error1911×1017 86.6 KB

can anyone help me to over come this error
thanks in advance

2

The SQL itself isn’t valid. It should be something like this:

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.

3

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
error11911×1017 78.3 KB

4

when i try removing the type cast it gives error like it should be type casted as in the table

5

You are trying to use SELECT where you should be using VALUES.

6

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

7

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:

INSERT INTO mytablename (mycolumn1, myothercolumn, onemorecolumn)
SELECT mycolumn1, myothercolumn, onemorecolumn
FROM sometable
WHERE .....

Pick one style or the other.

8

I am trying to insert a row .if row a does not exist with the provided plantid and traceno in the table

9

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 syntax.

2 Likes
10

This post might be of help