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

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay