After updating table id via csv file when trying to add new field getting - duplicate key value violates unique constraint

Question

Problem. After successful data migration from csv files to django /Postgres application .

When I try to add a new record via the application interface getting - duplicate key value violates unique constraint.(Since i had id's in my csv files -i use them as keys )

Basically the app try to generate id's that already migrated. After each attempt ID increments by one so if I have 160 record I have to get this error 160 times and then when I try 160 times the time 161 record saves ok.

Any ideas how to solve it?

Sounds like your sequence needs an update. https://stackoverflow.com/a/8750984 — Adam Hopkins, Sep 30 '17 at 21:06

cwallenpoole · Accepted Answer · 2017-10-01T20:35:58.487

PostgreSQL doesn't have an actual AUTO_INCREMENT column, at least not in the way that MySQL does. Instead it has a special SERIAL. This creates a four-byte INT column and attaches a trigger to it. Behind the scenes, if PostgreSQL sees that there is no value in that ID column, it checks the value of a sequence created when that column was created.

You can see this by:

SELECT 
    TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT 
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_NAME='<your-table>' AND COLUMN_NAME = '<your-id-column>';

You should see something like:

 table_name   | column_name               | column_default
--------------+---------------------------+-------------------------------------
 <your-table> | <your-id-column>          | nextval('<table-name>_<your-id-column>_seq'::regclass)
(1 row)

To resolve your particular issue, you're going to need to reset the value of the sequence (named <table-name>_<your-id-column>_seq) to reflect the current index.

ALTER SEQUENCE your_name_your_id_column_seq RESTART WITH 161;

Credit where credit is due.

After updating table id via csv file when trying to add new field getting - duplicate key value violates unique constraint

1 Answers1