# DB Tables and Unique or duplicate values

**URL:** <https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678>\
**Category:** Ignition\
**Created:** [October 14, 2020, 6:38pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678 "2020-10-14T18:38:56Z")\
**Posts on this page:** 10\
**Page:** 2

<div class="post-metadata">

**Author:** ![tyrel.parker](https://avatars.discourse-cdn.com/v4/letter/t/47e85d/32.png) [@tyrel.parker](https://forum.inductiveautomation.com/u/tyrel.parker)\
**Post date:** [October 20, 2020, 6:07pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/21 "2020-10-20T18:07:02Z")

</div>

You can define a logger in some code like:

```auto
logger = system.util.logger('what i want to show in the gateway logger column')

```

Then you can call that whenever with:

```auto
logger.info('message')

```

Just a way to setup the name of your logger at the top of your code without having to retype it for each message.

```auto
try:
    system.db.runNamedQuery('query', param)

except Exception as e:
    msg = str(e.getCause())
    if 'Cannot insert duplicate key' in msg:
        ### code to popup a window with a message, or whatever else you want to do with it ###

```

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 20, 2020, 6:24pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/22 "2020-10-20T18:24:53Z")

</div>

@tyrel.parker,

Ok, so I have defined the logger in the window open script like this…

logger = system.util.getLogger(“binderLogger”)

This way I can call if from any component in the window. (if I understand that correctly.)

I added this to my code…

```auto
try:
        #Update binder_page table
	system.db.runNamedQuery("page_name_update", {"new_page" :newPage, "topic_id" :topic_id})
			
except Exception as e:
	msg = str(e.getClause())
	logger.info('except javalang: {}'.format( msg)
	if 'duplicate key value violates unique constraint' in msg:
		logger.info( 'Page Name Already Exists.')
					
	else:
	pass
					

```

For some reason it does not like the if statement. says there is no alternative to the if. I must be missing something or syntax is not just right.

Any idea?

Thanks, Steven

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 20, 2020, 6:57pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/23 "2020-10-20T18:57:00Z")

</div>

So I found a missing bracket on the logger.info line.

it does not seem to affect anything. ☹

Still pops up the same error message.

Not sure where to go now.

Thanks, Steven

---

<div class="post-metadata">

**Author:** ![tyrel.parker](https://avatars.discourse-cdn.com/v4/letter/t/47e85d/32.png) [@tyrel.parker](https://forum.inductiveautomation.com/u/tyrel.parker)\
**Post date:** [October 20, 2020, 7:36pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/24 "2020-10-20T19:36:34Z")

</div>

My guess is that you need to tab in the pass or leave off the else all together.

```auto
else:
    pass

```

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 20, 2020, 8:07pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/25 "2020-10-20T20:07:01Z")

</div>

I removed the else all together. I still get the same error message …

```auto
Traceback (most recent call last):
  File "<event:propertyChange>", line 24, in <module>
  File "<event:propertyChange>", line 24, in <module>
  Detail: Key (lower(page_name::text), topics_id)=(page 2, 9) already exists.

com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pagename_idx"
  Detail: Key (lower(page_name::text), topics_id)=(page 2, 9) already exists.

	caused by GatewayException: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pagename_idx"
  Detail: Key (lower(page_name::text), topics_id)=(page 2, 9) already exists.
	caused by Exception: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pagename_idx"
  Detail: Key (lower(page_name::text), topics_id)=(page 2, 9) already exists.

Ignition v8.0.16 (b2020082513)
Java: Azul Systems, Inc. 11.0.7

```

I guess it is not capturing the error.

Thanks, Steven

---

<div class="post-metadata">

**Author:** ![tyrel.parker](https://avatars.discourse-cdn.com/v4/letter/t/47e85d/32.png) [@tyrel.parker](https://forum.inductiveautomation.com/u/tyrel.parker)\
**Post date:** [October 21, 2020, 9:15pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/26 "2020-10-21T21:15:28Z")

</div>

That error is from the gateway logs? Or when you click the button it gives you the error popup with that message?

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [October 21, 2020, 10:55pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/27 "2020-10-21T22:55:37Z")

</div>

@tyrel.parker it is in a popup after I press enter ( on property change).

Thanks, Steven

---

<div class="post-metadata">

**Author:** ![tyrel.parker](https://avatars.discourse-cdn.com/v4/letter/t/47e85d/32.png) [@tyrel.parker](https://forum.inductiveautomation.com/u/tyrel.parker)\
**Post date:** [November 16, 2020, 7:15pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/28 "2020-11-16T19:15:48Z")

</div>

@steven.cox,

Did you figure this out?

Tyrel

---

<div class="post-metadata">

**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:** [November 16, 2020, 7:47pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/29 "2020-11-16T19:47:57Z")

</div>

You can follow this method I outline in another post after you make the UNIQUE indexes

> [@Insert into database only if the entry doesn't exist](http://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…

---

<div class="post-metadata">

**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\
**Post date:** [November 16, 2020, 11:47pm UTC](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678/30 "2020-11-16T23:47:20Z")

</div>

> [@tyrel.parker](#):
>
> Did you figure this out?

I created a check prior to running the insert query so no error occurs. Made it nice and clean. I was checking for a blank anyway, so I just added to check for a duplicate as well.

Thanks for following up,

Steven

[Previous page](https://forum.inductiveautomation.com/t/db-tables-and-unique-or-duplicate-values/39678.md?page=1)