Python psycopg2 copy_from() to load data throws error for null integer values: DataError: invalid input syntax for integer: ""

Question

I am trying to load data from a StringIO object of python into a Postgres database table using psycopg2's copy_from() method.

My copy_from fails on the first record itself specifically for a particular (nullable) integer column which has null value ('' without quotes). I have also tried using Python's None keyword instead of '' for NULL values. It throws me the following error: DataError: invalid input syntax for integer: "" CONTEXT: COPY , line 1, column : ""

The code looks something like this:

table_data = StringIO.StringIO()
# Populate the table_data variable with rows delimited by \n and columns delimited by \t
cursor = db_connection.cursor()
cursor.copy_from(table_data, <table_name>)

This column is a smallint column.

Eevee · Accepted Answer · 2014-10-28 20:52:52Z

16

By default, COPY FROM (and copy_from) encode a NULL value as \N. If you want to use the empty string to mean NULL, you need to say so explicitly:

cursor.copy_from(table_data, table_name, null="")

answered Oct 28, 2014 at 20:52

Eevee

48.7k11 gold badges100 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3422637 Over a year ago

Thanks. The issue has now been resolved. However, I get a new error: DataError: invalid byte sequence for encoding "UTF8": 0x92 Should I raise a new question for this?

Eevee Over a year ago

that's probably already been answered a zillion times over :) it's an encoding problem. most likely your data is encoded as windows-1252, and you need to decode it.

user3422637 Over a year ago

I did search the answer for this on stackoverflow and I found questions/answers on similar lines but those didn't solve my problem. I have put up a new descriptive question for that here. stackoverflow.com/questions/26619801/… Please have a look. Thanks for your help

shrinathM Over a year ago

copy_from() method does not allow quoted strings like double quotes.Example row ["python", 598.00]; It sends to db like ""python""

Collectives™ on Stack Overflow

Python psycopg2 copy_from() to load data throws error for null integer values: DataError: invalid input syntax for integer: ""

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related