CODEWITHSUNDEEP
python-3.xpostgresqlpsycopg2
Xlsx
Xlsx

Reputation: 175

Issue with Python copy_expert loading data with nulls

I'm unable to import a csv into a table in postgres using copy_expert. The error is due to null values.

My field type in db is to allow nulls. Manually inserting via insert into proves to be successful

Based on what I've understood so far, it is because copy_expert translates nulls into a text, which is why it fails on a timestamp datatype. However, I'm unable to locate the right syntax to coerce the nulls as nulls. Code snippet below:

with open(ab, 'r') as f:
    cur.copy_expert("""COPY client_marketing (field1,field2,field3) FROM STDIN DELIMITER ',' CSV HEADER""", f)

Error msg:

DataError: invalid input syntax for type timestamp: "". Appreciate any help on the script or pointing me to the right sources to read on.

Upvotes: 1

Views: 3157

Answers (1)

bimsapi
bimsapi

Reputation: 5065

I was able to do this by adding force_null (column_name). E.g., if field3 is your timestamp:

copy client_marketing (field1, field2, field3) from stdin with (
    format csv,
    delimiter ',',
    header,
    force_null (field3)
);

Hope that helps. See https://www.postgresql.org/docs/10/sql-copy.html

Upvotes: 3

Related Questions