value too long for type character varying(25)

Question

I have troubles uploading a CSV file into my database, when I try to upload the file I get a "value too long for type character varying(25)" error message for the field performance_exccy (see below for my django model). This error message started occuring when I switched from SQLite to PostgreSQL (using ElephantSQL), with SQLite the upload worked out perfectly.

The value causing the problem was 0.000000000000000000, which has 20 characters, but in my model I defined max_digit = 40 and dec_places =20 - so the number of digits should not be an issue. What I also noticed is that the upload error did not start with the first row of the CSV file but with the 12th row, with the rows before having values of similar lenght.

My model:

class Testdata3(models.Model):
    key = models.CharField(max_length=100, primary_key=True)
    mnemonic = models.CharField(max_length=50)
    assetclass = models.CharField(max_length=25)
    value = models.DecimalField(max_digits=25,decimal_places=10)
    performance = models.DecimalField(max_digits=40,decimal_places=20)
    performance_exccy = models.DecimalField(max_digits=40,decimal_places=20)
    performance_abs = models.DecimalField(max_digits=40,decimal_places=20)
    performance_abs_exccy = models.DecimalField(max_digits=40,decimal_places=20)
    date = models.DateField()

    def __str__(self):
        return self.key

My view:

def file_upload(request):
    template = "upload.html"
    prompt = {
        'order': 'Order of the CSV should be "placeholder_1", "placeholder_2", "placeholder_3" '
    }

    if request.method == "GET":
        return render(request, template, prompt)

    csv_file = request.FILES['file']

    if not csv_file.name.endswith('.csv'):
        messages.error(request, 'This is not a csv file')

    data_set = csv_file.read().decode('UTF-8')

    io_string = io.StringIO(data_set)

    #Ignores header row by jumping to next row
    next(io_string) 

    for column in csv.reader(io_string, delimiter=';', quotechar="|"):
        # Check if csv-row is empty, if true jump to next iteration/row
        if all(elem == "" for elem in column):
            next
        else:
            _, created = Testdata3.objects.update_or_create(
                key = column[0],

                defaults = {
                'key' : column[0],
                # Get everything after the date part in the primary key
                'mnemonic': re.findall(r'AMCS#[0-9]*(.*)', column[0])[0],
                # Create datetime object from a string
                'date' : datetime.datetime.strptime(column[6], '%d/%m/%Y'),
                'assetclass' : column[10],
                'value' : column[16], 
                'performance' : column[19],
                'performance_abs' : column[20],
                'performance_abs_exccy' : column[30],
                'performance_exccy' : column[31],
                }
            )
        context = {}

    return render(request, template, context)

you have set `assetclass = models.CharField(max_length=25)`, maybe `column[10]` contains more than 25 character. — Nalin Dobhal, Dec 11 '19 at 12:10
I checked but that is not the case, also i can upload the data perfectly fine into SQLite but encounter the above described problems when I try to do the upload to PostgreSQL — Daniel, Dec 11 '19 at 12:13
SQLite doesn't validate anything (type, size, whatever). Using SQLite for dev is a bad idea (well, it's fine for a quickstart if you haven't installed a proper RDBMS yet, but that's about all), better to use the same RDBMS as the one that will be used in production. — bruno desthuilliers, Dec 11 '19 at 13:34

score 0 · Answer 1 · answered Dec 11 '19 at 13:19

0

PostgreSQL recommends that you simply use the text type for variable-width strings, if you don't want to enforce a maximum. There's no performance penalty involved in using text.

https://github.com/npgsql/efcore.pg/issues/342

answered Dec 11 '19 at 13:19

Iqbal Hussain

1,077
1
4
12

score 0 · Accepted Answer · answered Dec 11 '19 at 13:28

0

i 've found the problem: I checked the upload with multiple csv files and saw that the error always occured when the assetclass field contained the string Sonstige Vermögensgegenstände . So I commented out the assetclass field in the my file_upload view and suddenly I was able to import the data into the database. The reason for the error was that the assetclass field had a max_length=25 while the string Sonstige Vermögensgegenstände has a length of 30 characters. So although django was telling me the problem was related to the field performance_exccy actually it was due to the assetclass field.

answered Dec 11 '19 at 13:28

Daniel

963
1
12
29

sorry for that, will do better next time, still relatively new to stack overflow and coding – Daniel Dec 11 '19 at 13:33
"django was telling me the problem was related to the field "performance_exccy", where did you see that? – dirkgroten Dec 11 '19 at 13:47
from the original error message: The above exception (value too long for type character varying(20) ) was the direct cause of the following exception: 'performance_exccy' : column[31], – Daniel Dec 11 '19 at 13:53

value too long for type character varying(25)

2 Answers2