I’m using the script below on a property change event of a PLC tag and I get the error column reference “serialnumber” is ambiguous.
Anybody know what that means?
Thanks!
if currentValue.value == True:
laserdata = system.tag.read("[.]LaserSinkNumber").value
db = 'PostgreSQL'
if system.tag.read("[.]LaserFail").value == True:
laser = 'Scan Failed'
query = """
INSERT INTO packtrack (generictime, lasertime, lasersink)
VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)
"""
system.db.runPrepUpdate(query, [laser], db)
else:
laser = 'Scan Successful'
query = """
INSERT INTO packtrack (serialnumber, generictime, lasertime, lasersink)
VALUES (?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)
ON CONFLICT (serialnumber) DO UPDATE
SET generictime = EXCLUDED.generictime, lasertime = EXCLUDED.lasertime, lasersink = EXCLUDED.lasersink
WHERE serialnumber=?
"""
system.db.runPrepUpdate(query, [laserdata, laser, laserdata], db)
It means that there is more than one column named serialnumber and SQL doesn’t know which one to use. If I had to guess, you probably need to change serialnumber to either packtrack.serialnumber or EXCLUDED.serialnumber in your WHERE clause.
I changed my script as below and now I get this:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
Does the column ON CONFLICT is looking at have to be a integer? Right now my serialnumber column is a string.
if currentValue.value == True:
print "line 4"
laserdata = system.tag.read("[.]Laser_Sink_Number").value
db = 'PostgreSQL'
if system.tag.read("[.]Laser_Barcode_Error").value == True:
laser = 'Scan Failed'
query = """
INSERT INTO packtrack (generictime, lasertime, lasersink)
VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)
"""
system.db.runPrepUpdate(query, [laser], db)
print "line 14"
else:
laser = 'Scan Successful'
query = """
INSERT INTO packtrack (serialnumber, generictime, lasertime, lasersink)
VALUES (?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?)
ON CONFLICT (serialnumber) DO UPDATE
SET generictime = CURRENT_TIMESTAMP, lasertime = CURRENT_TIMESTAMP, lasersink = ?
WHERE packtrack.serialnumber=?
"""
system.db.runPrepUpdate(query, [laserdata, laser, laser, laserdata], db)
print "end"
To trigger a conflict clause, your table needs to be set up so that serialnumber (in this case) is marked as unique/exclusive, in Postgres. You need to update your table configuration.