# Can't Transfer data from SQLITE3 to Postgres

**URL:** <https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810>\
**Category:** Using the ORM\
**Created:** [February 13, 2023, 9:34am UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810 "2023-02-13T09:34:49Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 13, 2023, 9:34am UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/1 "2023-02-13T09:34:49Z")

</div>

**the Commands i have used to transfer the data**

```auto
python3 manage.py dumpdata > datadump.json

python3 manage.py migrate --run-syncdb

```

But i get the following error

the error start from here

```auto
  Applying testing.0024_user_affiliate_id... OK
  Applying testing.0025_main_profile_affiliate_id_delete_user_affiliate_id...Traceback (most recent call last)

```

**The error message `value too long for type character varying(10)`**

SO i go to my models file and change the following and makemigrations and migrate

**I also delete the postgres database and make a new one just in case**

**From this**

`class main_profile(models.Model):`

```
user = models.OneToOneField(User, on_delete=models.CASCADE)

profile_img = ResizedImageField(
    size=[250, 250], quality=100, upload_to='profile_img', default='default_profile.jpg', blank=True)

affiliate_id = models.CharField(max_length=10, default=uuid.uuid4, unique = True,blank= True, null = True)

```

**to this**

`class main_profile(models.Model):`

```
user = models.OneToOneField(User, on_delete=models.CASCADE)

profile_img = ResizedImageField(
    size=[250, 250], quality=100, upload_to='profile_img', default='default_profile.jpg', blank=True)

affiliate_id = models.TextField(default=uuid.uuid4, unique = True,blank= True, null = True)

```

but i still get the same Error, Please help 🙂

---

<div class="post-metadata">

**Author:** ![DanielGnzlzVll](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/danielgnzlzvll/32/4585_2.png) [@DanielGnzlzVll](https://forum.djangoproject.com/u/DanielGnzlzVll)\
**Post date:** [February 13, 2023, 12:46pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/2 "2023-02-13T12:46:09Z")

</div>

Hi,

The thing is sqlite does not restrict the long for varchar registers, so I suppose you have one or more registers with a long bigger than 10. So I would search the migration when you created that field and change the max\_lenght both in the migration file and in the model file.

---

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 13, 2023, 12:59pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/3 "2023-02-13T12:59:43Z")

</div>

hi so now i tried another approach of doing this below

```auto
#affiliate_id = models.CharField(max_length=10, default=uuid.uuid4, unique = True,blank= True, null = True)

```

and then did makemigrations and migrate

and then used the commands to datadump.json and then run the sync commaond but i still get the error, even tough i removed the field and the objects from the model.

i double checked the data and it didnt have the affiliate\_id data, but why still i get the error ?

please help

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 13, 2023, 1:18pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/4 "2023-02-13T13:18:11Z")

</div>

It may help if you posted the complete traceback. There’s always the chance that the root issue is higher up in the stack.

---

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 13, 2023, 1:32pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/5 "2023-02-13T13:32:34Z")

</div>

ok so the first reply was correct in my migrations folder i had to make some changes and it worked perfectly , but now i get another error in the last part

```auto
python3 manage.py loaddata datadump.json

Traceback (most recent call last):
  File "/home/firaki/.local/lib/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "testing_profile_pkey"
DETAIL: Key (id)=(1) already exists.

```

---

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 13, 2023, 1:33pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/6 "2023-02-13T13:33:23Z")

</div>

hi you were so right, thanks you so much. i had to alter some migration files and it worked but now i have problem in the last part, can you see my below comment to @KenWhitesell

thanks

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 13, 2023, 1:55pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/7 "2023-02-13T13:55:42Z")

</div>

Two different likely causes here:

- There’s already data in the target database when doing the loaddata. Empty the tables and retry

- There’s bad data in the datadump where multiple entries for the same key exist. In that case you either need to fix the data at the source or edit the datadump file to remove the duplicate.

---

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 13, 2023, 2:14pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/8 "2023-02-13T14:14:17Z")

</div>

ok the First option i dropped the postgres database and made a new one, so no chance of duplicate data there

now the second part i am confused now how can there be duplicates ? i do have a manytomany field maybe because of that ?

**here are all of my models**

