Why I obtain this error migrationg data from SQL Lite to Posgres DB ? duplicate key value violates unique constraint

Question

I am pretty new in Django and Python and I am trying to follow this videotutorial about how to migrate a Django application from SqlLite to Postgres database:

https://www.youtube.com/watch?v=ZgRkGfoy2nE&t=554s

But I am finding some problem. Following the details on what I have done:

I am working on Ubuntu 20.04 and I have a Django application made by someone else that uses SQLLite and that have to be migrated on Postgres. Following the previous tutorial I have done the following steps:

First of all I installed Postegres and PgAdmin4 on my Ubuntu system. Then here I created a DB named dgsrgdb that have to be my new database for my application in replacement of SQL Lite DB.
I installed this package to let Python\Django operate with Postgres:
```
pip3 install psycopg2-binary
```
I performed the backup of my original SqlLite DB by this command:
```
python3 manage.py dumpdata > datadump.json
```
and I obtained the datadump.json file that should contains the data inside the original DB that have to moved on the new Postgres DB.

Into the Django settings.py file I replaced this configuration:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

whith this configuration related to Postgres:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'dgsrg',
        'USER': 'dgsrguser',
        'PASSWORD': 'password',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

so now my application points to my new dgsrgdb previously created on Postgres. I am using the "root" postgres user at the moment.

And now my problem. As shown in the tutorial I execute this command in my command line in order to create my tables on my dgsrg Postgres DB:
```
python3 manage.py migrate --run-syncdb
```
The tables related to my Django applications seems to be created on my new DB, this is what I have using PgAdmin:

Then following the tutorial I am trying to import the data previously exported into my datadump.json* file into these tables using this command:
```
python manage.py loaddata datadump.json
```

but perfroming this command I am obtaining this error message:

    (DGSRG) andrea@ubuntu:~/Documenti/Python-WS/django_projects/dgsrg$ python manage.py loaddata datadump.json
    Traceback (most recent call last):
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
        return self.cursor.execute(sql, params)
    psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
    DETAIL:  Key (app_label, model)=(admin, logentry) already exists.


    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "manage.py", line 21, in <module>
        main()
      File "manage.py", line 17, in main
        execute_from_command_line(sys.argv)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
        utility.execute()
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/__init__.py", line 395, in execute
        self.fetch_command(subcommand).run_from_argv(self.argv)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/base.py", line 330, in run_from_argv
        self.execute(*args, **cmd_options)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/base.py", line 371, in execute
        output = self.handle(*args, **options)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 72, in handle
        self.loaddata(fixture_labels)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 114, in loaddata
        self.load_label(fixture_label)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/management/commands/loaddata.py", line 181, in load_label
        obj.save(using=self.using)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/core/serializers/base.py", line 223, in save
        models.Model.save_base(self.object, using=using, raw=True, **kwargs)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/base.py", line 790, in save_base
        updated = self._save_table(
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/base.py", line 872, in _save_table
        updated = self._do_update(base_qs, using, pk_val, values, update_fields,
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/base.py", line 926, in _do_update
        return filtered._update(values) > 0
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/query.py", line 803, in _update
        return query.get_compiler(self.db).execute_sql(CURSOR)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1522, in execute_sql
        cursor = super().execute_sql(result_type)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1156, in execute_sql
        cursor.execute(sql, params)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 98, in execute
        return super().execute(sql, params)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
        return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
        return executor(sql, params, many, context)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
        return self.cursor.execute(sql, params)
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
        raise dj_exc_value.with_traceback(traceback) from exc_value
      File "/home/andrea/Documenti/Python-WS/Environments/DGSRG/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
        return self.cursor.execute(sql, params)
    django.db.utils.IntegrityError: Problem installing fixture '/home/andrea/Documenti/Python-WS/django_projects/dgsrg/datadump.json': Could not load contenttypes.ContentType(pk=1): duplicate key value violates unique constraint "django_content_type_app_label_model_76bd3d3b_uniq"
    DETAIL:  Key (app_label, model)=(admin, logentry) already exists.

Why? What could be the problem? What am I missing? How can I try to fix it?

related: https://stackoverflow.com/q/42125730, https://stackoverflow.com/q/46416877, https://stackoverflow.com/q/55641992, https://stackoverflow.com/q/64662550 — djvg, Jun 06 '23 at 10:14

score 1 · Answer 1 · answered Jan 15 '21 at 10:36

Before loading data using loaddata command,

We have to flush all the tables because django itself create some objects during migration.

Try running below command, command will flush data of all tables:

 python manage.py sqlflush | python manage.py dbshell

Then, loaddata into the PostgreSQl.

score 1 · Answer 2 · answered Jan 15 '21 at 10:45

The problem is that by dumping all the data from sqlite, you're also copying contenttypes. Keep in mind that the contenttypes framework contains metadata about your models. Your traceback error points to this possibility when you tried to load admin.LogEntry into the contenttypes table when it already had that data from migrate.

What you should do is to exclude contenttypes in your dumpdata.

python manage.py dumpdata --exclude=contenttypes

There is also a possibility that you may have to exclude auth.Permission since the default view, change, and delete permissions of models are created on migrate, though I cannot say whether this behavior happens during a run-syncdb. You could test dumpdata first without excluding auth.Permission.

score 0 · Answer 3 · answered Jan 15 '21 at 10:51

I am not very sure why the error came but, I kind of solved it by specifying all app names needed in the dumped

python manage.py dumpdata auth.user auth.group app1 app2 app3 > database.json

it will dump all needed data and python manage.py loaddata database.json command won't return any error.

Why I obtain this error migrationg data from SQL Lite to Posgres DB ? duplicate key value violates unique constraint

3 Answers3

Linked