How to Solve Error Message ERROR: value too long for type character varying(10) on the Restore Process of a Database in PostgreSQL Database

Posted on

Introduction

Another process showing on how to import data or restoring data from an SQL file to a PostgreSQL database server. Actually, this article has a connection with the previous article. The context is the same but the error message appear is different. The previous article with the title of ‘How to Solve Error ERROR: syntax error at or near “[” on the Restore Process of a Database in PostgreSQL Database’ exist in this link. The error message is ERROR: value too long for type character varying where the varying character size are different according to each column. Actually, the size depends on the max_length of each column or attribute definition. There are several attempt for importing or restoring the SQL file containing many insert statements. Those are exist in the following process :

C:\>psql -Udb_user -d db_app < "C:\Users\Personal\Downloads\insert-current-product.sql" 
Password for user db_user: 
ERROR: value too long for type character varying(10) 
C:\>psql -Udb_user -d db_app < "C:\Users\Personal\Downloads\insert-current-product.sql" 
Password for user db_user: 
ERROR: value too long for type character varying(30) 
C:\>psql -Udb_user -d db_app < "C:\Users\Personal\Downloads\insert-current-product.sql"  
Password for user db_user: 
ERROR: value too long for type character varying(100) 
C:\>psql -Udb_user -d db_app < "C:\Users\Personal\Downloads\insert-current-product.sql"  
Password for user db_user: 
ERROR: value too long for type character varying(50)

Before going on to the solution, the following is the actual definition of the model with the name of ’employee’ as follows :

from django.db import models
from django.contrib.auth.models import User
# Create your models here.
class Employee(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE, blank=True, null=True)
    id = models.CharField(max_length=10, blank=True, null=True)
    name = models.CharField(max_length=30, blank=True, null=True)
    birthdate = models.CharField(max_length=10, blank=True, null=True)
    address = models.CharField(max_length=100, blank=True, null=True)
    mail_address = models.CharField(max_length=50, blank=True, null=True)
    def __str__(self):
        return self.name

Solution

So, in order to solve the above problem concerning the fail process for importing or restoring rows or records through INSERT statement in the SQL file just change the model. Search the max_length of each attribute and adjust the value manually. As in the following definition, just modify the ‘max_length=value’ with the revised value accordingly. From an example from a parameter with the value of max_length=value into a new value of max_length=new_value. For an example, the change is from max_length=10 to max_length=20, and so on. It is  exist as in the as the following definition :

from django.db import models
from django.contrib.auth.models import User
# Create your models here.
class Employee(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE, blank=True, null=True)
    id = models.CharField(max_length=20, blank=True, null=True)
    name = models.CharField(max_length=100, blank=True, null=True)
    birthdate = models.CharField(max_length=20, blank=True, null=True)
    address = models.CharField(max_length=255, blank=True, null=True)
    mail_address = models.CharField(max_length=100, blank=True, null=True)

    def __str__(self):
        return self.name

Leave a Reply