Issue with Python copy_expert loading data with nulls

Question

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.

Answer 1

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

• Xlsx: Works like a charm. Thank you so much. I was super confused on how to input the syntax from the link you have shared when I came across it previously. Looking at your snippet clarified everything. I was stuck on this for weeks!