Django Import-Export Import Duplicate key value violates Error

Question

I'm working on a project with Django 1.10, Python 3.6 and PostgreSQL as the database backend, in which I have a model called 'Article" and I need to import data from CSV files. I have imported my first CSV file successfully with the following fields: id, link & category It's ID fields starts from 1 to 1960 then in next file, I have started ID field from 1961 to onward but it shows the following error:

Line number: 1961 - duplicate key value violates unique constraint "article_article_pkey" DETAIL: Key (id)=(1961) already exists.

Even when i see my Article model in Django admin it shows IDs from 1- 1960

Here's my models.py:

class Article(models.Model):
   id = models.AutoField(primary_key=True)
   link = models.URLField(max_length=255)
   category = models.CharField(max_length=255, choices=Categories)

Here's admin.py

@admin.register(Article)
    class ArticleAdmin(ImportExportModelAdmin):
    resource_class = views.ArticleResource
    readonly_fields = ('id',)

`class ArticleResource(resources.ModelResource): class Meta: model = Article` — Abdul Rehman, Oct 19 '17 at 06:59
try adding import_id_fields = ('id',) http://django-import-export.readthedocs.io/en/latest/getting_started.html — Sandeep Balagopal, Oct 19 '17 at 07:02
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/157044/discussion-between-sandeep-balagopal-and-abdul-rehman). — Sandeep Balagopal, Oct 19 '17 at 07:08
I've had similar problems before, because Django was generating the ids itself (when you set id to be readonly). What I had to do was manually reset the sequence of the id field: https://stackoverflow.com/questions/4678110/how-to-reset-sequence-in-postgres-and-fill-id-column-with-new-data not sure if that fits on your problem. but it might be a hint in the right direction — platzhersh, Oct 19 '17 at 07:30
how can I manually reset the sequence of id field in PostgreSQL? please! — Abdul Rehman, Oct 19 '17 at 07:31

score 2 · Accepted Answer · answered Oct 20 '17 at 05:15

I have triggered that what's the issue : Actually, the problem is PostgreSQL primary key sequential which is not synced with table rows. That's why, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists.

To solve this issue we have to reset the ID sequential for PostgreSQL, Here's the step by step guide:

Log onto the DB shell and connect to your database
First, check maximum value for id column of your table as SELECT MAX(id) FROM your_table;
Then, check what's going to be the next value for ID as : SELECT nextval('your_table_id_seq');
If nextval is the next number of Max value then it's right. e.g MAX=10 & nextval=11
Otherwise reset the id_seq for your table as:

BEGIN;

-- protect against concurrent inserts while you update the counter

LOCK TABLE your_table IN EXCLUSIVE MODE;

-- Update the sequence

SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);

COMMIT;

This correctly identifies the problem I had. Django now comes with a tool to fix this: `python manage.py sqlsequencereset ` — Nick Lothian, Oct 24 '19 at 03:00

score 1 · Answer 2 · answered Sep 25 '19 at 11:07

I had this issue, because I'd specified a custom import_id_fields field but hadn't excluded the id field.

I had specified:

    import_id_fields = ('code',)
    skip_unchanged = True
    report_skipped = True

But I needed:

    import_id_fields = ('code',)
    skip_unchanged = True
    report_skipped = True
    exclude = ('id',)

Bit of a noob error but this might save someone a google.

Ref: https://github.com/django-import-export/django-import-export/issues/273

score 1 · Answer 3 · answered Jan 13 '22 at 11:40

1

i often run a for loop. This also updates my auto datetime fields:

import Article
for i in range(1, your_last_id + 1):
    item = Article.objects.get(id=i)
    item.save()

your_last_id may be SELECT MAX(id)

answered Jan 13 '22 at 11:40

Électron libre

11
2

Django Import-Export Import Duplicate key value violates Error

3 Answers3

Linked