# Insert into database only if the entry doesn't exist **URL:** \ **Category:** Ignition\ **Tags:** perspective, database\ **Created:** [May 28, 2020, 2:11pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122 "2020-05-28T14:11:58Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![smcfarlane](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/smcfarlane/32/42150_2.png) [@smcfarlane](https://forum.inductiveautomation.com/u/smcfarlane)\ **Post date:** [May 28, 2020, 2:11pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/1 "2020-05-28T14:11:58Z")
I have a perspective view where I am adding users to a database on a button event script. I want to prevent duplicate names from being added, in other words if the name already exists in the table, don’t add the entry and put an alert on the screen. Originally I wanted to do this check in the python script. I pull down the column of names from the DB and compare the new name to be added to that list using the ‘x in list’, but the strings returned from the db have a fixed size and have padded spaces at the end. So instead of the list containing strings like “John Doe” it has "John Doe " and my comparison fails. The datatype of the column is nchar(20) which is fixed length as I understand. Is there away to make this field variable length or a better way to do this comparison in python? ---
**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:** [May 28, 2020, 2:25pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/2 "2020-05-28T14:25:44Z")
There are two ways to do this - 1. check for a duplicate manually with python before inserting, and if there’s a problem show the screen 2. 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 name column or whatever you call it in your table UNIQUE. Then, you can do something like this - ```auto import java.lang.Exception try: #Your insert statement here except Exception, e: # Catches any python errors except java.lang.Exception, e: #SQL Integrity errors come as Java errors so you need java.lang.Exception if "Duplicate entry" in str(e.cause): #Do something to let user know its a duplicate ``` One thing to note - The error you get from a Integrity error is probably different depending on what database you so, so your `if "Duplicate entry" in str(e.cause)` may have to be different. But once you make a unique constraint, you can easily trigger the error and see exactly what you get. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [May 28, 2020, 2:30pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/3 "2020-05-28T14:30:57Z")
You can use the LTRIM and RTRIM functions to tidy up your returned names from the SQL query. These functions remove spaces (among other things) at the start and end of the a string. But yes, as @bkarabinchak.psi said, you should also configure the name field in the table to be unique, and that exception handling is always handy to know! ---
**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:** [May 28, 2020, 2:35pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/4 "2020-05-28T14:35:23Z")
> [@bkarabinchak.psi](#): > > 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. +1 ---
**Author:** ![smcfarlane](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/smcfarlane/32/42150_2.png) [@smcfarlane](https://forum.inductiveautomation.com/u/smcfarlane)\ **Post date:** [May 28, 2020, 2:39pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/5 "2020-05-28T14:39:47Z")
Im far from a SQL expert so I will look in to the UNIQUE definitions, thanks! ---
**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:** [May 28, 2020, 2:44pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/6 "2020-05-28T14:44:47Z")
No problem! For future reference you can also make multi-column unique indexes, so if your table had a firstName column and a lastName column you could make it so you only accept unique combinations of first and last names. ---
**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\ **Post date:** [May 28, 2020, 6:00pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/7 "2020-05-28T18:00:12Z")
> [@bkarabinchak.psi](#): > > For future reference you can also make multi-column unique indexes, so if your table had a firstName column and a lastName column you could make it so you only accept unique combinations of first and last names. One caveat to this -- if the columns don't have a NOT NULL constraint you can get some unexpected results with some SQL vendors. NULL doesn't equal NULL in SQL, so you can have what seems like duplicate entries when one value is NULL. If you need to allow NULL values, take a look and see how your database handles the uniqueness of them. If it's an issue, there are several ways around it, depending on the DB, but it's good to find out _before_ it's a problem. 😉 ---
**Author:** ![Radim](https://avatars.discourse-cdn.com/v4/letter/r/f0a364/32.png) [@Radim](https://forum.inductiveautomation.com/u/Radim)\ **Post date:** [June 3, 2020, 4:08pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/8 "2020-06-03T16:08:27Z")
Hi, if you can change column type in your DB you can use nvarchar(20). It avoids blanks padding. Then you can use your way. Also is possible to use IF EXISTS… clause in SQL ---
**Author:** ![conan.a](https://avatars.discourse-cdn.com/v4/letter/c/22d042/32.png) [@conan.a](https://forum.inductiveautomation.com/u/conan.a)\ **Post date:** [January 19, 2021, 10:54am UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/9 "2021-01-19T10:54:16Z")
Hi There, Didn’t want to start a new thread as my query seemed close enough to the above but is there a way you can do the above with 2 columns on the same row? ## Existing Table: **Code | Desc** 100 | Test1 Data Entry: 100 | Test1 - **Rejected** Data Entry: 101 | Test1 - **Accepted** I can start a new thread with a more clear example if needed? Cheers. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [January 19, 2021, 1:23pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/10 "2021-01-19T13:23:27Z")
> [@conan.a](#): > > is there a way you can do the above with 2 columns on the same row? The above is for multiple columns of the same row? So, I am not certain what you're asking. When creating a UNIQUE INDEX the question you need to answer is what makes this data UNIQUE. For instance in the example that you gave, would the values 100 | Test2 be Accepted or Rejected? If yes then you need to make a CLUSTERED INDEX. This tells the database to compare the values of both the code and desc column to determine uniqueness. The way this is done depends on the database you're using, and as @KathyApplebaum cautions above you should be aware of how your database treats NULL values if either column doesn't have a NOT NULL constraint. If only the code determines if the row is unique or not then you would create a simple UNIQUE INDEX. ---
**Author:** ![CMuecke](https://avatars.discourse-cdn.com/v4/letter/c/df705f/32.png) [@CMuecke](https://forum.inductiveautomation.com/u/CMuecke)\ **Post date:** [July 22, 2022, 8:06pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/11 "2022-07-22T20:06:04Z")
Is there a small example that anyone would have for this? ---
**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 22, 2022, 8:55pm UTC](https://forum.inductiveautomation.com/t/insert-into-database-only-if-the-entry-doesnt-exist/35122/12 "2022-07-22T20:55:17Z")
> [@bkarabinchak.psi](#): > > ```python > import java.lang.Exception > try: > #Your insert statement here > except Exception, e: > # Catches any python errors > except java.lang.Exception, e: > #SQL Integrity errors come as Java errors so you need java.lang.Exception > if "Duplicate entry" in str(e.cause): > #Do something to let user know its a duplicate > > ``` Could be something like ```python #Updated with Thrwoable import java.lang.Throwable try: insertParams = {'namedQueryParam1':1, 'namedQueryParam2':'something'} system.db.runNamedQuery("SomeInsertQuery",insertParams) except Exception, e: # Catches any python errors except java.lang.Throwable, e: #SQL Integrity errors come as Java errors so you need java.lang.Exception if "Duplicate entry" in str(e.cause): #Do something to let user know its a duplicate updateParams = {'someNamedQueryParams':"someValue",...} system.db.runNamedQuery("someUpdateQuery", updateParams) ``` My example uses MySQL 5.6. You'll have to do some testing or read your databases documentation to see what `if "Duplicate entry" in str(e.cause):` should be for you.