DEV Community

abbazs
abbazs

Posted on

How to fix postgresql duplicate key violates unique constraint?

Shortly after migrating a db from Microsoft SQL to Postgresql, I was trying to add new record only to face with issue:

Original exception was: 
(psycopg2.errors.UniqueViolation) duplicate key value 
violates unique constraint "some_table_pkey"
DETAIL:  Key (id)=(2) already exists.
Enter fullscreen mode Exit fullscreen mode

It seems this is prone to happen whenever there is a bulk update done to the db and how to fix it?

So why does it happen only when do a bulk update?
While doing bulk update the primary key sequence is not going to get incremented, because primary key is already there in the records.

# Imports first!
import pandas as pd
from sqlalchemy import create_engine

# Postgres instance can only be accessed with sslmode
ssl_args = {
    "sslcert": r"C:\.postgresql\postgresql.crt",
    "sslkey": r"C:\.postgresql\postgresql.key",
    "sslmode": "require",
}

# create a sqlalchemy engine
sqlengine = create_engine(
"postgresql+psycopg2://admin:password@x.x.x.x:5432/thedatabase",
connect_args=ssl_args,
)
# Repeat the following steps for the all tables with primary key
table = "problem_table"
pkey = "pkey"
# Get the serial sequence reference using pg_get_serial_sequence
output = pd.read_sql(f"SELECT pg_get_serial_sequence('{table}', '{pkey}');", con=sqlengine )
# Set the serial sequence value to the max value of the primary key 
output = pd.read_sql(f"SELECT setval('{output.iloc[0][0]}', (SELECT MAX({pkey}) FROM {table})+1);", con=sqlengine )
Enter fullscreen mode Exit fullscreen mode

Discussion (0)