Pandas to_sql() not working with Posgresql - value too long for type character varying
<p>I'm using Pandas with SQLAlchemy to apply some ETL on one CSV file</p>

<p>After validating the fields and transforming some of them I try to export to my PostgreSQL database, but I'm getting one error which does not make sense:</p>

<pre><code>sqlalchemy.exc.DataError: (psycopg2.DataError) value too long for type character varying(50)
</code></pre>

<p>I already changed the field to many values (it was initially setup as 15). I tried to get <code>NaN</code> values for that field and replacing with '' (there was only one field). For that I used:</p>

<pre><code>&gt;&gt;&gt; df.loc[df['foo'].isnull(), 'foo'] = ''
</code></pre>

<p>I tried changing the <code>chunksize</code> to 5000 and 1000. Initially, it was not set.</p>

<pre><code>&gt;&gt;&gt; df.to_sql("mytable", con, index=False, if_exists='append', chunksize=1000)
</code></pre>

<p>The command above worked with <code>sqlite</code></p>

<p>After having those problems I checked the column which was throwing the error again to see if there was any problem with its length. Apparently, it did not, but I ran the following code anyway:</p>

<pre><code>&gt;&gt;&gt; df.foo.str.len().max() 
11.0
</code></pre>

<p>I also tried the following:</p>

<pre><code>&gt;&gt;&gt; df.fillna(value='', inplace=True)
&gt;&gt;&gt; df['foo'] = df['foo'].str.strip()
</code></pre>

<p>Then I also added</p>

<pre><code>for f in Inventory._meta.get_fields():
    if f.get_internal_type() == 'CharField':
        df[f.name] = df[f.name].str[:f.max_length]
</code></pre>

<p>But it did not work either</p>

<p>I finally put the length of the column to 100, but this is not right. The field contains only 11 char per row. I ran out of ideas. This error is strange, and I'd appreciate some help.</p>

<p>Ok, it's pretty embarrassing what happened. The code above was right since the beginning. The problem is that I'm merging some rows and concatenating in that column the merged values in a different function and I forgot. I thought the error was only in my validation function when it was never there.</p>

<p>What did I do to find it out?</p>

<p>I change the value of the column to a big value (100) so the code above would work, then I ran the following SQL:</p>

<pre><code>SELECT length(foo) AS ln_foo FROM inventory WHERE length(foo) &gt; 11
</code></pre>

python django postgresql pandas django-models