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. 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 ) 
Top comments (0)