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)