Processing error using the COPY command when using PostgreSQL

Question

I have a Python script that executes the following command to copy the contents of a CSV file to a database:

copy_query = "COPY table_name FROM STDIN DELIMITER '%s' CSV HEADER QUOTE '\"';" % (self.delimiter) 

table_name is an already created table with specific types. The data in the CSV file does not always match the type of column. For example, a CSV file might look like this:

 "row1", 123, "2012-11-11" "row2", 346, "2012-11-12" "row3", \N, "2012-11-12" 

As you can see, column 2 must be of type int , but since the data in row 3 does not match type int , the whole operation fails. Is it possible to refuse this line at all? I would prefer it to fill in some kind of default value of the appropriate type, but the deviation of the string in order is fine too. Thanks for the help!

Answer 1

You cannot do this with the COPY command, but you can modify the target table to accept NULL for these number fields.