COPY NULL values in Postgresql using psycopg2 copy_from()

Question

This seems like a rather popular question, but all the answers around here did not help me solve the issue... I have a Postgresql 9.5 table on my OS X machine:

CREATE TABLE test (col1 TEXT, col2 INT)

The following function uses the psycopg2 copy_from() command:

def test_copy(conn, curs, data):
    cpy = BytesIO()
    for row in data:
         cpy.write('\t'.join([str(x) for x in row]) + '\n')
    print cpy
    cpy.seek(0)
    curs.copy_from(cpy, 'test')

test_copy(connection, [('a', None), ('b', None)])

And will result in this error:

ERROR:  invalid input syntax for integer: "None"
CONTEXT:  COPY test, line 1, column col2: "None"
STATEMENT:  COPY test FROM stdin WITH DELIMITER AS '    ' NULL AS '\N'

I tried also curs.copy_from(cpy, 'test', null=''), curs.copy_from(cpy, 'test', null='NULL'). Any suggestions are greatly appreciated.

n1000 · Accepted Answer · 2016-02-10 12:44:23Z

22

OK, after more trial & error I found the solution:

copy_from(cpy, 'test', null='None')

answered Feb 10, 2016 at 12:44

n1000

5,36411 gold badges43 silver badges67 bronze badges

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

1 Comment

AntonOfTheWoods Over a year ago

If you are using csv.write on an io.StringIO then null='' works (psycopg2 2.8, postgresql 13)

Collectives™ on Stack Overflow

COPY NULL values in Postgresql using psycopg2 copy_from()

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related