# Error column reference " " is ambiguous

**URL:** <https://forum.inductiveautomation.com/t/error-column-reference-is-ambiguous/44315>\
**Category:** Ignition\
**Created:** [March 10, 2021, 3:29pm UTC](https://forum.inductiveautomation.com/t/error-column-reference-is-ambiguous/44315 "2021-03-10T15:29:15Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\
**Post date:** [March 10, 2021, 3:29pm UTC](https://forum.inductiveautomation.com/t/error-column-reference-is-ambiguous/44315/1 "2021-03-10T15:29:15Z")

</div>

Hi all,

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!

```auto
 	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)

```

---

<div class="post-metadata">

**Author:** ![chandler](https://avatars.discourse-cdn.com/v4/letter/c/e9a140/32.png) [@chandler](https://forum.inductiveautomation.com/u/chandler)\
**Post date:** [March 10, 2021, 4:05pm UTC](https://forum.inductiveautomation.com/t/error-column-reference-is-ambiguous/44315/2 "2021-03-10T16:05:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\
**Post date:** [March 10, 2021, 5:13pm UTC](https://forum.inductiveautomation.com/t/error-column-reference-is-ambiguous/44315/3 "2021-03-10T17:13:27Z")

</div>

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.

```auto
	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"	

```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [March 10, 2021, 5:16pm UTC](https://forum.inductiveautomation.com/t/error-column-reference-is-ambiguous/44315/4 "2021-03-10T17:16:39Z")

</div>

> [@casian](#):
>
> ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

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.

---

<div class="post-metadata">

**Author:** ![casian](https://avatars.discourse-cdn.com/v4/letter/c/b19c9b/32.png) [@casian](https://forum.inductiveautomation.com/u/casian)\
**Post date:** [March 10, 2021, 7:43pm UTC](https://forum.inductiveautomation.com/t/error-column-reference-is-ambiguous/44315/5 "2021-03-10T19:43:56Z")

</div>

I figured it out.  
For the ON CONFLICT to work you have to create a unique index on the column you’re looking at