CSV Loading Error Fix: Data Too Long Or Value Too Long

 You are trying to load a CSV file into a database, and almost inevitably, you run into an ERROR: value too long for type character varying 64 if you are using PostgreSQL.  Or SQL Error [1406] [22001]: Data truncation: Data too long for column 'Name' at row 1 if you are using MariaDB or MySQL.

It doesn't matter if you are using the DBeaver application or the native database import tools. Newbies will look at the first line of the CSV file to examine the very first line and scratch their heads, as there is no error in the first line.

The database is ingesting the data one line at a time, so each line in the file is the first line. Your line in the CSV file is hiding deep in the file. And there may be many of them in there. 

The error message you receive is helpful, but it DOES NOT tell you which line in the CSV file is causing you this grief. You could use an 'eyeball inspection' of the data, which quickly becomes tedious. And if the CSV file has thousands of lines, your odds of finding the offending line grow longer.

Splitting up the file into smaller chunks is labor-intensive.

Example

I was loading data from the HMS Titanic data set when I received the following error:

Error occurred during batch insert
(you can disable batch insert to skip particular rows).

Reason:

SQL Error [22001]: Batch entry 0 INSERT INTO public.titanic ("PassengerId","Survived","Pclass","Name","Sex","Age","SibSp","Parch","Ticket","Fare","Cabin","Embarked")

VALUES (('308'::int8),('1'::int8),('1'::int8),('Penasco y Castellana, Mrs. Victor de Satode (Maria Josefa Perez de Soto y Vallejo)'),('female'),('17'::int8),('1'::int8),('0'::int8),('PC 17758'),('108.9'::real),('C65'),('C')) was aborted: ERROR: value too long for type character varying(64)  Call getNextException to see other errors in the batch.

A Difference Between PostgreSQL and MariaDB/MySQL

PostgreSQL will return the offending line in the error message, as shown above. MariaDB and MySQL do not. They provide only SQL Error [1406] [22001]: Data truncation: Data too long for column 'Name' at row 1.

DBeaver's import tool selected a VARCHAR(64) for the name field after examining some of the records. The unfortunate Penasco y Castellana, Mrs. Victor de Satode (Maria Josefa Perez de Soto y Vallejo needs more space. And there may be other records with names as long, or longer.

Solution

You can use Google Sheets to examine the CSV file before attempting to load it into the database. 

We can use =max(arrayformula(len(D1:D891))) on the columns with the names to determine the maximum length of a name. The longest name in this data is eighty-two characters long.  

If you read the linked blog post, you will see that I defined the Name column as a TEXT field to solve the issue. But what if you are trying to save space? Knowing the width of that column is valuable.

So now you have a tool to determine the width of a column when you get the dreaded 'value too long' 



Comments

  1. Adrian KlaverNovember 13, 2025 at 1:09 PM

    Not sure what you are getting at with "But what if you are trying to save space? Knowing the width of that column is valuable." ? From here https://www.postgresql.org/docs/current/datatype-character.html: "The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less."

    ReplyDelete

Post a Comment

Popular posts from this blog

Incremental Backups in PostgreSQL 17

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use. This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies. Step 1 - Enable WALL Summerization The incremental backups require Write Ahead Log Summarization. It is likely not on your instance by default. But it is easy to enable. demo=# alter system set summerize_wal = 'on'; ALTER SYSTEM demo=# select pg_reload_conf(); pg_reload_conf  ----------------  t (1 row) Step 2 - Full Backup The examples below are much simpler than you will run into in real life.  The scenario is that we make a full backup on Sunday and do incrementals the rest of the week. Wednesday through Saturday are omitted below.  stoker@ThinkPa...
Read more

How PostgreSQL's Aggregate FILTER Will Spoil You

Using PostgreSQL will definitely spoil you. I recently had to use another database and found, to my chagrin, that aggregate filters were not included in their implementation of Structured Query Language. Specifically, there was no FILTER clause available. FILTER? To illustrate my dependency, let me start with some simple data. demo=# create table z (a int, b int, c int); CREATE TABLE demo=# insert into z values (1,10,100),(2,20,200),(3,30,300),(4,40,400); INSERT 0 4 demo=# select a,b,c from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=#  If I wanted the overall number of rows, I could use a simple COUNT(*). If I wanted an overall number of rows where one of the columns was of a certain value, I could add the logic for getting those rows to the WHERE clause. What if I wanted both?  Do I need two queries to do the work separately? Maybe a materialized view or a CTE (Common Tabl...
Read more

Can Artificial Intelligence Created Better Tables Than You?

 Artificial Intelligence is one of those conundrums where the ability to have some tasks handled for you contrasts with a mix of ego and pride that it may be better than you at those tasks.  I recently wrote a blog using another database about an AI-generated SQL that was quite sophisticated . But what about asking an AI to create a table with a specification like a DBA/SRE/Analyst might receive? I used Grok and entered the following prompt: Write the SQL to create a table on a PostgreSQL version 17 server to record data on customers. The data will include a first name, a last name, an address, a birthdate, and a unique UUID primary key. Partition the data by the year of birth. And produce some test data of at least ten records. I am sure many of us have started large projects given less instruction.  Notice: I did not denote the format for the address (US Post Office's format, or UK, or other). Nor did I set a name length. I wanted to see what assumptions were made by t...
Read more