```auto
# Create your models here.

def file_directory_path(instance, filename):

    return f"{instance.country}/{instance.shop}/{filename}"

class retailer(models.Model):

    links = models.CharField(max_length=9999, unique=True)

    shop = models.CharField(max_length=100)

    country = models.CharField(max_length=300)

    category = models.CharField(max_length=300)

    title = models.CharField(max_length=300)

    price = models.DecimalField(max_digits=10, decimal_places=2)

    image = ResizedImageField(
        size=[250, 250], quality=100, upload_to=file_directory_path)

    color = models.CharField(max_length=300)

    slug = AutoSlugField(
        populate_from=['country', 'shop', 'category', 'title'])

    date_posted = models.DateTimeField(default=timezone.now)

    no_of_likes = models.IntegerField(
        default=0, validators=[MinValueValidator(0)])

    liked_by = models.ManyToManyField(User, related_name='likes', blank=True)

    def __str__ (self):

        return self.title

    def save(self, *args, **kwargs):

        self.shop = self.shop.strip()

        super().save(*args, **kwargs)

    def count(self):

        return self.count()

class Like(models.Model):

	
    retailer = models.ForeignKey(retailer, on_delete=models.SET_NULL, null=True)
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    date_liked = models.DateTimeField(default=None, null=True, blank=True)

    # class Meta:
    # unique_together = ['retailer', 'user']

    def __str__ (self):
        return str(self.retailer) + " - " + str(self.user) + " - " + str(self.date_liked)

class unique_colors(models.Model):

    color_name = models.CharField(max_length=300, unique=True)

    color_image = models.FileField(upload_to="color_images")

    def __str__ (self):

        return self.color_name

# Profile Models Started Below 

class main_profile(models.Model):

    user = models.OneToOneField(User, on_delete=models.CASCADE)

    profile_img = ResizedImageField(
        size=[250, 250], quality=100, upload_to='profile_img', default='default_profile.jpg', blank=True)

    bio = models.TextField(blank=True)

    location = models.CharField(max_length=30, blank=True)

    no_of_followers = models.IntegerField(
        default=0, validators=[MinValueValidator(0)])

    followed_by = models.ManyToManyField(
        User, related_name='followers', blank=True)

    no_of_following = models.IntegerField(
        default=0, validators=[MinValueValidator(0)])

    following = models.ManyToManyField(
        User, related_name='following', blank=True)

    affiliate_id = models.CharField(max_length=100, default=uuid.uuid4,unique = True, blank= True, null = True)

    def __str__ (self):
        return self.user.username

class Notification(models.Model):
    send_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='sender')
    received_by = models.ForeignKey(User, on_delete=models.CASCADE, related_name='receiver')
    message = models.CharField(max_length=255)
    read = models.BooleanField(default=False)
    created_at = models.DateTimeField(default=None, null=True, blank=True)
    
    def __str__ (self):
        return self.message

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 13, 2023, 3:24pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/9 "2023-02-13T15:24:09Z")

</div>

> [@firaki12345](#):
>
> now the second part i am confused now how can there be duplicates ?

I wouldn’t know - it’s your data. You can inspect the dumpdata output to see if there are any anomalies in it.

If there are duplicates, it has nothing to do with your _current_ model definitions - it’s strictly an issue of what data exists in the current database.

Note the error:

> [@firaki12345](#):
>
> `psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "testing_profile_pkey"`

Assuming you used defaults for table names, you’ll want to look at the Profile model in your Testing app.

---

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 13, 2023, 4:18pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/10 "2023-02-13T16:18:51Z")

</div>

but the thing is there is no such table as

`psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "testing_profile_pkey"`

i have a model named

testing\_main\_profile

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 13, 2023, 4:24pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/11 "2023-02-13T16:24:02Z")

</div>

Again, your model definitions are irrelevant here.

The _only_ things that matter are your actual database schema in the original and new databases, and the contents of the dumpdata file.

Nothing else matters for diagnosing _this_ issue.

Side note:

> [@firaki12345](#):
>
> psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint “testing\_profile\_pkey”

This is not identifying a table. This is identifying a table constraint - these use a slightly different naming convention.

---

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 13, 2023, 4:30pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/12 "2023-02-13T16:30:16Z")

</div>

ok i am sorry for the confusion these are all the tables in my sqlite3

```auto
sqlite> .tables
account_emailaddress socialaccount_socialaccount     
account_emailconfirmation socialaccount_socialapp         
auth_group socialaccount_socialapp_sites   
auth_group_permissions socialaccount_socialtoken       
auth_permission testing_like                    
auth_user testing_main_profile            
auth_user_groups testing_main_profile_followed_by
auth_user_user_permissions testing_main_profile_following  
django_admin_log testing_notification            
django_content_type testing_retailer                
django_migrations testing_retailer_liked_by       
django_session testing_unique_colors           
django_site                     
sqlite>

```

how do i identify a table constraint ? sorry for the noob question

---

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 13, 2023, 4:38pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/13 "2023-02-13T16:38:27Z")

</div>

i even tried the following to find duplicates but didnt get any

```auto
import json

with open('datadump.json', 'r') as f:
    data = json.load(f)

unique_objects = []

for obj in data:
    if obj not in unique_objects:
        unique_objects.append(obj)
    else:
        print(f"Duplicate object: {obj}")

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 13, 2023, 5:21pm UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/14 "2023-02-13T17:21:26Z")

</div>

It’s not necessarily the _complete_ data that’s a duplicate - the only thing that needs to be duplicated is whatever field is constrained by the field in the table with that identified constraint.

For that, you may want to use something like pg\_admin to examine your schema to see what defines that constraint. Or you could use pg\_dump to dump the existing schema to have a text file to read.

Once you’ve identified what that constraint is, then you can look at the dumpdata file to see what is going to try and load the data, and then examine the rows being loaded to find the duplicated value.

---

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 14, 2023, 9:21am UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/15 "2023-02-14T09:21:21Z")

</div>

hi so you were right that it is a constraint, so installed pgadmin4 to examine it, and hte following results i tried abut am still confused, i hope it cna get solved

![1](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/4/489c0fe543e02e3048afc66cd67a64c9418e685f.png)

Tried to delete it but it depends on other tables as well

![2](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/a/a629829e578e71a295108d2ab8dcf78e3b6a3079.png)

**My Json data search for testing.main\_profile**

 ![Screenshot from 2023-02-14 14-50-51](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/9/9dc0877bd33aa5216f360dc5c1b57ea8793a323f.png)

so still confused, on the last step i know i am near i guess

thanks

---

<div class="post-metadata">

**Author:** ![firaki12345](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/firaki12345/32/4251_2.png) [@firaki12345](https://forum.djangoproject.com/u/firaki12345)\
**Post date:** [February 14, 2023, 10:30am UTC](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810/16 "2023-02-14T10:30:43Z")

</div>

OMG OMG I solved it , all i had to do was

in my signals.py file just comment tham out 🙂

i got the solution from this video

[![](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/4/4cdb89aabf5bc596ca1bafe6df62b9a9272fc6fc.jpeg "How to troubleshoot Django errors when migrating sqlite database to postgres") ](https://www.youtube.com/watch?v=Y2g5nUnZpNc)