1

I'm using psycopg3 to copy rows from a csv into a SQL table.

But in the CSV are some empty values that are represented with a ' '. So when it gets copied to a int value field, I get a invalid input syntax for type integer: ""

    with conn.cursor() as cur:
                with cur.copy("COPY manycolumns...") FROM STDIN") as copy:
    
                        for line in csv.reader(data):
    
                            print(line)
    
                            copy.write_row(line)

I've tried to turn all empty values in the list to null. But is a very inefficient solution. Is there an argument in how I read the csv that doesn't read empty values?

Hannan Rhodes
  • 79
  • 8
  • 1
    You have to translate the blank values within your for loop. Probably to a `none` which will become a SQL `null`. – Schwern Sep 20 '22 at 19:46
  • Is it actually a quoted empty string `' '`? – Adrian Klaver Sep 21 '22 at 22:24
  • If is is actually an unquoted empty string which the error seems to indicate see my answer here [COPY with FORCE NULL](https://stackoverflow.com/questions/73074229/copy-with-force-null-to-all-fields/73083429#73083429). – Adrian Klaver Sep 21 '22 at 22:30

0 Answers0