The psycopg2.errors.UniqueViolation is an error thrown by the when a user attempts to insert a duplicate key value.
In an SQL or SQL-like database a key value is defined when a table is created. This key value is then used to reference specific rows of the table. In order to make calls to these rows unambiguous, this key value must be unique for every row.
Any attempt to insert a new row which has a value in the key field that already exists in the table cannot be completed. This will result in a UniqueViolation error.
An Example of psycopg2.errors.UniqueViolation
In the below example, we are attempting to create a new table and populate it with data. However, our code will cause a UniqueViolation error to be thrown.
import psycopg2
def create_tables(cur):
cur.execute("create table student2(name char(30), gender char(30), birthday char(30), primary key(name));")
def fill_tables(cur):
cur.execute("insert into student2 (name, gender, birthday) values ('Tom', 'male', '11-21-2000');")
cur.execute("insert into student2 (name, gender, birthday) values ('Tom', 'male', '03-31-2001');")
try:
conn = psycopg2.connect("dbname='postgres' user='postgres' host='localhost' password='pa55w0rd'")
cur = conn.cursor()
except:
print("I am unable to connect to the database")
if __name__ == '__main__':
create_tables(cur)
fill_tables(cur)In the above example, a new table is created using the create_tables() function. This table stores the name, gender, and birthday for each entry. Notice that the name is being used as the primary key, which means this value cannot be duplicated. Any other values can be duplicated as needed. For example the gender field is expected to be repeated frequently as it has very few possible values.
Next, we insert two rows into the table. Each row has a name, gender, and birthday to match the table. Notice, however, that both rows have identical names. This means our program will throw an error when we try to insert the second row.
The Output of the Example Code with psycopg2.errors.UniqueViolation
In the output below, we can see the error message printed to the console when the above code is run. The message in the output shows that a psycopg2.errors.UniqueViolation error was thrown by the second insert command.
C:/code/python/PostreSQL/unique_violation
Traceback (most recent call last):
File "C:/code/python/PostreSQL/unique_violation", line 19, in <module>
fill_tables(cur)
File "C:/code/python/PostreSQL/unique_violation", line 9, in fill_tables
cur.execute("insert into student2 (ID, name, gender, birthday) values ('Tom', 'male', '03-31-2001');")
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "student2_pkey"
DETAIL: Key (name)=(Tom) already exists. finished with exit code 1
How to Handle A UniqueViolation Error
The short answer to avoiding this error is simply to avoid using a duplicate key. However, that can be challenging in situations where we need to put two or more similar records on our table. One straightforward solution is to include a second field in the primary key.
Solution 1: UniqueViolation Error Handling
def create_tables(cur):
cur.execute("create table student2(name char(30), gender char(30), birthday char(30), primary key(name, birthday));")In this table, the name and the birthday are used in conjunction as a key. This way, there is only an error if both the name and the birthday are equal between two rows, which is unlikely. This does, however, mean that any time you are referring to a row anywhere else in your code, you will be required to reference both values rather than just one. This can make the code more complex and increase the likelihood of errors.
Another common solution is the introduction of a new field that exists purely to differentiate between the rows. This means there is no possibility of repetition, but it does mean special infrastructure must be built to assign and maintain these values.
Solution 2: UniqueViolation Error Handling
In the example below, an ID column has been added and set as the primary key. Each of the two Toms is then given a unique ID to identify them in the system.
def create_tables(cur):
cur.execute("create table student2(ID int, name char(30), gender char(30), birthday char(30), primary key(ID));")
def fill_tables(cur):
cur.execute("insert into student2 (ID, name, gender, birthday) values ('1', 'Tom', 'male', '11-21-2000');")
cur.execute("insert into student2 (ID, name, gender, birthday) values ('101', 'Tom', 'male', '03-31-2001');")
How to Avoid UniqueViolation Errors
Typically, UniqueViolation errors are evidence of an underlying issue with the design of your database. Consider if there are values that should be unique in your data set (email, SKU number, or timestamp, for example) and whether those would make appropriate keys. Additionally, consider how the data in these tables will be updated and accessed. Careful planning of the layout of a database will avoid UniqueViolation errors along with a host of other errors as that database grows.
Track, Analyze and Manage Errors With Rollbar
Managing errors and exceptions in your code is challenging. It can make deploying production code an unnerving experience. Being able to track, analyze, and manage errors in real-time can help you proceed with more confidence. Rollbar automates error monitoring and triaging, making fixing Python errors easier than ever. Sign Up Today!
