DEV Community

abbazs
abbazs

Posted on • Edited on

7 2

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

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay