Can't Transfer data from SQLITE3 to Postgres

the Commands i have used to transfer the data

python3 manage.py dumpdata > datadump.json

python3 manage.py migrate --run-syncdb

But i get the following error

the error start from here

  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 :slight_smile:

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.

1 Like

hi so now i tried another approach of doing this below

#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

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

1 Like

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

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.

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

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.

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

# 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

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:

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

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

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:

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

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

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

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

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}")

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.

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

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

2

My Json data search for testing.main_profile

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

thanks

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

in my signals.py file just comment tham out :slight_smile:

i got the solution from this video

1 Like