psycopg2.DataError: invalid input syntax for type double precision: "NULL"

Question

I'm trying to upload a CSV file using the copy from command and the Python psycopg2 library. I understand that the error stems from trying to insert a char value into a double precision column, but I specified null values as "NULL" when creating my csv file and in the Postgres copy command I also specified nulls as "NULL", so I'm not sure why it isn't converting those values when uploading.

Creating the csv file:

with file_path.open(mode='w', newline='') as outfile:
    csv_writer = csv.writer(outfile, delimiter=delimiter, quoting=csv.QUOTE_NONNUMERIC)

    if include_headers:
        csv_writer.writerow(col[0] for col in self.cursor.description)
    for row in self.cursor:
        csv_writer.writerow(['NULL' if value is None else value for value in row])

Uploading the file to postgres:

sql = """COPY {table} FROM STDIN WITH (DELIMITER '{sep}', NULL 'NULL', FORMAT CSV, HEADER True);""".format(table=table, sep=sep)

with open(file) as f:
    self.cursor.copy_expert(sql=sql, file=f, size=size)
self.commit()

Error:

psycopg2.DataError: invalid input syntax for type double precision:
"NULL" CONTEXT:  COPY test_table, line 25, column Lat:
"NULL"

Laurenz Albe Laurenz Albe · Accepted Answer · 2019-03-04T16:27:28

You should use QUOTE_MINIMAL, otherwise your Nones will show up as "NULL" (including the quotes) in the file, which is why they are not recognized properly by COPY.

But I think you don't have to go through all this as COPY is able to deal with CSV files just fine. Open the file and use the copy_from and copy_to methods.

psycopg2.DataError: invalid input syntax for type double precision: "NULL"

1 Answers